Skip to main content

Manipulations with Table TTL

MODIFY TTL​

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

ALTER TABLE table_name MODIFY TTL ttl_expression;

REMOVE TTL​

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

ALTER TABLE table_name 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