Distributed SQL Tricks and Tips – Dec 20, 2019

VP Developer Relations

December 20, 2019

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

Getting Started with PostgreSQL Authentication and Authorization in YugabyteDB
Getting Started with PostgreSQL’s Row-Level Security in YugabyteDB
Designing a Change Data Capture and Two Data Center Architecture for a Distributed SQL Database
Distributed Databases Deconstructed: CockroachDB, TiDB and YugabyteDB
We have uploaded over a dozen videos from this year’s Distributed SQL Summit. You can links to the presentations, slides and a recap of the highlights by checking out, “2019 Distributed SQL Summit Recap and Highlights.”

New Docs

Encryption at rest
Configuring encryption at rest with the yb-admin tool
Using PostgreSQL’s JDBC Driver
YugabyteDB configuration reference documentation for TServer, Master nodes and port assignments
Two data center (2DC) deployment instructions

Upcoming Meetups and Conferences

PostgreSQL Meetups

Jan 16: Silicon Valley PostgreSQL Meetup

Distributed SQL Webinars

Jan 15: Developing Cloud-Native Spring Microservices with a Distributed SQL Backend

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.

December 20, 2019

Tips and Tricks

Distributed SQL Tricks and Tips – Dec 20, 2019

Related Posts

Explore Distributed SQL and YugabyteDB in Depth