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?
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
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
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
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.
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)
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
- 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
- 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
- Kubernetes Pune Meetup, March 7, 2020, Pune
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:
- Lead Development Representative – Sunnyvale, CA
- Site Reliability Engineer (SRE) – Sunnyvale, CA
- Community Success Engineer – Remote OK
- Senior Curriculum Developer – Remote OK
- Frontend Engineer – Sunnyvale, CA
- Software Engineer – Cloud Infrastructure – Sunnyvale, CA
- Software Engineer – Core Database – Sunnyvale, CA
- Software Engineer – Full Stack – Sunnyvale, CA
- Solutions Engineer – Sunnyvale, CA
- Developer Advocate – Sunnyvale, CA
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!
Ready to start exploring YugabyteDB features? Getting up and running locally on your laptop is fast. Visit our quickstart page to get started.