Time-To-Live

TidesDB can expire rows automatically, at the table level, the row level, or the session level. An expired row is filtered out of reads and reclaimed at compaction. Every TTL is expressed in seconds of lifetime from the time of the write.

Table-level TTL

Every row inserted into the table expires after the given number of seconds:

CREATE TABLE sessions (
  id INT PRIMARY KEY, token VARCHAR(100)
) ENGINE=TIDESDB TTL=3600;   -- one hour

INSERT INTO sessions VALUES (1, 'abc123');
-- after 3600 seconds this row is no longer returned

Per-row TTL

A column can be marked as the TTL source with the `TTL` field option. The value in that column is the row’s lifetime in seconds from insertion:

CREATE TABLE cache (
  id INT PRIMARY KEY, val VARCHAR(100), ttl_sec INT `TTL`=1
) ENGINE=TIDESDB;

INSERT INTO cache VALUES (1, 'short-lived', 5);      -- expires in 5 seconds
INSERT INTO cache VALUES (2, 'long-lived', 86400);   -- expires in a day
INSERT INTO cache VALUES (3, 'permanent', 0);        -- 0 defers to session and table TTL, unset here, so no expiry

A non-zero per-row value takes precedence. If it is zero, resolution falls through to the session TTL and then the table TTL. Updating a row recomputes its TTL from the new column value, which refreshes the expiration.

Session-level TTL

tidesdb_ttl applies a TTL to every INSERT and UPDATE on any TidesDB table for the session, even tables not created with a TTL option:

SET SESSION tidesdb_ttl = 300;                          -- five minutes
INSERT INTO events (id, data) VALUES (1, 'temporary');  -- expires in 300s
SET SESSION tidesdb_ttl = 0;                            -- back to the table default

SET STATEMENT scopes it to one statement:

SET STATEMENT tidesdb_ttl = 60 FOR INSERT INTO events (id, data) VALUES (2, 'one-minute');

Resolution order

For each written row the lifetime is resolved in this order, taking the first that is set:

The per-row `TTL` column value, when non-zero.
The session tidesdb_ttl, when non-zero.
The table-level TTL option, when non-zero.
Otherwise the row never expires.