TTL for data expiration

• YSQL
• YCQL

In YCQL there are two types of TTL, the table level TTL and column level TTL. The column level TTLs are storedwith the value of the column. The table level TTL is not stored in DocDB (it is storedin yb-master system catalog as part of the table’s schema). If no TTL is present at the column’s value,the table level TTL acts as the default value.

Furthermore, YCQL has a distinction between rows created using Insert vs. Update. We keep track ofthis difference (and row level TTLs) using a “liveness column”, a special system column invisible tothe user. It is added for inserts, but not updates: making sure the row is present even if allnon-primary key columns are deleted only in the case of inserts.

Table level TTL

YCQL allows the TTL property to be specified at the table level.In this case, we do not store the TTL on a per KV basis in DocDB; but the TTL is implicitly enforcedon reads as well as during compactions (to reclaim space).Table level TTL can be defined with default_time_to_live property.

Below, we will look at how the row-level TTL is achieved in detail.

Row level TTL

YCQL allows the TTL property to be specified at the level of each INSERT/UPDATE operation.Row level TTL expires the whole row. The value is specified at insert/update time with USING TTL clause.In such cases, the TTL is stored as part of the DocDB value. A simple query would be:

INSERT INTO pageviews(path) VALUES ('/index') USING TTL 10;
SELECT * FROM pageviews;
 path   | views
--------+-------
 /index |  null
(1 rows)

After 10 seconds, the row is expired:

SELECT * FROM pageviews;
 path | views
------+-------
(0 rows)

Column level TTL

YCQL also allows to set column level TTL. In such cases, the TTL is stored as part of the DocDB column value.But we can set it only when updating the column:

INSERT INTO pageviews(path,views) VALUES ('/index', 10);
SELECT * FROM pageviews;
 path   | views
--------+-------
 /index |  10
(1 rows)
UPDATE pageviews USING TTL 10 SET views=10 WHERE path='/index';

After 10 seconds, querying for the rows the views column will return NULL but notice that the row still exists:

SELECT * FROM pageviews;
 path   | views
--------+-------
 /index |  null
(1 rows)

TTL related commands & functions

There are several ways to work with TTL:

• Table level TTL with default_time_to_live property.
• Expiring rows with TTL
• TTL function to return number of seconds until expiration
• WriteTime function returns timestamp when row/column was inserted
• Update row/column TTL to update the TTL of a row or column