Opensource internals

ClickHouse Deletes Expired TTL Rows Only When Parts Merge

17 min read Jay Hubbert

ClickHouse stores a per-part TTL expiry bound instead of a row set, then removes expired rows during a merge using a bit mask the merging algorithm applies while it copies rows.

TL;DR: A TTL is not a delete job. At insert time ClickHouse evaluates the TTL expression and remembers only its minimum and maximum per part, in the part’s ttl.txt. The rows actually disappear when the part is merged, and even then the merge does not rewrite them: it computes a UInt8 mask and the merging algorithm simply never copies the expired rows into the output part.

Every time-to-live feature is a promise about the future that a storage engine has to keep. The obvious implementation of “delete rows older than 30 days” is a scheduled job that finds the offending rows and removes them. In a row store that is a DELETE and a vacuum. In a columnar store it is something much worse: a part is an immutable, compressed, column-wise file set, and removing 1 % of its rows means decompressing every column, filtering it, and recompressing the result. Do that every hour and the write amplification dwarfs the workload the user actually asked for.

ClickHouse takes the opposite route. It refuses to do work on a schedule at all. A TTL is a property of the part, not of the row: the engine evaluates the TTL expression once, when the block is written, and distils the result down to two timestamps — the earliest expiry in the part and the latest. Those two numbers live in the part’s metadata. The rows themselves are not touched again until a merge rewrites the part, and then the deletion is done with a trick that costs almost nothing: a bit mask that the merging algorithm consults while it copies rows.

This article walks the whole path: how a TTL is stored, how the background merger decides which parts to rewrite because of a TTL, how the mask is built, how the merge consumes it, and why the effect of a TTL is invisible until a merge happens.

What a part remembers about its TTL

The TTL expression is evaluated at write time by TTLCalcTransform, which attaches one TTLUpdateInfoAlgorithm per TTL rule to the insert pipeline. The algorithm does not look at individual rows after the fact; it accumulates a minimum and a maximum:

cpp 
// src/Processors/TTL/TTLUpdateInfoAlgorithm.cpp:20
void TTLUpdateInfoAlgorithm::execute(Block & block)
{
    if (block.empty())
        return;

    auto ttl_column = executeExpressionAndGetColumn(ttl_expressions.expression, block, description.result_column);

    const size_t rows = block.rows();
    PaddedPODArray<Int64> timestamps;
    extractTimestamps(ttl_column.get(), timestamps);

    for (size_t i = 0; i < rows; ++i)
        new_ttl_info.update(timestamps[i]);
}

and update is the entire “TTL semantics” of a part:

cpp 
// src/Storages/MergeTree/MergeTreeDataPartTTLInfo.cpp:17
void MergeTreeDataPartTTLInfo::update(time_t time)
{
    if (time && (!min || time < min))
        min = time;

    max = std::max(time, max);
}

So a part with a TTL ts + INTERVAL 30 DAY rule and rows spread over three months remembers min = the oldest row’s expiry and max = the newest row’s expiry. That pair is enough to answer the only two questions the merge scheduler ever asks:

  • max <= now → every row in this part has expired; the part can be dropped whole.
  • min <= now → some row in this part has expired; the part has to be rewritten.

Everything else — the per-row expiry instants, the WHERE clause of the rule — is recomputed at merge time, because the merge has to read the rows anyway.

The two numbers are written into the part as ttl.txt, a small JSON document with a format version in front of it:

cpp 
// src/Storages/MergeTree/MergeTreeDataPartTTLInfo.cpp:148
void MergeTreeDataPartTTLInfos::write(WriteBuffer & out) const
{
    writeString("ttl format version: 1\n", out);
    writeString("{", out);
    if (!columns_ttl.empty())
    {
        writeString("\"columns\":[", out);
        for (auto it = columns_ttl.begin(); it != columns_ttl.end(); ++it)
        {
            if (it != columns_ttl.begin())
                writeString(",", out);

            writeString("{\"name\":", out);
            writeString(doubleQuoteString(it->first), out);
            writeString(",\"min\":", out);
            writeIntText(it->second.min, out);
            writeString(",\"max\":", out);
            writeIntText(it->second.max, out);
            writeString(R"(,"finished":)", out);
            writeIntText(static_cast<uint8_t>(it->second.finished()), out);
            writeString("}", out);
        }
        writeString("]", out);
    }

The file is read back lazily, only when the table actually has a TTL:

cpp 
// src/Storages/MergeTree/IMergeTreeDataPart.cpp:2847
void IMergeTreeDataPart::loadTTLInfos()
{
    auto metadata_snapshot = getMetadataSnapshot();
    if (metadata_snapshot->hasAnyTTL())
    {
        if (auto in = readFileIfExists("ttl.txt"))
        {
            assertString("ttl format version: ", *in);
            size_t format_version = 0;
            readText(format_version, *in);
            assertChar('\n', *in);

            if (format_version == 1)
            {
                try
                {
                    ttl_infos.read(*in);
                }
                catch (const JSONException &)
                {
                    throw Exception(ErrorCodes::BAD_TTL_FILE, "Error while parsing file ttl.txt in part: {}", name);
                }
            }
            else
                throw Exception(ErrorCodes::BAD_TTL_FILE, "Unknown ttl format version: {}", toString(format_version));
        }
    }
}

Note the shape of the metadata: for a table TTL there is a single {"min":…,"max":…} pair; for column TTLs there is one pair per column; for WHERE-conditioned TTLs the expression string is the key. The expression is stored as a string, and the lookup has to be forgiving about how it is spelled — expired + toIntervalSecond(20) and plus(expired, toIntervalSecond(20)) are the same rule but different strings, so the lookup falls back to parsing both and comparing tree hashes.

Which parts get rewritten because of a TTL

The merge scheduler has four different TTL selectors, and they are tried in a fixed priority order. The order is the whole design: the cheapest way to honour a TTL wins.

cpp 
// src/Storages/MergeTree/Compaction/MergeSelectorApplier.cpp:78
MergeSelectorChoices tryChooseTTLMerge(const ChooseContext & ctx)
{
    /// Drop parts - 1 priority
    if (!ctx.merge_constraints.empty())
    {
        /// The size of the completely expired part of TTL drop is not affected by the merge pressure and the size of the storage space.
        std::vector<MergeConstraint> ttl_constraints(ctx.merge_constraints.size(), {std::numeric_limits<size_t>::max(), std::numeric_limits<size_t>::max()});
        TTLPartDropMergeSelector drop_ttl_selector(ctx.current_time, ctx.merge_tree_settings[MergeTreeSetting::max_parts_to_merge_at_once]);

        if (auto merge_ranges = drop_ttl_selector.select(ctx.ranges, ttl_constraints, ctx.range_filter); !merge_ranges.empty())
            return pack(ctx, std::move(merge_ranges), MergeType::TTLDrop);
    }

    /// Delete rows - 2 priority
    if (!ctx.merge_constraints.empty() && !ctx.merge_tree_settings[MergeTreeSetting::ttl_only_drop_parts])
    {
        TTLRowDeleteMergeSelector delete_ttl_selector(ctx.next_delete_times, ctx.current_time);

        if (auto merge_ranges = delete_ttl_selector.select(ctx.ranges, ctx.merge_constraints, ctx.range_filter); !merge_ranges.empty())
            return pack(ctx, std::move(merge_ranges), MergeType::TTLDelete);
    }

The four priorities are: drop the whole part (nothing to rewrite — the part is unlinked), delete rows (rewrite the part, drop the expired rows), delete columns (a column TTL can only be honoured by rewriting the part; there is no cheaper alternative), and recompress (see the companion article on TTL … RECOMPRESS CODEC(…)).

Priority 1 is the cheapest by far, and it is why the TTL merge selector is built the way it is. TTLPartDropMergeSelector looks only at the maximum expiry of the part, and it does not care about the size limits at all:

cpp 
// src/Storages/MergeTree/Compaction/MergeSelectors/TTLMergeSelector.cpp:6
static bool canIncludeToRange(size_t part_size, size_t part_rows, time_t part_ttl, time_t current_time, size_t usable_memory, size_t usable_rows)
{
    return (0 < part_ttl && part_ttl <= current_time) && usable_memory >= part_size && usable_rows >= part_rows;
}

Contrast that with the row-delete selector, which looks at the minimum expiry — the part qualifies as soon as a single row has expired, so the merge has to actually rewrite it:

cpp 
// src/Storages/MergeTree/Compaction/MergeSelectors/TTLMergeSelector.cpp:244
time_t TTLRowDeleteMergeSelector::getTTLForPart(const PartProperties & part) const
{
    return part.general_ttl_info->part_min_ttl;
}

bool TTLRowDeleteMergeSelector::canConsiderPart(const PartProperties & part) const
{
    if (part.is_in_volume_where_merges_avoid)
        return false;

    if (!part.general_ttl_info.has_value())
        return false;

    return part.general_ttl_info->has_any_non_finished_row_ttls;
}

The has_any_non_finished_* flag matters: once every row of a part has expired and the TTL has been applied, the part’s TTL info is marked finished and the selector must not pick it again. A part that was fully expired and dropped does not come back; a part that was partially rewritten records the new minimum, and the selector moves on to the next one.

The range building itself is a small greedy algorithm. For each candidate centre part (a part whose TTL has expired), the selector walks left and right while the accumulated size and row count fit the merge constraint, and it refuses to include a neighbour that has not itself expired — that is the meaning of the part_ttl <= current_time term in canIncludeToRange. A TTL merge therefore only ever merges parts whose data is actually due for deletion, and it drags along the minimum number of extra parts needed to make a legal merge.

The four-hour throttle

A TTL merge is not free — it rewrites the part — and a table whose TTL expires continuously would otherwise be rewritten continuously. ClickHouse throttles this per partition: after a TTL delete merge in a partition, the next one in that partition is not scheduled for four hours.

cpp 
// src/Storages/MergeTree/MergeTreeDataMergerMutator.cpp:194
void MergeTreeDataMergerMutator::updateTTLMergeTimes(const MergeSelectorChoices & choices, const MergeTreeSettingsPtr & settings, time_t current_time)
{
    for (const auto & choice : choices)
    {
        chassert(!choice.range.empty());
        const String & partition_id = choice.range.front().info.getPartitionId();

        switch (choice.merge_type)
        {
            case MergeType::Regular:
            case MergeType::TTLDrop:
            {
                /// Do not update anything for regular and drop merges.
                break;
            }
            case MergeType::TTLDelete:
            {
                const time_t next_time = current_time + (*settings)[MergeTreeSetting::merge_with_ttl_timeout];

and the setting itself:

cpp 
// src/Storages/MergeTree/MergeTreeSettings.cpp:2199
    DECLARE(Int64, merge_with_ttl_timeout, 3600 * 4, R"(
Minimum delay in seconds before repeating a merge with delete TTL.
)", 0) \
    DECLARE(Int64, merge_with_recompression_ttl_timeout, 3600 * 4, R"(
Minimum delay in seconds before repeating a merge with recompression TTL.
)", 0) \
    DECLARE(Bool, ttl_only_drop_parts, false, R"(
Controls whether data parts are fully dropped in MergeTree tables when all
rows in that part have expired according to their `TTL` settings.

ttl_only_drop_parts is the other side of the same coin: by default ClickHouse rewrites a part to remove the expired rows from it, but with this setting on it only drops parts whose rows have all expired and leaves partially expired parts alone. It is the knob for the “I want the disk space back, I do not care about query-level precision” user — and, not coincidentally, the knob that makes TTL processing much cheaper, because no part is ever rewritten for a row TTL.

The mask

Now the interesting part. When the scheduler decides that a part must be rewritten because some of its rows have expired, the merge has to find out which rows. The naive implementation reads every column, evaluates the TTL expression, and builds a filter — which is what the horizontal merge path does:

cpp 
// src/Processors/TTL/TTLDeleteAlgorithm.cpp:32
    IColumn::Filter filter(rows);
    size_t removed = 0;

    for (size_t i = 0; i < rows; ++i)
    {
        Int64 cur_ttl = timestamps[i];
        bool where_filter_passed = !where_column || where_column->getBool(i);
        bool remove = isTTLExpired(cur_ttl) && where_filter_passed;

        filter[i] = !remove;

        if (remove)
        {
            ++removed;
        }
        else if (where_filter_passed)
        {
            /// Update ttl info only if row passes the filter.
            /// Rows that don't pass the filter should not affect TTL.
            new_ttl_info.update(cur_ttl);
        }
    }

    rows_removed += removed;

    if (removed == 0)
        return;

    for (auto & column : block)
        column.column = column.column->filter(filter, rows - removed);

That is a real physical deletion: every column is filtered and the block shrinks. It is the correct thing to do, and it is what a horizontal merge does.

But there is a much cheaper way, and it is the one ClickHouse actually uses whenever it can. The vertical merge already reads the rows, sorts them, and copies them into the output part column by column. If the merge knows in advance which rows are expired, it does not have to filter anything at all — it just does not copy them. The information is carried as a side-channel: a bit mask attached to the chunk.

cpp 
// src/Processors/Transforms/TTLDeleteFilterTransform.h:13
/// Evaluates TTL delete expressions and attaches the resulting mask to the chunk, without
/// filtering the block or changing its header. The merging algorithm reads the mask to set skip
/// flags in row sources for a vertical merge.
class TTLDeleteFilterTransform final : public ISimpleTransform

The mask itself is a RowFilterInfo, a chunk-info object that rides next to the data:

cpp 
// src/Processors/Merges/Algorithms/RowFilterInfo.h:10
/// Rows a merge must not emit, computed upstream and carried beside the data
struct RowFilterInfo : public ChunkInfoCloneable<RowFilterInfo>
{
    explicit RowFilterInfo(IColumnFilter mask_) : mask(std::move(mask_)) {}

    /// `IColumnFilter` is move-only, so `clone`'s deep copy has to be written by hand.
    RowFilterInfo(const RowFilterInfo & other) { mask.assign(other.mask); }

    /// Not supported: nothing combines two chunks that each carry a mask.
    Ptr merge(const Ptr &) const override;

    IColumnFilter mask;
};

The transform that builds it is deliberately minimal: it evaluates the TTL expression once per block, converts the result into a flat Int64 timestamp array (one type-dispatch, then a tight loop), and folds the expiry test and the WHERE test into a single UInt8 per row:

cpp 
// src/Processors/Transforms/TTLDeleteFilterTransform.cpp:117
    IColumnFilter filter_vec(num_rows, 1);

    auto chunk_infos = std::move(chunk.getChunkInfos());

    auto block = getInputPort().getHeader().cloneWithColumns(chunk.detachColumns());

    for (const auto & entry : shared_state->entries)
    {
        /// Phase 1: extract typed TTL column into a flat Int64 timestamp array.
        auto ttl_column = ITTLAlgorithm::executeExpressionAndGetColumn(
            entry.expressions.expression, block, entry.description.result_column);
        extractTimestamps(ttl_column.get());

        /// Phase 2: apply TTL expiration and WHERE filter to produce the filter mask.
        auto where_column = ITTLAlgorithm::executeExpressionAndGetColumn(
            entry.expressions.where_expression, block, entry.description.where_result_column);

        for (size_t i = 0; i < num_rows; ++i)
        {
            if (!filter_vec[i])
                continue;

            bool where_filter_passed = !where_column || where_column->getBool(i);
            if (isTTLExpired(timestamps[i], shared_state->current_time) && where_filter_passed)
            {
                filter_vec[i] = 0;
            }
        }
    }

    chunk = Chunk(block.getColumns(), num_rows);
    chunk.setChunkInfos(std::move(chunk_infos));
    attachRowFilter(chunk, std::move(filter_vec));

Two details are worth pausing on. First, the mask is not a filter of the block: the block still has all its rows and all its columns, and the header is unchanged. Second, the mask is not applied to the rows that a WHERE-conditioned TTL skips: filter_vec[i] starts at 1 and is only cleared when the row both expired and passed the WHERE test, so the semantics match the horizontal path exactly.

Before building the mask, the transform checks whether the whole part is expired. If the maximum expiry of the part is in the past and the TTL has no WHERE clause, every single row is expired and the whole part is garbage:

cpp 
// src/Processors/Transforms/TTLDeleteFilterTransform.cpp:56
        if (force || isTTLExpired(old_ttl_infos.table_ttl.min, current_time))
        {
            auto expressions = buildTTLExpressions(rows_ttl, subqueries, context);

            if (isTTLExpired(old_ttl_infos.table_ttl.max, current_time) && !rows_ttl.where_expression_ast)
                state->all_data_dropped = true;

            state->entries.push_back({std::move(expressions), rows_ttl});
        }

all_data_dropped short-circuits the transform to a mask of all zeros — “emit nothing” — and the merge emits no rows at all for that part.

The merge consumes the mask

The mask has to reach the merging algorithm. The algorithm resolves it once per chunk, from either of two sources: a stored _row_exists column (the lightweight delete) or the chunk’s RowFilterInfo (a TTL delete):

cpp 
// src/Processors/Merges/Algorithms/MergingSortedAlgorithm.cpp:162
const IColumnFilter * MergingSortedAlgorithm::resolveRowFilterMask(const Chunk & chunk) const
{
    if (filter_column_position != -1)
    {
        /// One merge filters on one predicate: `canVerticalTTLDelete` gives up when a source part
        /// has a lightweight delete, so a merge never gets a mask from both directions.
        chassert(!chunk.getChunkInfos().has<RowFilterInfo>());
        return &assert_cast<const ColumnUInt8 &>(*chunk.getColumns()[filter_column_position]).getData();
    }

    return getRowFilterMask(chunk);
}

The comment says it all: the two mechanisms are mutually exclusive, because a merge filters on exactly one predicate.

From there the mask is consulted once per row, and the row is simply not copied:

cpp 
// src/Processors/Merges/Algorithms/MergingSortedAlgorithm.cpp:444
void MergingSortedAlgorithm::insertRow(const SortCursorImpl & current)
{
    auto write_row_source = [&](bool skipped)
    {
        if (out_row_sources_buf)
        {
            RowSourcePart row_source(current.order, skipped);
            out_row_sources_buf->write(row_source.data);
        }
    };

    size_t current_row = current.getRow();

    if (const auto * mask = source_row_filter_masks[current.order])
    {
        if ((*mask)[current_row])
            merged_data.insertRow(current.all_columns, current_row, current.rows);

        write_row_source(!(*mask)[current_row]);
    }
    else
    {
        merged_data.insertRow(current.all_columns, current_row, current.rows);
        write_row_source(false);
    }
}

That is the whole trick. The expired row is never written to the output column, so the output part is physically smaller — but nothing was filtered and no column was re-serialised because of a row that was dropped. The mask is a UInt8 per row, computed once, and it is read once.

The batch path does the same, and additionally records the skip in the row-source file, which is what lets the vertical phase of a merge know that a row of a gathering column has no counterpart in the merged column set:

cpp 
// src/Processors/Merges/Algorithms/MergingSortedAlgorithm.cpp:471
void MergingSortedAlgorithm::insertRows(const SortCursorImpl & current, size_t num_rows)
{
    if (const auto * mask = source_row_filter_masks[current.order])
    {
        size_t start_index = current.getRow();
        RowSourcePart row_source(current.order, false);
        RowSourcePart row_source_skipped(current.order, true);

        for (size_t i = start_index; i < start_index + num_rows; ++i)
        {
            if ((*mask)[i])
            {
                merged_data.insertRow(current.all_columns, i, current.rows);
                out_row_sources_buf->write(row_source.data);
            }
            else
            {
                out_row_sources_buf->write(row_source_skipped.data);
            }
        }
    }

The RowSourcePart with the skipped flag set is exactly the same mechanism the lightweight delete uses, so a TTL delete and a lightweight delete end up producing the same kind of part.

When the mask is not enough

The mask trick only works when the merge is allowed to not copy a row, and that has preconditions. canVerticalTTLDelete is the gate:

cpp 
// src/Storages/MergeTree/MergeTask.cpp:1454
bool MergeTask::canVerticalTTLDelete(const GlobalRuntimeContext & global_ctx)
{
    switch (global_ctx.merging_params.mode)
    {
        case MergeTreeData::MergingParams::Ordinary:
        case MergeTreeData::MergingParams::Replacing:
        case MergeTreeData::MergingParams::Collapsing:
        case MergeTreeData::MergingParams::VersionedCollapsing:
            break;
        case MergeTreeData::MergingParams::Summing:
        case MergeTreeData::MergingParams::Aggregating:
        case MergeTreeData::MergingParams::Coalescing:
        case MergeTreeData::MergingParams::Graphite:
            return false;
    }

    if (!(*global_ctx.data_settings)[MergeTreeSetting::vertical_merge_optimize_ttl_delete])
        return false;

    if (global_ctx.metadata_snapshot->hasAnyGroupByTTL())
        return false;

    if (global_ctx.metadata_snapshot->hasAnyColumnTTL())
        return false;

    if (hasLightweightDelete(global_ctx.future_part))
        return false;

    return global_ctx.metadata_snapshot->hasRowsTTL() || global_ctx.metadata_snapshot->hasAnyRowsWhereTTL();
}

The list of exclusions is the list of reasons a row cannot simply be skipped:

  • Summing / Aggregating / Coalescing / Graphite — these engines combine rows. A skipped row would change the aggregate, so the merge has to see it and decide. (For a GROUP BY TTL the same is true: the TTL is applied to an aggregate of the rows, so the rows must be present when the aggregate is formed.)
  • Column TTL — a column TTL resets a column’s value to its default. The rows stay, the column changes, so the part must be rewritten with different data, not with fewer rows.
  • A lightweight delete in the same merge — one merge filters on one predicate.

When the gate is closed, the merge falls back to the horizontal path, where TTLTransform physically filters the block and the rows are really gone.

There is one more case worth mentioning: a part whose rows have all expired. The merge does not need to read anything at all — it just writes an empty part and drops the old one. ClickHouse short-circuits the whole pipeline for that:

cpp 
// src/Storages/MergeTree/MergeTask.cpp:1261
        /// Reset all ttl_infos and mark table TTL as finished, matching what
        /// TTLTransform::finalize produces in the normal all_data_dropped path:
        /// it clears everything with `data_part->ttl_infos = {}`, then
        /// TTLDeleteAlgorithm::finalize sets table_ttl = {0, 0, finished}.
        global_ctx->new_data_part->ttl_infos = {};
        global_ctx->new_data_part->ttl_infos.table_ttl = {0, 0, true};

The gotcha: TTL is invisible until a merge

The consequence of all this is the thing that surprises everybody the first time: a TTL does not delete anything on its own. If you insert a row that is already expired and then query the table, the row is still there. It will still be there after an hour, after a day. The row disappears when a merge rewrites the part that contains it — and a merge happens when the merge scheduler decides it should, which may be minutes or may be days.

The same is true in the other direction: if a part is never merged (because it is the only part in its partition and is too small to be worth merging), the expired rows stay in it forever. TTL is a merge-triggered deletion, not a timer.

If you need the effect now, the tool is MATERIALIZE TTL, which is a mutation:

sql 
-- Force the TTL to be applied to every part, right now.
ALTER TABLE events MATERIALIZE TTL;

-- Recompute only the stored TTL metadata, without removing any data.
-- (merge_tree setting `materialize_ttl_recalculate_only = 1`)
ALTER TABLE events MATERIALIZE TTL;

The mutation rewrites every part with the TTL rules applied — the same TTLTransform machinery, but driven by the mutation engine instead of the merge scheduler. And it is expensive, which is why it is not what the background merger does: the background merger prefers to piggyback the deletion on a merge that was going to happen anyway.

There is a second, subtler gotcha in the same area: the TTL bound of a part is only valid if the TTL expression was evaluated for it. A part written by an older version, or attached from a backup, may not have a ttl.txt at all. The merge detects this and forces the calculation:

cpp 
// src/Storages/MergeTree/MergeTask.cpp:689
        if (global_ctx->metadata_snapshot->hasAnyTTL() && !part->checkAllTTLCalculated(global_ctx->metadata_snapshot))
        {
            LOG_INFO(ctx->log, "Some TTL values were not calculated for part {}. Will calculate them forcefully during merge.", part->name);
            ctx->need_remove_expired_values = true;
            ctx->force_ttl = true;
        }

Trade-offs

What this design buys:

  • No write amplification from a timer. A TTL costs one timestamp array per inserted block, and two integers per part on disk. The expensive work is folded into merges that were going to happen anyway.
  • Deletion is proportional to the merge, not to the expiry rate. A table with a TTL that expires 1 % of its rows per day does 1 % less work than one that expires 100 %, because the second one is rewritten far more often — but the per merge cost is the same.
  • The mask is nearly free. One UInt8 per row, computed once, read once. The rows are not filtered, re-serialised, or re-compressed; they are simply not copied.

What it costs:

  • The semantics are merge-dependent. “Rows expired 30 days ago” is not a guarantee about a query result; it is a guarantee about the next merge. Two replicas that have merged different amounts of data can legitimately return different row counts for the same query.
  • Latency. A part that is never merged never expires. MATERIALIZE TTL exists precisely because the user sometimes needs the guarantee now, and it is expensive.
  • Throttling. The four-hour merge_with_ttl_timeout means that even a table whose data expires continuously will keep a few hours’ worth of expired rows around, deliberately.
  • The mask trick has a whitelist. Summing, Aggregating, Coalescing, Graphite and GROUP BY TTLs cannot use it, because their rows have to be seen to be combined or aggregated. For those, the deletion is a real rewrite.

The design is a deliberate trade: ClickHouse gives up the guarantee that an expired row is gone, and in exchange makes the common case (a TTL that is a background housekeeping rule) essentially free. The MATERIALIZE TTL mutation is the escape hatch that restores the strong guarantee when a user needs it, and the price of that escape hatch is a full rewrite of every part.

ClickhouseInternalsMergetreeTtlPerformance