A ClickHouse TTL is not a timer that quietly removes rows. It schedules work for the storage engine. If expired rows share a part with live rows, ClickHouse must read that part, apply the rule, and write a replacement.

That is the point of this article: every TTL should pay for itself. Retention should make old data cheap to drop. A rollup should save more repeated query work than it adds in background merges.

Why TTLs become merge pressure

A row-level retention rule looks small in DDL, but its physical unit of work is a part. As rows become eligible at different times, TTL merges can repeatedly rewrite parts while competing with ingestion and normal compaction for CPU, memory, and I/O.

I removed one such TTL after TTLDropMerge became a recurring source of memory pressure. Keeping the rule inside the table was less valuable than making cleanup predictable.

Cheap deletion requires whole parts

ttl_only_drop_parts = 1 restricts TTL deletion to whole parts. If a part mixes live and expired rows, ClickHouse leaves the part unchanged, including its expired rows. It drops the part only after every row has expired. Partition boundaries therefore decide both how cheap deletion is and how long expired rows may remain.

When retention is the goal, I try to align expiration with a practical partition boundary and drop whole partitions or parts. This does not mean partitioning as finely as possible: too many partitions create a different operational problem. The boundary should match the lifecycle, not individual rows.

Rollups can justify the merge

Another workload repeatedly merged approximate-unique aggregate states across a moving history window. Recent data changed constantly; older daily buckets did not. In this case, spending background work once was worthwhile because it removed repeated work from every read.

The layout kept today's aggregate state live, rolled each completed day into a scalar, then deleted it after the required retention window:

CREATE TABLE daily_activity
(
    user_id String,
    day Date,
    unique_events AggregateFunction(uniqCombined64, UInt64),
    total SimpleAggregateFunction(max, UInt64) DEFAULT 0
)
ENGINE = AggregatingMergeTree
PARTITION BY day
ORDER BY user_id
TTL
    day + INTERVAL 1 DAY
        GROUP BY user_id
        SET total = uniqCombined64Merge(unique_events),
    day + INTERVAL 8 DAY DELETE;

Reads then paid the aggregate-state merge cost only for the live day. Historical days became a sum over small scalar values. This TTL still caused merges, but those merges bought a smaller and cheaper read path.

Rollups introduce a correctness boundary

Late-data note: this example assumes that no rows arrive after a bucket has rolled up. The TTL does not retain the complete aggregate state: omitted columns use any, and a later TTL merge can recompute and overwrite total from an incomplete state. SimpleAggregateFunction(max, ...) does not prevent that overwrite. If late rows are possible, preserve the mergeable state through the rollup or use explicit readiness and rebuild the bucket.

How I choose

Before adding a TTL, I decide which physical outcome I want:

  • Drop whole parts when the requirement is simple retention.
  • Use a rollup only when its merge cost removes meaningful query work.
  • Keep explicit cleanup when expiration cannot align with parts.

After deployment, I watch eligible parts, merge queue age, memory, rewritten bytes, and rejected inserts. A TTL is healthy when that work stays bounded and produces an intentional storage or query benefit.

Rule of thumb

Treat a TTL as scheduled merge work. Design the table so the work is either avoided through whole-part deletion or justified by a useful transformation.