Time to Live (TTL)

For some workloads have rows that need to be removed after a certain period of time. The usual way would be to have a created time column, and issue deletes regularly to remove old rows. This is not ideal because deletes can cause a number of problems in MyRocks. We have to pay the CPU cost of processing the delete statement, as well the IO cost of writing the delete markers into the database. Delete markers can also make scans slower.

Instead, we can leverage the compaction filter to do our deletes.

DDL Syntax

TTL is defined through table comments and has two variants, implicit and explicit timestamps.

CREATE TABLE t1 (a INT, b INT, c INT, PRIMARY KEY (a), KEY(b)) ENGINE=ROCKSDB COMMENT "ttl_duration=3600;";

CREATE TABLE t2 (a INT, b INT, c INT, ts BIGINT UNSIGNED NOT NULL, PRIMARY KEY (a), KEY(b)) ENGINE=ROCKSDB COMMENT "ttl_duration=3600;ttl_col=ts;";

In the above examples, we set ttl_duration to 3600 meaning that we expect rows older than 3600 seconds to be removed from the database.

The t1 table schema uses implicit timestamps, meaning that the created time is implicitly recorded as the current time when the record is inserted or updated. The recorded timestamp is stored in the record, but is not visible to the user.

The t2 table uses explicit timestamps, and this is denoted by the ttl_col qualifier found in the table comment. This means that the creation time used for TTL is directly taken from the ts column.

Read filtering

For repeatable read isolation level, or if you have TTL defined on a table with secondary keys, read filtering must be turned on. This can be done by setting the rocksdb_enable_ttl_read_filtering variable (which is already on by default).