TTL for data expiration
In YCQL there are two types of TTL, the table level TTL and column level TTL. The column level TTLs are stored with the value of the column. The table level TTL is not stored in DocDB (it is stored in 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 of this difference (and row level TTLs) using a "liveness column", a special system column invisible to the user. It is added for inserts, but not updates: making sure the row is present even if all non-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, you do not store the TTL on a per KV basis in DocDB; but the TTL is implicitly enforced
on reads as well as during compactions (to reclaim space).
Table level TTL can be defined with default_time_to_live property.
Below, you 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 you 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_liveproperty. - Expiring rows with TTL
TTLfunction to return number of seconds until expirationWriteTimefunction returns timestamp when row/column was inserted- Update row/column TTL to update the TTL of a row or column