ClickHouse TTL: Merge-Driven Retention, Not a Cron Job

TTL Is Merge-Driven, Not a Cron Sweep

You have a ClickHouse table that must keep 30 days of raw events and then silently disappear. No scheduled scripts, no nightly cron. The built-in TTL promise feels like a set-it-and-forget-it solution, but in practice retention can silently stall when parts are skipped—the useful takeaway is that TTL in MergeTree is evaluated per-part during background merges, not on a wall-clock schedule, so retention is best-effort and can leave data behind after downtime.

How TTL Actually Runs in MergeTree

MergeTree stores data in immutable parts. Each part spans a range of primary keys and a partition. Background merges rewrite parts into larger ones. TTL expressions are compiled into the table definition and evaluated when a part becomes eligible for a merge. If the expression evaluates to true, ClickHouse can DELETE matching rows or DROP the entire part. Both actions happen as part of a merge, not as a separate clock-driven sweep.

What Gets Compiled and What Gets Skipped

Operators can inspect the compiled policy via the system.ttl view. The view shows the expression ClickHouse will evaluate and whether a DROP action is configured.

SELECT database, table, ttl_expression, is_ttl_dropped FROM system.ttl WHERE database = 'analytics' AND table = 'events';

By design, TTL skips active or recently inserted parts. This prevents data loss during in-flight writes, but it also means a part written just before a retention window can be left untouched until it merges. If the server was offline during a retention window, those parts may not be evaluated until the next merge cycle, so retention is best-effort, not instant.

Worked Example: 30-Day Drop Policy

Create a table with a timestamp column that drives retention. The following statements assume you have CREATE and ALTER privileges on a ClickHouse server.

CREATE TABLE analytics.events (event_date DateTime, user_id UInt64, payload String) ENGINE = MergeTree() PARTITION BY toYYYYMM(event_date) ORDER BY (user_id, event_date);

Add a TTL policy that drops the whole part when the event date is older than 30 days.

ALTER TABLE analytics.events MODIFY TTL toDate(event_date) + INTERVAL 30 DAY DELETE;

Risk: MODIFY TTL rewrites table metadata. Run it during low write periods on high-throughput tables because the first merges that apply the policy can increase I/O and query latency.

Verify compilation.

SELECT database, table, ttl_expression, is_ttl_dropped FROM system.ttl WHERE database = 'analytics' AND table = 'events';

You should see a row with the expression toDate(event_date) + INTERVAL 30 DAY. If the expression is missing, the ALTER did not apply.

Check parts before and after a merge cycle.

SELECT partition, name, active, bytes FROM system.parts WHERE database = 'analytics' AND table = 'events' ORDER BY bytes DESC LIMIT 10;

A practical way to quantify cleanup is to compare volume metrics.

SELECT count() FROM analytics.events; SELECT sum(bytes) FROM system.parts WHERE database='analytics' AND table='events' AND active;

Run the queries after the background merge thread has had time to process. The count and sum(bytes) should trend down as expired parts are dropped. Do not assume immediate results; merges are scheduled.

Trade-Offs and Limitations

• Frequent TTL-driven compactions on high-throughput tables can increase I/O load and query latency during peak periods. Consider aligning TTL with partition granularity so whole partitions can be dropped.
• With ReplacingMergeTree, ambiguous primary keys combined with TTL can result in unexpected data deletion across partitions if key semantics are not carefully defined. TTL acts on rows, not on logical versions.
• Because active parts are intentionally skipped, manual intervention may be required for parts that were not evaluated due to downtime. There is no forced immediate sweep; you can trigger merges with OPTIMIZE, but that adds load.

Actionable close: define TTL at table creation when possible, keep partitions aligned with retention windows, monitor system.ttl for compiled expressions, and track part count and bytes over time to confirm cleanup. Treat TTL as a retention contract with eventual semantics, not a precise deletion timestamp.