How to Use TTL in ClickHouse
Last updated July 25, 2026 · By the SaturnSQL team
A table-level TTL clause like TTL event_time + INTERVAL 90 DAY deletes expired rows automatically; a TTL on a single column resets just that column to its default. Expiry is applied during background merges, so data disappears eventually, not at the exact moment.
Row TTL
CREATE TABLE session_logs
(
event_time DateTime,
user_id UInt64,
payload String
)
ENGINE = MergeTree
ORDER BY (user_id, event_time)
TTL event_time + INTERVAL 90 DAY;Add or change TTL on an existing table
ALTER TABLE session_logs MODIFY TTL event_time + INTERVAL 30 DAY;Column TTL
A column TTL clears only that column after the interval, keeping the row. Force expired data out immediately with ALTER TABLE ... MATERIALIZE TTL or OPTIMIZE TABLE ... FINAL.
CREATE TABLE events_raw
(
event_time DateTime,
user_id UInt64,
raw_payload String TTL event_time + INTERVAL 7 DAY
)
ENGINE = MergeTree
ORDER BY (user_id, event_time);Run this in SaturnSQL
SaturnSQL is a browser-based SQL editor for teams: shared query library, schema-aware autocomplete, and scheduled exports to Google Sheets and Slack.
Try it free