ClickHouse TTL

TTL (time-to-live) in ClickHouse is a set of declarative rules for what happens to data once it reaches a certain age. The name suggests deletion, but TTL covers more than that: it can delete rows, drop individual columns, move data to cheaper storage, recompress it, or roll it up into aggregates. The point is to manage the data lifecycle inside the database instead of running external cron jobs that issue DELETEs and ALTERs.

TTL applies to MergeTree-family tables and works off an expression that resolves to a Date or DateTime. The expression is almost always a timestamp column plus an INTERVAL.

Row-Level TTL: Deleting Old Data

The most common use is dropping rows after they expire. Declare it at the end of the table definition.

CREATE TABLE events
(
    event_time DateTime,
    user_id    UInt64,
    payload    String
)
ENGINE = MergeTree
PARTITION BY toYYYYMMDD(event_time)
ORDER BY (user_id, event_time)
TTL event_time + INTERVAL 30 DAY;

Rows where event_time is more than 30 days old become eligible for deletion. You can also add a WHERE clause to delete only a subset:

TTL event_time + INTERVAL 30 DAY DELETE WHERE payload = ''

Column-Level TTL

A TTL clause can sit on a single column instead of the whole row. When it expires, the column is reset to its default value for the affected rows while the rest of the row stays. This is useful for dropping bulky fields (raw payloads, debug blobs) while keeping the lightweight columns around longer.

CREATE TABLE example1
(
    timestamp DateTime,
    x UInt32 TTL timestamp + INTERVAL 1 MONTH,
    y String TTL timestamp + INTERVAL 1 DAY,
    z String
)
ENGINE = MergeTree
ORDER BY tuple();

Here y is wiped a day after its timestamp, x a month after, and z is kept indefinitely.

Moving Data Between Storage Tiers

With a storage policy that defines multiple volumes (for example fast SSD and cheaper HDD or object storage), TTL can move data between them as it ages. This is tiered storage: hot data on fast disks, cold data on cheap disks, all driven by one rule.

TTL
    event_time + INTERVAL 7 DAY TO VOLUME 'hot',
    event_time + INTERVAL 30 DAY TO VOLUME 'cold',
    event_time + INTERVAL 365 DAY DELETE

TO DISK 'name' targets a specific disk; TO VOLUME 'name' targets a named volume in the policy. The moves and the final delete are all expressed in a single TTL clause.

Rollups with TTL GROUP BY

TTL can aggregate expired rows instead of deleting them, collapsing fine-grained old data into coarser summaries. The GROUP BY must be a prefix of the table's ORDER BY key, and the aggregated columns are set with SET.

CREATE TABLE metrics
(
    event_date Date,
    name       String,
    value      UInt64
)
ENGINE = MergeTree
ORDER BY (name, event_date)
TTL event_date + INTERVAL 90 DAY
    GROUP BY name
    SET value = sum(value);

After 90 days, rows collapse to one row per name with the summed value, shrinking storage while keeping long-term totals. Note that you can only specify one GROUP BY rollup rule; multiple GROUP BY or DELETE WHERE rules in the same TTL are not supported.

Recompression

TTL can switch the compression codec on aging data, trading CPU for a smaller footprint on cold data that is rarely read.

TTL
    event_time + INTERVAL 7 DAY RECOMPRESS CODEC(ZSTD(1)),
    event_time + INTERVAL 30 DAY RECOMPRESS CODEC(ZSTD(6));

Fresh data stays on a fast, light codec; older data is recompressed with a higher ZSTD level for better ratio.

When TTL Actually Fires

This is the part that surprises people: TTL does not run on a timer the moment data expires. Deletion, moving, and rollups happen during background merges. If a table is not actively merging, expired data can sit around past its TTL.

Two settings nudge this along: merge_with_ttl_timeout (for delete and move TTLs) and merge_with_recompression_ttl_timeout (for recompression TTLs). By default TTL rules are applied at least roughly every 4 hours. Lower these if you need expiry to happen sooner.

To force it immediately, run a merge by hand:

OPTIMIZE TABLE events FINAL;

OPTIMIZE triggers an unscheduled merge, and FINAL forces reoptimization even if the table is already a single part. This is heavy on large tables, so reserve it for one-off cleanup, not routine operation.

Partition for Efficient Expiry

For row-level delete TTL, partition the table by the same time field used in the TTL, at a granularity that matches the TTL period:

• TTL in days or weeks: partition by day with toYYYYMMDD(date_field).
• TTL in months or years: partition by month with toYYYYMM(date_field) or toStartOfMonth(date_field).

When the partition key aligns with the TTL expression, ClickHouse drops whole expired partitions at once instead of rewriting data parts to strip out individual rows. Dropping a partition is far cheaper than rewriting parts, so this alignment is the single biggest lever on TTL cost.

Altering and Materializing TTL

TTL rules are not fixed at creation. You can add or change them later:

ALTER TABLE events MODIFY TTL event_time + INTERVAL 60 DAY;

By default, modifying TTL does not immediately rewrite existing data. To apply a new rule to data already on disk, materialize it:

ALTER TABLE events MATERIALIZE TTL;

If you only want ClickHouse to recalculate which data is expired without a full rewrite, the materialize_ttl_recalculate_only setting controls that behavior.

Common Mistakes

• Expecting instant deletion. TTL fires during merges, not at the expiry instant. Without merges, expired rows linger; tune merge_with_ttl_timeout or run OPTIMIZE ... FINAL for one-off cleanup.
• Not partitioning by the TTL field. Without aligned partitioning, ClickHouse rewrites parts to remove rows instead of dropping whole partitions, which is much more expensive.
• Using a non-Date/DateTime expression. The TTL expression must resolve to Date or DateTime. An integer epoch column will not work directly without conversion.
• Assuming MODIFY TTL rewrites old data. It does not by default. Run ALTER TABLE ... MATERIALIZE TTL to apply a new rule to existing parts.
• Trying multiple GROUP BY rollup rules. Only one GROUP BY rollup is allowed per table TTL.

When you are layering several TTL actions (move, then recompress, then delete) on one table, Mako's AI autocomplete can help you draft and read the multi-rule TTL clause so the intervals and targets line up the way you intend.

Related Guides

• ClickHouse Materialized Views for building the pre-aggregated tables that TTL rollups feed into.
• ClickHouse Date and Time Functions for the toYYYYMM/toStartOfMonth expressions used in partitioning and TTL.
• ClickHouse Aggregate Functions for the sum and other aggregates used in TTL GROUP BY rollups.

Mako connects to ClickHouse with AI-powered autocomplete for writing and exploring analytical queries. Try it free at mako.ai.