Distributed SQL Tricks and Tips – Dec 20, 2019

Jimmy Guerrero

VP Developer Relations

Welcome to this week’s tips and tricks blog where we recap some distributed SQL questions from around the Internet. We’ll also review upcoming events, new blogs and documentation that has been published since the last blog. Got questions? Make sure to ask them on our YugabyteDB Slack channel, Forum, GitHub or Stackoverflow. Ok, let’s dive right in:

How can I update a JSONB document in YugabyteDB?

To perform updates to JSONB you use an update statement via the YSQL API. For example:

Create a schema called store:

CREATE SCHEMA store;

Next, create a table called books:

CREATE TABLE store.books ( id int PRIMARY KEY, details jsonb );

Insert some sample JSONB documents:

INSERT INTO store.books (id, details) VALUES
(1, '{ "name": "Macbeth", "author": { "first_name": "William", "last_name": "Shakespeare" }, "year": 1623, "editors": ["John", "Elizabeth", "Jeff"] }');
INSERT INTO store.books (id, details) VALUES
(2, '{ "name": "Hamlet", "author": { "first_name": "William", "last_name": "Shakespeare" }, "year": 1603, "editors": ["Lysa", "Mark", "Robert"] }');
INSERT INTO store.books (id, details) VALUES
(3, '{ "name": "Oliver Twist", "author": { "first_name": "Charles", "last_name": "Dickens" }, "year": 1838, "genre": "novel", "editors": ["Mark", "Tony", "Britney"] }');
INSERT INTO store.books (id, details) VALUES
(4, '{ "name": "Great Expectations", "author": { "first_name": "Charles", "last_name": "Dickens" }, "year": 1950, "genre": "novel", "editors": ["Robert", "John", "Melisa"] }');
INSERT INTO store.books (id, details) VALUES
(5, '{ "name": "A Brief History of Time", "author": { "first_name": "Stephen", "last_name": "Hawking" }, "year": 1988, "genre": "science", "editors": ["Melisa", "Mark", "John"] }');

Perform a simple select to confirm you have data.

SELECT * FROM store.books;

Now, update the document which has the id set to 4:

UPDATE store.books
SET details='{"name": "Great Expectations", "year": 1950, "genre": "novel", "author": {"last_name": "Dickens", "first_name": "Jerome"}, "editors": ["Robert", "John", "Melisa"]}'
WHERE id=4;

Finally, do a select to confirm the document has been updated:

SELECT * FROM store.books WHERE id = 4;

In YugabyteDB do I need to keep string and byte objects under 64kb and 1 MB respectively in order to avoid potential performance problems?

The short answer is no, you don’t have to keep your objects below those limits. In fact, YugabyteDB’s upper limit is bound by a 32 MB RPC ceiling. Obviously, keeping columns in the 2 MB or less range will deliver better performance. But, if your use case demands it and depending on the size of the other columns, we support 32 MB sized columns.

When deploying YugabyteDB on Kubernetes, I would like to add CPU and memory request limits to my secure and insecure StatefulSet configurations. How can I go about this?

Out-of-the-box, YugabyteDB supports setting memory and CPU request limits on Kubernetes. For example:

helm install yugabyte --set resource.tserver.limits.cpu=16,resource.tserver.limits.memory=30Gi --namespace yb-demo --name yb-demo --wait

For additional information, check out the documentation on Helm Hub.

I am using yb-ctl to create a 3 node, rf=3 cluster. How can make it listen on external IP address instead of localhost?

The yb-ctl command only works for local deployments. To bring up YugabyteDB on three separate hosts, you can follow the instructions detailed in the manual deployments section of the Docs. There are a number of orchestrated multi-node deployment options available:

  • Terraform on any cloud
  • Cloud formation on AWS, Deployment manager in GCP and ARM templates in Azure
  • Kubernetes is another easy way to deploy using Operators or Helm charts.

All the available multi-host, cloud installation options are detailed at: https://download.yugabyte.com/cloud

New Documentation, Blogs, Tutorials, and Videos

New Blogs

New Videos

New Docs

Upcoming Meetups and Conferences

PostgreSQL Meetups

Distributed SQL Webinars

We are Hiring!

YugaByte is growing fast and we’d like you to help us keep the momentum going! Check out our currently open positions:

Our team consists of domain experts from leading software companies such as Facebook, Oracle, Nutanix, Google and LinkedIn. We have come a long way in a short time but we cannot rest on our past accomplishments. We need your ideas and skills to make us better at every function that is necessary to create the next great software company. All while having tons of fun and blazing new trails!

Get Started

Ready to start exploring YugabyteDB features? Getting up and running locally on your laptop is fast. Visit our quickstart page to get started.

What’s Next?

  • Compare YugabyteDB in depth to databases like CockroachDB, Google Cloud Spanner and MongoDB.
  • Get started with YugabyteDB on macOS, Linux, Docker, and Kubernetes.
  • Contact us to learn more about licensing, pricing or to schedule a technical overview.
Jimmy Guerrero

VP Developer Relations

Related Posts

Explore Distributed SQL and YugabyteDB in Depth

Discover the future of data management.
Learn at Yugabyte University
Get Started
Browse Yugabyte Docs
Explore docs
PostgreSQL For Cloud Native World
Read for Free