ClickHouseintermediate

ClickHouse TTL

Expiring rows, moving parts between storage tiers, and rolling up old data with TTL expressions.

2 min readIntermediateUpdated 31 Jul 2026Edit this page

TTL expressions let ClickHouse delete, move or aggregate data automatically as it ages. They run as part of background merges.

Row expiry

CREATE TABLE events
(
    event_time DateTime,
    event_date Date MATERIALIZED toDate(event_time),
    tenant_id  UInt64,
    payload    String
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_date)
ORDER BY (tenant_id, event_time)
TTL event_date + INTERVAL 90 DAY DELETE;

-- Add or change TTL on an existing table.
ALTER TABLE events MODIFY TTL event_date + INTERVAL 180 DAY DELETE;

Column expiry

Individual columns can expire earlier than the row, which is useful for large payloads you only need recently:

CREATE TABLE events
(
    event_time DateTime,
    tenant_id  UInt64,
    summary    String,
    payload    String TTL event_time + INTERVAL 7 DAY
)
ENGINE = MergeTree
ORDER BY (tenant_id, event_time)
TTL event_time + INTERVAL 365 DAY DELETE;

After seven days the payload column is reset to its default while the row and its summary remain.

Tiered storage

TTL can move parts between volumes defined in the storage policy — hot NVMe for recent data, cheaper disks or object storage for older data:

ALTER TABLE events MODIFY TTL
    event_date + INTERVAL 7 DAY  TO VOLUME 'warm',
    event_date + INTERVAL 30 DAY TO VOLUME 'cold',
    event_date + INTERVAL 365 DAY DELETE;

Rollup on expiry

TTL with GROUP BY aggregates old rows in place rather than deleting them — full detail recently, summaries for history:

CREATE TABLE metrics
(
    ts        DateTime,
    tenant_id UInt64,
    metric    LowCardinality(String),
    value     Float64
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(ts)
ORDER BY (tenant_id, metric, ts)
TTL ts + INTERVAL 30 DAY
    GROUP BY tenant_id, metric
    SET ts = toStartOfHour(max(ts)), value = avg(value);

The GROUP BY must be a prefix of the table's ORDER BY.

Data Retention

TTL and partition dropping solve the same problem differently:

	TTL	Drop partition
Granularity	Per row or column	Whole partition
Cost	Rewrites parts during merges	Metadata only, instant
Timing	Eventual, when merges run	Immediate and explicit
Best for	Column expiry, tiering, rollups	Bulk range deletion

For simple age-based expiry of a time-partitioned table, dropping partitions is cheaper. Use TTL when you need per-column expiry, storage tiering, or aggregation of old data.

TTL work happens during merges and is not immediate

Rows past their TTL remain on disk until a merge processes their part, so disk usage does not fall the moment data expires. merge_with_ttl_timeout controls how often TTL merges are considered.

To force it — knowing that it rewrites parts and consumes I/O:

ALTER TABLE events MATERIALIZE TTL;
OPTIMIZE TABLE events FINAL;   -- expensive on a large table; do not schedule casually

-- Check what TTL is set and when parts will next be processed.
SELECT table, delete_ttl_info_min, delete_ttl_info_max, move_ttl_info
FROM system.parts WHERE active AND table = 'events' LIMIT 5;