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 ENGINE_ATTRIBUTE='{"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 its own ENGINE_ATTRIBUTE. 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 ENGINE_ATTRIBUTE='{"ttl": true}'
) 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

At most one column may be marked as the TTL source; a table naming two is refused at CREATE, because which one won would otherwise depend on the order the columns happened to be read in.

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

There is no syntax here for scoping a variable to a single statement, so a one-statement TTL is written by setting the variable and putting it back:

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

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.