Distributed SQL Tips and Tricks – April 26, 2022
Welcome back to our distributed SQL tips and tricks blog! I have the pleasure of recapping distributed SQL questions from around the Internet.
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.
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.
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-184.108.40.206-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.
Outside of the Yugabyte blogs called out below, you can also check out our Yugabyte DEV Community Blogs here.
- Distributed SQL Deep Dive: Inside YugabyteDB’s Two-Layer Architecture
- Performing Flexible IO Testing in YugabyteDB
- Announcing YugabyteDB 2.13: Breakthrough Developer Experience and Performance
- Implementing Change Data Capture (CDC) in YugabyteDB
- Automation Workflows: Using GitOps for YugabyteDB with Argo CD and Helm
- Achieving PCI DSS Compliance with YugabyteDB
- Securing YugabyteDB: Part 2 – Client-to-Server Encryption in Transit
- What Every Application Developer Needs to Know About Geo-Distributed Databases
- Five Benefits to Running a Distributed SQL Database in Kubernetes
- Using Ebean ORM with YugabyteDB
- Retail Application Migration: Lessons Learned Moving from On-Prem to Cloud Native
- New Product Names: YugabyteDB Managed and YugabyteDB Anywhere
- YSQL Exercises: Learning Path, 7 self-paced mini-courses
- YugabyteDB Friday Tech Talks: Episode 11: Business Continuity – Backup/Restore in YugabyteDB
- Justuno Uses Distributed SQL to Help Increase e-commerce Revenue Streams
- Admiral Uses YugabyteDB for Global Scale with Single Digit Millisecond Latency
- Abra Controls Uses Yugabyte Cloud
- YugabyteDB Friday Tech Talks: Episode 12: Indexes in YugabyteDB
- DSS Asia 2022: Opening Keynote: The Biography of a Modern Application
- YugabyteDB Friday Tech Talks: Episode 13: Point-in-Time Recovery in YugabyteDB
- PostgreSQL Compatibility: What Does That Mean? Why Choose Yugabyte?
- YugabyteDB Friday Tech Talks: Episode 14: Partial Indexes & Expression Based Indexes
- YugabyteDB Friday Tech Talks: Episode 15: Optimizing Queries Using pg_hint_plan PostgreSQL Extension
- Distributed SQL Summit 2021 – all videos!
- Build a Real-time Polling App with Hasura GraphQL and YugabyteDB Managed
- AWS Summit San Francisco
- CIO Visions Summit, North America
- How to build an app at Facebook-scale in 2022
- The Power of a Modern Database: Accelerate Developer Productivity with YugabyteDB and VMware Tanzu
- When is Eventually (Consistent) Not Soon Enough?
- Percona Live
- Temenos Community Forum 2022
- KubeCon + CloudNativeCon Europe 2022
- Hack MidWest
- Cloud Expo Asia, Singapore
- KubeCon + CloudNativeCon North America 2022
- CIO Visions Summit, Chicago
- Yugabyte University Calendar: Free Virtual Training and Builder Workshops
Finally, are you ready to explore more YugabyteDB features?
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!