Distributed SQL Tips and Tricks – March 6, 2020

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 documentation, and blogs that have been published since the last post. 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 configure YugabyteDB for serialization isolation level?

YugabyteDB supports snapshot isolation and serializable isolation levels. By default we use snapshot isolation which maps to PostgreSQL’s repeatable read isolation level. There are several ways to change the isolation level:

1. Change the default isolation level at the cluster level for all databases by setting the --ysql_default_transaction_isolation yb-tserver gflag to SERIALIZABLE.

2. Change isolation level for the current session using the SET statement:

yb_demo=# show transaction_isolation;
 transaction_isolation
-----------------------
 read committed
(1 row)

yb_demo=# set default_transaction_isolation='serializable';
SET
yb_demo=# show transaction_isolation;
 transaction_isolation
-----------------------
 serializable
(1 row)

3. Change isolation level for the current transaction as part of the BEGIN TRANSACTION step:

yb_demo=# begin transaction isolation level serializable;
BEGIN

yb_demo=# SHOW transaction_isolation;

transaction_isolation 
-----------------------
serializable
(1 row)

yb_demo=# commit;
COMMIT

yb_demo=# SHOW transaction_isolation;

transaction_isolation 
-----------------------
read committed
(1 row)

4. Change isolation level for the current transaction using SET TRANSACTION statement:

yb_demo=# begin;
BEGIN

yb_demo=# SHOW transaction_isolation;

transaction_isolation 
-----------------------
read committed
(1 row)

yb_demo=# SET TRANSACTION ISOLATION LEVEL serializable;
SET

yb_demo=# SHOW transaction_isolation;
 
transaction_isolation 
-----------------------
serializable
(1 row)

yb_demo=# commit;
COMMIT

Is it possible to create read-only users in YugabyteDB?

Yes. Read-only users are often used by applications where data manipulation is not required, for example reporting-type applications. Users with this type of custom permission can be created easily using the instructions below:

First create the user:

yb_demo=# CREATE USER john WITH PASSWORD 'password';
CREATE ROLE

Next, let’s add the ability to connect to the database and the schema using the GRANT statement:

yb_demo=# GRANT CONNECT ON DATABASE yb_demo TO john;
GRANT

yb_demo=# GRANT USAGE ON SCHEMA public TO john;
GRANT

Now, we can grant read only access to all tables in the public schema via the SELECT privilege:

yb_demo=# GRANT SELECT ON ALL TABLES IN SCHEMA public TO john;
GRANT

Finally, we can test the privileges of the new user in another shell to make sure the permissions are working as expected:

$ ./bin/ysqlsh --dbname=yb_demo --username=user_read
yb_demo=> SELECT * FROM users;

 id | name
----+------
  2 | bill
  1 | ben
(2 rows)

yb_demo=> INSERT INTO users(name) VALUES ('a');
ERROR:  permission denied for table users

How does YugabyteDB recover data after a crash?

YugabyteDB uses DocDB for persistence. DocDB is a LSM storage engine forked from RocksDB. In a single node scenario, when the process restarts, YugabyteDB will read the Write Ahead Log (WAL) to return to the node to the state that it was in before it crashed.

Each DML statement is synchronously replicated using the Raft algorithm and each replica writes to its own local WAL.

When a node crashes, YugabyteDB picks a new leader tablet in just a couple of seconds. If the old node doesn’t come back online or it takes too long, tablet peers are replicated in other nodes of the cluster using a process called bootstrapping until the replication factor is achieved for each tablet.

Should I use auto-incrementing IDs in a distributed SQL database?

Auto-incremented IDs are not recommended in YugabyteDB because they can introduce a bottleneck and may lower write concurrency by creating hotspots. Since auto-increment IDs are generated by using a single row in a system table that maintains the ID, the tablet hosting the row becomes a hotspot. Furthermore, auto-incremented IDs are logically close together and can end up writing to a set of few tablets (if table uses range sharding), thereby increasing contention and load on those tablets.

A better way to get similar results is to use uuid columns. They do not introduce a single point of bottleneck, can be generated in the server or client and are uniformly distributed across tablets when inserting new rows.

In the example below, we first create the extension pgcrypto which enables automatically generating uuid values with the gen_random_uuid() function and use it as the default value:

yb_demo=# CREATE EXTENSION IF NOT EXISTS pgcrypto;
NOTICE:  extension "pgcrypto" already exists, skipping
CREATE EXTENSION

yb_demo=# CREATE TABLE users(id uuid PRIMARY KEY DEFAULT gen_random_uuid(), name TEXT);
CREATE TABLE

yb_demo=# INSERT INTO users(name) VALUES ('ben'),('bill');
INSERT 0 2

yb_demo=# SELECT * FROM users;
              	id              	| name
--------------------------------------+------
 47c2e90f-6a27-4d49-b694-b8a392ac4f70 | bill
 a69c219c-ca6b-428e-b35e-ef04d29cb5f3 | ben
(2 rows)

However, if your application still requires auto-incrementing IDs, then you can create those using serial (4 byte integer) or bigserial (8 byte integer) columns.

yb_demo=# CREATE TABLE users(id bigserial PRIMARY KEY, name TEXT);
CREATE TABLE

yb_demo=# INSERT INTO users(name) VALUES ('ben'),('bill');
INSERT 0 2

yb_demo=# SELECT * FROM users;
 id | name
----+------
  2 | bill
  1 | ben
(2 rows)

Serial columns use sequences for generating the IDs. To alleviate the hotspot problem that sequences cause, we recommend configuring a cache for sequences that will pre-allocate a set of auto-incrementing IDs and store them in memory for faster access.

yb_demo=# CREATE SEQUENCE user_id_sequence CACHE 10000;
CREATE SEQUENCE
yb_demo=# CREATE TABLE users(id BIGINT PRIMARY KEY DEFAULT nextval('user_id_sequence'), name TEXT);
CREATE TABLE
yb_demo=# INSERT INTO users(name) VALUES ('ben'),('bill');
INSERT 0 2
yb_demo=# SELECT * FROM users;
 id | name
----+------
  2 | bill
  1 | ben
(2 rows)

New Blogs, Tutorials, and Videos

New Blogs

• Cloud Native Meets Distributed SQL: Bringing Microservices, Kubernetes, Istio & YugabyteDB Together with Hipster Shop Demo
• 5 Query Pushdowns for Distributed SQL and How They Differ from a Traditional RDBMS
• YugabyteDB 2.1 is GA: Scaling New Heights with Distributed SQL
• Getting Started with Distributed SQL on Yugabyte Cloud

New Videos

• Install YugabyteDB on macOS using Docker and yugabyted
• Longhorn Distributed Block Storage & Cloud Native Distributed SQL on Google Kubernetes Engine
• Getting Started with Distributed SQL Colocated Tables
• What’s New in YugabyteDB v2.1

Upcoming Events

• Kubernetes Pune Meetup, March 7, 2020, Pune

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.