Skip to content
ClickHouse Docs
ClickHouse DocsClickHouse Docs
49.6kGet Started

ALTER TABLE ... MODIFY TTL

View as MarkdownEdit this pageReport an issueSet up docs MCP

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

Navigation