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.
- 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);
- 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.
Explore our library of distributed SQL tips and tricks and general “how to” information on the YugabyteDB blog and on our DEV Community Blogs.
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.
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!