Leveraging time to live (TTL)

In both MySQL and Postgres, expiring records after a set period of time takes a couple of timestamps and a little creativity. With Cassandra, or in this case the YugabyteDB ycql API, TTL (time to live) can be leveraged to handle this functionality, simplifying both the table definition and amount of work required by your code.

Here’s a short test to demonstrate. Reminder that you can set up a quick 3-node cluster using the code here: https://github.com/dataindataout/xtest_ansible.

First, create a table with a simple ID and a string of some kind (maybe a user login that you want to expire after a set period unless it continues to be used).

CREATE TABLE test (id int PRIMARY KEY, mystring text);

Insert some strings set to expire after the defined TTL. A very short TTL (10 seconds) is defined here to make the test simple; in a real use case, this TTL would be much longer.

insert into test (id, mystring) values (1, 'asdf1') using ttl 10;
insert into test (id, mystring) values (2, 'asdf2') using ttl 10;
insert into test (id, mystring) values (3, 'asdf3') using ttl 10;

select * from test;
id | mystring
----+----------
1 | asdf1
2 | asdf2
3 | asdf3

After 10 seconds, the data has been expired.

select * from test;
id | mystring
----+----------

Run through the example again.

insert into test (id, mystring) values (1, 'asdf1') using ttl 10;
insert into test (id, mystring) values (2, 'asdf2') using ttl 10;
insert into test (id, mystring) values (3, 'asdf3') using ttl 10;

Wait 2 seconds and update one of the records before it expires. As an aside, another difference with Cassandra and the YugabyteDB ycql API is that an insert is actually an upsert: if the key doesn’t exist, the record will be inserted; if the key does exist, the record will be updated. This feature allows you to issue the same query repeatedly as needed.

insert into test (id, mystring) values (3, 'asdf3a') using ttl 10;

After 10 seconds, the records that weren’t updated will have expired.

select * from test;
id | mystring
----+----------
3 | asdf3a

And just in case you ever need to get the timestamps for things like “created date” or “updated date,” you can still get those from the table metadata, like this:

select id, mystring, ttl(mystring), writetime(mystring) from test;

id | mystring | ttl(mystring) | writetime(mystring)
----+----------+---------------+---------------------
1 | asdf1 | 6 | 1694549846655463
2 | asdf2 | 6 | 1694549846662280
3 | asdf3 | 6 | 1694549846805859