Unlocking Time to Live (TTL) in YSQL

The YCQL API does support TTL. At this time, YSQL does not. However, you can achieve the same result (in a very straightforward manner) using database partitions. Physically, a partition is like a table, so it’s possible to drop one while inserting it into others, and this operation is fast. You will 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 you want to drop any changes on an order after a certain period of time. Here is an example of how to handle this.

1. 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);

2. 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. With this you can perform operations on the data:

   ALTER TABLE order_changes DETACH PARTITION order_changes_2019_03;

Currently, both steps have to be done manually. Once pg_partman support is generally available, it can be automated.

Take a deep dive into table partitioning

Discover More Tips and Tricks

Explore our library of distributed SQL tips and tricks and general “how to” information on the YugabyteDB blog and on our DEV Community Blogs.

Events and Training

Check out the upcoming YugabyteDB events, including all training sessions, conferences, in-person and virtual events, and YugabyteDB Friday Tech Talks (designed for engineers by engineers).

In addition, there is some extremely popular “how to” content on the YugabyteDB YouTube channel.

If You Have Questions About Distributed SQL

If you have questions, ask them on the YugabyteDB Slack channel, Forum, GitHub, or Stack Overflow.

Next Steps

Ready to start exploring YugabyteDB features? You have some great options to get started. Run the database locally on your laptop (Quick Start), deploy it to your favorite cloud provider (Multi-node Cluster Deployment), sign up for a free YugabyteDB Managed cluster, or request a full-featured trial. It’s easy! Get started today!