ClickHouse TTL: How Deletes Are Scheduled and 6 Reasons They Fail

A ClickHouse TTL clause is not a delete queue. There is no background reaper walking rows and checking expiry timestamps. A TTL expression on a MergeTree table is a promise that expired rows will be removed during some future merge — and whether that merge ever gets scheduled depends on part-level metadata written at insert time, a per-partition timer, a dedicated merge-selector cascade, and two thread-pool quotas. When someone reports “TTL didn’t fire”, the answer is almost always hiding in one of those four places.

This post walks through the actual ClickHouse TTL scheduling path, verified against the current source tree (master, 26.x line) and demonstrated on a live ClickHouse 26.7 build. We will cover the three artifacts that decide everything: merge_with_ttl_timeout, ttl_only_drop_parts, and the delete_ttl_info metadata in system.parts — and close with a diagnostic checklist we use when retention has visibly stalled on production clusters.

ClickHouse TTL deletes are a merge byproduct, not an operation

Two moments exist where ClickHouse physically removes expired rows: when a part is written (rows already past their TTL at INSERT time are dropped as the part is formed), and when a part participates in a merge. Between those two moments, expired rows sit on disk and remain fully visible to queries — a ClickHouse TTL is storage reclamation, not a read-time filter. The ClickHouse knowledgebase is explicit that TTL application is eventual, but the operational consequences only make sense once you see what is stored per part.

For every ClickHouse TTL expression, each part carries a small metadata file, ttl.txt, holding the minimum and maximum values of each TTL expression evaluated over that part’s rows (in-memory this is MergeTreeDataPartTTLInfos, defined in MergeTreeDataPartTTLInfo.h). For the table-level delete TTL, those min/max values surface in system.parts as delete_ttl_info_min and delete_ttl_info_max. Here is a table on ClickHouse 26.7 with a 90-day TTL and two parts — one 45 days old, one fresh:

CREATE TABLE sensor_readings
(
    reading_time  DateTime,
    sensor_id     UInt32,
    reading_value Float64
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(reading_time)
ORDER BY (sensor_id, reading_time)
TTL reading_time + INTERVAL 90 DAY;

SELECT
    name,
    partition,
    rows,
    delete_ttl_info_min,
    delete_ttl_info_max
FROM system.parts
WHERE table = 'sensor_readings' AND active
ORDER BY name;

┌─name─────────┬─partition─┬───rows─┬─delete_ttl_info_min─┬─delete_ttl_info_max─┐
│ 202607_1_1_0 │ 202607    │ 100000 │ 2026-10-13 08:00:00 │ 2026-10-13 08:59:59 │
│ 202608_2_2_0 │ 202608    │  50000 │ 2026-11-27 17:38:33 │ 2026-11-27 18:38:32 │
└──────────────┴───────────┴────────┴─────────────────────┴─────────────────────┘

Read those two columns the way the scheduler does. delete_ttl_info_min is the moment the part starts containing expired rows — the earliest candidate time for a row-delete merge. delete_ttl_info_max is the moment the entire part is expired — the earliest time the part can simply be dropped. Everything the TTL scheduler decides, it decides from these two timestamps. It never re-evaluates the TTL expression against actual rows to make a scheduling decision.

Keep that in mind; it is the root cause of the nastiest “TTL didn’t fire” case we see, and we will demonstrate it below.

How the ClickHouse TTL scheduler picks a merge

Each background scheduling iteration, the merge selection code in MergeSelectorApplier (source) tries TTL-driven selection before falling through to the regular size-based SimpleMergeSelector. The cascade in current master has three stages, in strict priority order:

The ClickHouse TTL merge selection cascade as implemented in MergeSelectorApplier.cpp and TTLMergeSelector.cpp, master (26.x line).

The selector classes live in TTLMergeSelector.cpp. The base class comment describes the algorithm plainly: find the part with the earliest expired TTL, then greedily extend the merge range around it. The two delete-oriented subclasses differ in exactly one line — which timestamp they consult:

// src/Storages/MergeTree/Compaction/MergeSelectors/TTLMergeSelector.cpp

time_t TTLPartDropMergeSelector::getTTLForPart(const PartProperties & part) const
{
    return part.general_ttl_info->part_max_ttl;   // whole part expired?
}

time_t TTLRowDeleteMergeSelector::getTTLForPart(const PartProperties & part) const
{
    return part.general_ttl_info->part_min_ttl;   // any row expired?
}

merge_with_ttl_timeout: the ClickHouse TTL rate limiter

ClickHouse TTL delete merges are expensive — a TTLDelete merge is a full rewrite of every selected part, decompressing and re-filtering all rows. To stop a table with a rolling TTL boundary from re-merging the same partition continuously, the scheduler keeps a per-partition due-time map. The update happens in MergeTreeDataMergerMutator::updateTTLMergeTimes when a row-delete merge is assigned:

// src/Storages/MergeTree/MergeTreeDataMergerMutator.cpp
case MergeType::TTLDelete:
{
    next_delete_ttl_merge_times_by_partition[partition_id]
        = current_time + (*settings)[MergeTreeSetting::merge_with_ttl_timeout];
    ...
}

Three properties of this mechanism explain most confusing field behavior:

The quotas from the diagram add a cluster-wide dimension: max_number_of_merges_with_ttl_in_pool (default 2) caps concurrent TTL merges per server against the background pool, and on replicated tables max_replicated_merges_with_ttl_in_queue (default 1) caps TTL merges a replica will queue. On an ingest-heavy cluster where regular merges saturate the background pool, TTL merges compete for those few slots — this is the classic silent-stall pattern on under-provisioned clusters. Both are MergeTree-level settings; both can be inspected per table via system.merge_tree_settings.

Replication adds one more subtlety to ClickHouse TTL scheduling. On ReplicatedMergeTree, merge assignment goes through the replication queue, so a TTL merge is decided once and executed on every replica — but the merge_due_times map is in-memory and per server process, and each replica also enforces its own max_replicated_merges_with_ttl_in_queue when it considers queueing new TTL work. In practice this means replicas can disagree about when the next ClickHouse TTL merge is due after restarts, and a rolling restart of the cluster often produces a visible burst of TTLDelete merges as fresh processes start with empty due-time maps.

ttl_only_drop_parts: two very different deletion shapes

The cascade above hides an important asymmetry in the ClickHouse TTL delete path. Dropping a fully expired part is a metadata operation — no decompression, no rewrite, effectively free, and in current versions it is not even subject to the merge_with_ttl_timeout gate or merge size limits. Deleting expired rows out of a partially expired part is a full rewrite of that part. ttl_only_drop_parts (default 0) chooses between them:

-- current vs proposed, per table (MergeTree setting, no restart required)
ALTER TABLE sensor_readings
MODIFY SETTING ttl_only_drop_parts = 1;   -- default: 0

With ttl_only_drop_parts = 1, the row-delete selector is disabled entirely — the guard in MergeSelectorApplier.cpp is explicit: if (!ctx.merge_tree_settings[MergeTreeSetting::ttl_only_drop_parts]) wraps priority 2. Parts are then removed only when every row in the part is expired, i.e. when delete_ttl_info_max passes. Regular merges still apply row-level TTL filtering as a side effect when they happen to pick up a part whose part_min_ttl has passed, so expired rows do still disappear eventually — but no merge will be scheduled for that purpose.

The two ClickHouse TTL deletion shapes: a row rewrite priced by part size versus a metadata-only drop.

This is where partition design and TTL design stop being separate decisions. ttl_only_drop_parts = 1 works when merged parts converge to a time range comfortably narrower than the TTL window — the natural fit is time-based partitioning where the partition granularity divides the TTL cleanly (daily partitions with a 30-day TTL). It fails quietly when parts span wide time ranges relative to the TTL: a part whose rows cover 40 days can never fully expire under a 30-day TTL until its newest row ages out, so reclamation runs one part-width behind expectation, permanently.

If you enable it, verify the assumption with data, not intuition:

SELECT
    table,
    max(delete_ttl_info_max - delete_ttl_info_min)      AS widest_part_span_s,
    max(now() - delete_ttl_info_max)                    AS oldest_fully_expired_for_s
FROM system.parts
WHERE active
  AND delete_ttl_info_min > toDateTime(0)
GROUP BY table
ORDER BY oldest_fully_expired_for_s DESC;

If widest_part_span_s approaches your TTL window, ttl_only_drop_parts = 1 will hold data far longer than the TTL suggests. The trade is real in both directions: we have measured double-digit percentages of cluster write amplification going to TTLDelete rewrites on tables where drop-parts would have been free, and we have seen drop-parts configurations retain 2× the intended data volume because partitioning did not align. Test on your own part-size distribution before committing either way.

delete_ttl_info is the authority — the MODIFY TTL trap

Here is the mechanism behind the least intuitive ClickHouse TTL failure mode. The scheduler and the merge itself trust the part’s stored TTL info unconditionally. In MergeTask.cpp, the decision to filter expired rows during any merge is:

// src/Storages/MergeTree/MergeTask.cpp
const auto & local_part_min_ttl = global_ctx->new_data_part->ttl_infos.part_min_ttl;
if (global_ctx->metadata_snapshot->hasAnyTTL()
    && local_part_min_ttl
    && local_part_min_ttl <= global_ctx->time_of_merge)
        ctx->need_remove_expired_values = true;

Note what is absent: no re-evaluation of the current TTL expression against the rows. If ttl.txt was computed under an old TTL definition, every scheduling and merge decision runs on stale timestamps. The companion check, IMergeTreeDataPart::checkAllTTLCalculated, only verifies that TTL info exists for the table’s TTL — if (ttl_infos.table_ttl.min == 0) return false; — it cannot detect that the info was computed under a different expression.

A ClickHouse TTL demonstration on 26.7. Take the table above (90-day TTL, one part 45 days old), tighten the TTL to 30 days with materialization suppressed, and watch every escalation fail until the metadata is rebuilt:

SET materialize_ttl_after_modify = 0;   -- default is 1; shown here to expose the mechanism
ALTER TABLE sensor_readings MODIFY TTL reading_time + INTERVAL 30 DAY;

SELECT name, rows, delete_ttl_info_min, delete_ttl_info_max
FROM system.parts WHERE table = 'sensor_readings' AND active;

┌─name─────────┬───rows─┬─delete_ttl_info_min─┬─delete_ttl_info_max─┐
│ 202607_1_1_0 │ 100000 │ 2026-10-13 08:00:00 │ 2026-10-13 08:59:59 │  ← still the 90-day values
│ 202608_2_2_0 │  50000 │ 2026-11-27 17:38:33 │ 2026-11-27 18:38:32 │
└──────────────┴────────┴─────────────────────┴─────────────────────┘

SELECT count() AS total_rows,
       countIf(reading_time < now() - INTERVAL 30 DAY) AS rows_past_ttl_still_visible
FROM sensor_readings;

┌─total_rows─┬─rows_past_ttl_still_visible─┐
│     150000 │                      100000 │
└────────────┴─────────────────────────────┘

OPTIMIZE TABLE sensor_readings FINAL;   -- forces a merge... which trusts ttl.txt

┌─total_rows─┬─rows_past_ttl_still_visible─┐
│     150000 │                      100000 │  ← unchanged. The merge saw part_min_ttl in October.
└────────────┴─────────────────────────────┘

ALTER TABLE sensor_readings MATERIALIZE TTL;   -- mutation: recompute ttl.txt, apply TTL

┌─total_rows─┬─rows_past_ttl_still_visible─┐
│      50000 │                           0 │
└────────────┴─────────────────────────────┘

Even OPTIMIZE TABLE ... FINAL — the standard “force it” advice — left 100,000 expired rows in place, because the rewritten part inherited TTL timestamps computed under the old expression. Only MATERIALIZE TTL fixed it, by recomputing the info via mutation. In default configurations materialize_ttl_after_modify = 1 schedules that mutation automatically after MODIFY TTL, but the mutation is asynchronous, competes with the same background pool, and on large tables can run for hours — check system.mutations before concluding the TTL change “did nothing”.

Why this ClickHouse TTL didn’t fire: every stage trusts ttl.txt until MATERIALIZE TTL rebuilds it.

And note the sharp edge on the other side: that automatic materialization is a whole-table rewrite triggered by a metadata-looking command, which is exactly how a casual MODIFY TTL on a 50 TB table becomes an unplanned I/O event. The related setting materialize_ttl_recalculate_only (default 0) restricts materialization to recomputing ttl.txt without deleting data — useful when you want correct scheduling metadata now and are content to let normal merges do the removal.

Why your ClickHouse TTL “didn’t fire”: a ranked checklist

Ordered by how often each turns out to be the actual cause of a stalled ClickHouse TTL in support engagements. Every item names the evidence that confirms or eliminates it — work down the list, don’t guess.

1. It fired; you’re inside the normal lag window. Expired rows remain query-visible until a merge removes them, and row-delete merges are rate-limited to one per partition per merge_with_ttl_timeout (4 h default). Evidence: delete_ttl_info_min passed less than a few hours ago. The ClickHouse TTL machinery is working as designed here; if the product requirement is “expired rows must never be visible”, that is a query-layer contract (WHERE reading_time > now() - INTERVAL 30 DAY, or a row policy), not something the storage layer promises.

2. ClickHouse TTL merge slots are saturated or outcompeted. Two quota settings and one shared pool decide whether a TTL merge can even be assigned. Evidence, in order:

-- anything TTL-related running right now?
SELECT database, table, merge_type, elapsed, progress
FROM system.merges
WHERE merge_type IN ('TTLDelete', 'TTLRecompress');

-- has ANY TTL merge completed recently? (merge_reason comes from system.part_log)
SELECT table, merge_reason, count() AS merges, sum(rows) AS rows_written
FROM system.part_log
WHERE event_type = 'MergeParts'
  AND event_time > now() - INTERVAL 1 DAY
GROUP BY table, merge_reason
ORDER BY table, merges DESC;

If part_log shows a healthy stream of RegularMerge and zero TTLDeleteMerge on the affected table, you are looking at quota starvation (max_number_of_merges_with_ttl_in_pool, default 2, or on replicated tables max_replicated_merges_with_ttl_in_queue, default 1) or a background pool consumed by ingest-driven merges — check system.metrics for BackgroundMergesAndMutationsPoolTask against pool size before touching any TTL setting.

3. ttl_only_drop_parts = 1 and no part ever fully expires. The most common self-inflicted ClickHouse TTL stall. Row-delete selection is disabled, and parts whose time span exceeds the remaining TTL window cannot be dropped. Evidence: the widest_part_span_s query in the previous section. Fix by aligning partitioning to the TTL boundary, or accept row-rewrite merges by reverting the setting.

4. Stale delete_ttl_info after a ClickHouse TTL change. The trap demonstrated above. Evidence: delete_ttl_info_min/max values inconsistent with the current TTL expression, or a stuck entry in system.mutations with MATERIALIZE TTL in the command. Fix: let the materialization mutation finish, or run ALTER TABLE ... MATERIALIZE TTL explicitly. On large production tables, schedule that window deliberately — it is a rewrite. Test the impact on a staging copy first and confirm your backup posture before running it; retention bugs and their fixes both destroy data by design.

5. Parts parked on a merge-averse volume. The row-delete selector explicitly skips parts on volumes where merges are avoided (part.is_in_volume_where_merges_avoid in the selector’s canConsiderPart) — typical with tiered storage using prefer_not_to_merge on the cold volume, a combination with a long history of confusing interactions (see issue #85636 for a recent one involving ttl_only_drop_parts). Evidence: expired parts all sitting on the cold disk in system.parts.disk_name.

6. The ClickHouse TTL expression itself doesn’t cover the rows you think it does. A TTL evaluated over a nullable column ignores NULL rows; a TTL keyed to an event-time column holds late-arriving data hostage to the event time, not the insert time. Evidence: compare countIf(<ttl expression> < now()) per part against delete_ttl_info_min — if they disagree in shape, the expression is the problem, not the scheduler.

What to change in your designs because of this

ClickHouse TTL retention is reliable once the scheduler’s constraints are treated as design inputs rather than surprises. Partition so parts can fully expire, then prefer ttl_only_drop_parts = 1 for ClickHouse TTL cleanup — metadata drops instead of rewrites is the single largest TTL-related win available, and it removes an entire class of merge load. Treat merge_with_ttl_timeout as a cost dial, not a freshness dial: lowering it below an hour buys marginally fresher deletion at the price of repeated full-part rewrites, and the MergeTree settings reference carries the same warning. Monitor the outcome, not the mechanism — one alert on max(now() - delete_ttl_info_max) across active parts catches every failure mode in the checklist above, whatever its cause.

And put MODIFY TTL on existing large tables through change control with a staging rehearsal, because its cost profile is a table rewrite wearing a DDL costume.

Version notes: the ClickHouse TTL mechanisms and defaults here were verified against ClickHouse master (26.x line) and exercised on 26.7; merge_with_ttl_timeout and ttl_only_drop_parts defaults (14400 s, 0) have been stable for years. The strict three-selector cascade with the part-drop path exempt from the timeout gate reflects the current merge-selector architecture (the Compaction/ refactor); on older releases still in the field, part drops and row deletes shared one selector and both sat behind the timeout, so fully expired parts could also wait up to 4 h — behavior you may still observe on pre-25.x clusters. As always: validate against the exact version you run before wiring any of this into automation.

If you are building retention pipelines, tiered storage, or large-scale MergeTree schemas and want this level of internals depth on tap, this is the material we teach in the free courses at ChistaDATA University, alongside the broader multi-engine curriculum at MinervaDB University. And when a production cluster is holding terabytes past their TTL right now, our ClickHouse engineering team does this diagnosis daily — ChistaDATA consulting and 24×7 support.