TTL

Analytical data almost always has a shelf life: raw events matter most in the first days, are worth keeping cheaply for a while longer, and eventually are worth nothing except storage cost. TTL (time-to-live) lets you encode that lifecycle directly into the table, instead of writing a cron job that runs DELETE statements.

Declaring a lifecycle

CREATE TABLE events
(
    event_time DateTime,
    payload    String
)
ENGINE = MergeTree
ORDER BY event_time
TTL event_time + INTERVAL 7 DAY TO VOLUME 'cold',
    event_time + INTERVAL 30 DAY DELETE
SETTINGS storage_policy = 'hot_cold';

Enforced by merges, not a scheduler

TTL rules aren't evaluated by a separate cron-like process — they're checked whenever a background merge touches a part. Expired rows are dropped (or moved) as a side effect of the normal merge cycle. This means TTL cleanup has no fixed schedule: a part that rarely gets touched by merges may hold expired rows a bit longer than the TTL literally states, though ClickHouse also runs periodic housekeeping merges specifically to catch this.

What TTL can do besides delete

• TTL ... DELETE — drop rows once the expression is in the past.
• TTL ... TO VOLUME 'name' / TO DISK 'name' — move the containing part to cheaper storage (e.g. HDD or S3-backed disks) without deleting anything, given a configured storage policy with multiple tiers.
• TTL ... GROUP BY — instead of deleting expired rows, roll them up into an aggregate first, then delete the raw rows. Useful for "keep raw events for 7 days, keep hourly rollups forever."
• Column-level TTL — expire just one column (setting it to its default) while keeping the rest of the row.