See all blog posts

Expiring Data with Time to Live (TTL)

Time to Live (TTL) is a ScyllaDB (and Apache Cassandra) feature that is often used but not always fully understood. ScyllaDB provides the functionality to automatically delete expired data according to TTL. We recently updated our documentation and added a new lesson on this topic to ScyllaDB University.

In this post, I’ll cover the gist of TTL. The TTL can be set when defining a Table, or when using the INSERT and UPDATE queries. It’s measured in seconds. If the field is not updated within the TTL, it is deleted.

The expiration works at the individual column level, while a default for an entire table or row can also be used, as you will see below. This provides a lot of flexibility.

By default, the TTL value is null, which means that the data will not expire.

To define the TTL when creating the table we use default_time_to_live. For example, this would create a table and set it’s default_time_to_live value to 500:

CREATE TABLE heartrate (
    pet_chip_id uuid,
    name text,
    heart_rate int,
    PRIMARY KEY (pet_chip_id))
WITH default_time_to_live = 500;

The TTL value of 500 seconds is applied to all rows. Now when we insert data, each column will have a TTL of 500:

INSERT INTO heartrate(pet_chip_id, name, heart_rate) VALUES (c63e71f0-936e-11ea-bb37-0242ac130002, 'Rocky', 92);

To get the TTL value of a column we use the TTL() function:

SELECT name, heart_rate, TTL(name) as name_ttl, TTL(heart_rate) as heart_rate_ttl FROM heartrate WHERE pet_chip_id = c63e71f0-936e-11ea-bb37-0242ac130002;

We can see that the TTL is a bit lower than 500, as some time passes between inserting the data and performing the query. If we’d wait for another 500 seconds or so, and query for that row again, we would see that it would no longer be there.

It’s also possible to set the TTL for an entire row value using an INSERT (or UPSERT) command:

INSERT INTO heartrate(pet_chip_id, name, heart_rate) VALUES (c63e71f0-936e-11ea-bb37-0242ac130002, 'Rocky', 117) USING TTL 100;

Now if we perform a SELECT we’ll see the updated TTL:

SELECT name, heart_rate, TTL(name) as name_ttl, TTL(heart_rate) as heart_rate_ttl FROM heartrate WHERE pet_chip_id = c63e71f0-936e-11ea-bb37-0242ac130002;

If we want to update the TTL for a specific field:

UPDATE heartrate USING TTL 10000 SET heart_rate =
117 WHERE pet_chip_id = c63e71f0-936e-11ea-bb37-0242ac130002;

And now we can see that the heart_rate has a different TTL than the name field:

SELECT name, heart_rate, TTL(name) as name_ttl, TTL(heart_rate) as heart_rate_ttl FROM heartrate WHERE pet_chip_id = c63e71f0-936e-11ea-bb37-0242ac130002;

To remove the TTL value, set it to 0:

UPDATE heartrate USING TTL 0 SET heart_rate =
150 WHERE pet_chip_id = c63e71f0-936e-11ea-bb37-0242ac130002;

To learn more about TTL and see the full example, check out the lesson on ScyllaDB University.

About Guy Shtub

Head of Training: Guy is experienced in creating products that people love. Previously he co-founded two start-ups. Outside of the office, you can find him climbing, juggling and generally getting off the beaten path. Guy holds a B.SC. degree in Software Engineering from Ben Gurion University.