Skip to main content

Manipulations with Table TTL

Note

If you are looking for details on using TTL for managing old data, check out the Manage Data with TTL user guide. The docs below demonstrate how to alter or remove an existing TTL rule.

MODIFY TTL​

You can change table TTL with a request of the following form:

ALTER TABLE [db.]table_name [ON CLUSTER cluster] MODIFY TTL ttl_expression;

REMOVE TTL​

TTL-property can be removed from table with the following query:

ALTER TABLE [db.]table_name [ON CLUSTER cluster] REMOVE TTL

Example

Consider the table with table TTL:

CREATE TABLE table_with_ttl
(
    event_time DateTime,
    UserID UInt64,
    Comment String
)
ENGINE MergeTree()
ORDER BY tuple()
TTL event_time + INTERVAL 3 MONTH
SETTINGS min_bytes_for_wide_part = 0;

INSERT INTO table_with_ttl VALUES (now(), 1, 'username1');

INSERT INTO table_with_ttl VALUES (now() - INTERVAL 4 MONTH, 2, 'username2');

Run OPTIMIZE to force TTL cleanup:

OPTIMIZE TABLE table_with_ttl FINAL;
SELECT * FROM table_with_ttl FORMAT PrettyCompact;

Second row was deleted from table.

┌─────────event_time────┬──UserID─┬─────Comment──┐
│   2020-12-11 12:44:57 │       1 │    username1 │
└───────────────────────┴─────────┴──────────────┘

Now remove table TTL with the following query:

ALTER TABLE table_with_ttl REMOVE TTL;

Re-insert the deleted row and force the TTL cleanup again with OPTIMIZE:

INSERT INTO table_with_ttl VALUES (now() - INTERVAL 4 MONTH, 2, 'username2');
OPTIMIZE TABLE table_with_ttl FINAL;
SELECT * FROM table_with_ttl FORMAT PrettyCompact;

The TTL is no longer there, so the second row is not deleted:

┌─────────event_time────┬──UserID─┬─────Comment──┐
│   2020-12-11 12:44:57 │       1 │    username1 │
│   2020-08-11 12:44:57 │       2 │    username2 │
└───────────────────────┴─────────┴──────────────┘

See Also