Distributed SQL Tips and Tricks – April 26, 2022

April 26, 2022

Welcome back to our distributed SQL tips and tricks blog series! I have the pleasure of recapping distributed SQL questions from around the Internet.

This blog series would not be possible without Dorian Hoxha, Franck Pachot, and Frits Hoogland. We also thank our incredible user community for not being afraid to ask questions.

Do you have questions? Make sure to ask them on our YugabyteDB Slack channel, Forum, GitHub, or Stack Overflow. For previous Tips & Tricks posts, check out our archives. Now let’s dive in!

What is the best way to handle read restart errors in YSQL?

First and foremost, you will have to use the latest version of YugabyteDB (either v2.12 or v2.13) along with yb_enable_read_committed_isolation=true to use the READ COMMITTED isolation YSQL now supports. But this isolation is the default in PostgreSQL, and YSQL has exactly the same semantics (barring minor limitations that won’t likely affect you). More specifically, it ensures clients won’t see Restart read required errors. This is an error that isn’t seen in single node PostgreSQL, but it can occur in YugabyteDB due to the clock skew that might be present between nodes.

As per our transaction isolation documentation:

“…this isolation level internally handles read restart and conflict errors. In other words, the client doesn’t see read restart and conflict errors (barring an exception). Details on YugabyteDB’s implementation are on the Read Committed isolation level page.”

This isolation level will more than likely alleviate all of the errors that you are running into. Furthermore, READ COMMITTED already has pessimistic locking built-in, which means a transaction might wait for another transaction to complete as seen in PostgreSQL (check out this example). We are working on a more sophisticated implementation of pessimistic locking that will improve the performance of READ COMMITTED in a workload with contention and also help avoid the requirement of adding a statement timeout to avoid deadlocks (see this example). Additionally, orthogonal to the work on performance-optimized pessimistic locking implementation, current limitations are set to be improved.

If you cannot afford to upgrade your cluster at this time, you can incorporate retries at the application level when these errors surface to help alleviate the issue.

I know your YCQL API has TTL, how can we accomplish the same using the YSQL API ?

You are correct that the YCQL API has support for TTL, and that at this time YSQL does not. But there is a straightforward path to accomplishing the same end result using database partitions. Physically, a partition is like a table, so it is possible to drop one while inserting it into others, and this operation is fast. You need to automate it yourself (i.e., create and drop) until pg_partman (for the DDL) and pg_cron (for the scheduling) are supported. But let’s say we want to drop any changes on an order after a certain period of time. Below you can find an example of how to handle this.

First, create your table and the range partition based on your date or time column:

CREATE TABLE order_changes (
  change_date date,
  type text,
  description text
)
PARTITION BY RANGE (change_date);

Next, remove the partition from the partitioned table while retaining access to it as a separate table. You can get the partition name with tableoid::regclass. This enables you to perform operations on the data:

ALTER TABLE order_changes DETACH PARTITION order_changes_2019_03;

Currently, these steps have to be done manually. Once we have pg_partman support this can be automated.

Is there a way to query for the YugabyteDB version we are running?

Yes. Since we reuse the PostgreSQL query layer, you can run the same SELECT version(); command to get these details. The output will look like this:

PostgreSQL 11.2-YB-2.8.1.0-b0 on x86_64-pc-linux-gnu, compiled by gcc (Homebrew gcc 5.5.0_4) 5.5.0, 64-bit

This output will call out the latest PostgreSQL version YugabyteDB is compatible with (v11.2) , as well as the current version of YugabyteDB you are running (v2.8.1). But if you’re using YugabyteDB Managed, you can view the database version through the UI under the Settings tab. The latest stable version, v2.12, is now available and all YugabyteDB Managed clusters will be upgraded shortly. But for those looking to play around with a preview release, v2.13 is available as well. However, please keep in mind that odd numbered releases are not recommended for production use. And finally, if you plan on running in production, please use a stable release.

There are some great options at your disposal: run YugabyteDB locally on your laptop (Quick Start), deploy it to your favorite cloud provider (Multi-node Cluster Deployment), or sign up for a free YugabyteDB Managed cluster. It’s easy, so get started today!