Opensource internals

ClickHouse Deletes Rows by Writing a Mask, Not by Rewriting Data

18 min read Jay Hubbert

How ClickHouse's lightweight DELETE rewrites itself into an UPDATE of a hidden _row_exists column, hardlinks the untouched columns, and filters the mask at read time.

TL;DR: DELETE FROM t WHERE ... does not delete anything. It is rewritten into ALTER TABLE t UPDATE _row_exists = 0 WHERE ..., which materializes a hidden UInt8 mask column: 1 for a live row, 0 for a deleted one. Every other column’s file is hardlinked, not rewritten. Reads apply the mask as a filter, and a later merge finally drops the dead rows.

The problem: a delete in an immutable store

MergeTree is an LSM tree. A part is an immutable, on-disk, columnar file bundle. Once written, its bytes are not supposed to change. That is what makes merging cheap, what makes the primary index meaningful, and what makes SELECT fast and lock-free.

But DELETE FROM t WHERE x = 5 wants to remove rows. There is no way to remove bytes from an immutable file without rewriting it. The naive implementation — the one ALTER TABLE ... DELETE uses — rewrites the whole part, column by column, applying the predicate and dropping the matching rows.

For a table with a billion rows and a predicate that matches a thousand, that means: read a billion rows, write a billion rows, and do it in the background where it competes with merges for disk bandwidth and pool slots. A user who wants to delete ten thousand rows out of a hundred million has to wait for a gigabyte-scale rewrite.

ClickHouse’s answer is to not delete anything, and to write down the fact of deletion in a place that costs almost nothing to write and almost nothing to read.

The mask column

The mechanism is a hidden persistent column called _row_exists:

cpp 
// src/Storages/MergeTree/MergeTreeVirtualColumns.cpp:27
const String RowExistsColumn::name = "_row_exists";
const DataTypePtr RowExistsColumn::type = std::make_shared<DataTypeUInt8>();

One byte per row. 1 means the row exists, 0 means it was deleted. The column is registered as a persistent virtual column, which means it is a real column on disk when present and absent from the schema when not:

cpp 
// src/Storages/MergeTree/MergeTreeData.cpp:1086
    desc.addPersistent(RowExistsColumn::name, RowExistsColumn::type, nullptr, "Persisted mask created by lightweight delete that show whether row exists or is deleted");

Note the crucial property: the column only exists in a part if some row in that part was deleted. A part that has never been touched by a delete has no _row_exists column at all, so it pays nothing. The presence of the column is the record that a delete happened.

cpp 
// src/Storages/MergeTree/IMergeTreeDataPart.cpp:3115
bool IMergeTreeDataPart::hasLightweightDelete() const
{
    return getColumns().contains(RowExistsColumn::name);
}

A one-line predicate that answers “does this part need mask processing?”

The rewrite: DELETE becomes UPDATE

The whole trick starts at the parser/interpreter boundary. DELETE FROM never reaches the storage engine as a delete. It is rewritten into an UPDATE of the mask column:

cpp 
// src/Interpreters/InterpreterDeleteQuery.cpp:255
            /// Build "ALTER <table> [ON CLUSTER <cluster>] UPDATE _row_exists = 0 [IN PARTITION <partition_id>] WHERE <predicate>" query
            static constexpr auto alter_query_template = "ALTER TABLE {}{} UPDATE `_row_exists` = 0{} WHERE {}";

The rewrite is textual — the predicate is formatted back into a string and re-parsed:

cpp 
// src/Interpreters/InterpreterDeleteQuery.cpp:266
            String alter_query = fmt::format(
                alter_query_template,
                table->getStorageID().getFullTableName(),
                delete_query.cluster.empty() ? "" : " ON CLUSTER " + backQuoteIfNeed(delete_query.cluster),
                partition_clause,
                delete_query.predicate->formatWithSecretsOneLine());

            ParserAlterQuery parser;
            ASTPtr alter_ast = parseQuery(
                parser,
                alter_query.data(),
                alter_query.data() + alter_query.size(),
                "ALTER query",
                0,
                DBMS_DEFAULT_MAX_PARSER_DEPTH,
                DBMS_DEFAULT_MAX_PARSER_BACKTRACKS);

This is not elegance for its own sake. The privilege check has to come out right: DELETE FROM requires only the ALTER DELETE privilege, but the rewritten statement is an ALTER ... UPDATE, which would normally require ALTER UPDATE. ClickHouse handles that by classifying the assignment rather than the statement:

cpp 
// src/Storages/MergeTree/MergeTreeVirtualColumns.cpp:30
bool isLightweightDeleteAssignment(const ASTAssignment & assignment)
{
    if (assignment.column_name != RowExistsColumn::name)
        return false;
    /// `DELETE FROM` rewrites to `_row_exists = 0`; only that exact literal is a delete. Any other
    /// expression (e.g. `_row_exists = 1` to resurrect rows) is a real update of the deletion mask.
    const auto * literal = assignment.expression()->as<ASTLiteral>();
    return literal && literal->value == Field(static_cast<UInt64>(0));
}

Only the exact literal _row_exists = 0 counts as a delete. _row_exists = 1 — resurrecting a deleted row — is a real update and needs ALTER UPDATE. This is checked in the access-control layer:

cpp 
// src/Interpreters/InterpreterAlterQuery.cpp:870
            /// Setting the `_row_exists` lightweight-delete marker to 0 is a delete, not an update:
            /// `DELETE FROM` rewrites to `ALTER ... UPDATE _row_exists = 0`. Govern that exact form by
            /// ALTER DELETE so `DELETE FROM` needs only the documented ALTER DELETE privilege. Any other
            /// assignment - including `_row_exists = <expr>` that resurrects/edits the deletion mask -
            /// stays a real update requiring ALTER UPDATE. The shortcut applies only when `_row_exists`
            /// is the hidden virtual marker (not an ordinary physical column on some other engine).
            std::vector<std::string_view> updated_columns;
            bool deletes_via_row_exists = false;
            for (const ASTPtr & assignment_ast : command.update_assignments->children)
            {
                const auto & assignment = assignment_ast->as<const ASTAssignment &>();
                const bool is_lightweight_delete_assignment = isLightweightDeleteAssignment(assignment);
                if (!is_lightweight_delete_assignment || row_exists_column_kind != RowExistsColumnKind::LightweightDeleteMarker)
                    updated_columns.emplace_back(assignment.column_name);
                if (is_lightweight_delete_assignment && row_exists_column_kind != RowExistsColumnKind::Regular)
                    deletes_via_row_exists = true;
            }
            if (!updated_columns.empty())
                required_access.emplace_back(AccessType::ALTER_UPDATE, database, table, updated_columns);
            if (deletes_via_row_exists)
                required_access.emplace_back(AccessType::ALTER_DELETE, database, table);

The RowExistsColumnKind check matters because _row_exists is only the hidden marker on engines that register it as such. On a Memory table, a user could ADD COLUMN _row_exists and then UPDATE _row_exists = 0 — that is a real physical column and must require ALTER UPDATE. The kind is resolved once, best-effort, and fails closed:

cpp 
// src/Interpreters/InterpreterAlterQuery.cpp:766
    /// `_row_exists` is the hidden lightweight-delete marker only on storages that register it as a
    /// virtual column (the MergeTree family). Testing merely for the absence of a physical `_row_exists`
    /// column is too broad: on e.g. a `Memory` table `_row_exists` is not the marker, yet has no
    /// physical column either, so a user could `ADD COLUMN _row_exists, UPDATE _row_exists = 0` and edit
    /// a real physical column with only `ALTER DELETE`. `isVirtualColumn` is true only when `_row_exists`
    /// is a registered virtual and not shadowed by a real column, which precisely identifies the marker.
    if (!storage)
        return RowExistsColumnKind::Unknown;
    const auto metadata_snapshot = storage->getInMemoryMetadataPtr(context_, false);
    return metadata_snapshot->isVirtualColumn(RowExistsColumn::name)
        ? RowExistsColumnKind::LightweightDeleteMarker
        : RowExistsColumnKind::Regular;

What the mutation actually does

The mutation is an ordinary UPDATE command, and the mutations interpreter treats _row_exists as a data column with a special type. The mask column is not a virtual column to the interpreter — it is a real column of type UInt8:

cpp 
// src/Interpreters/MutationsInterpreter.cpp:1179
                else if (column_name == RowExistsColumn::name)
                {
                    type = RowExistsColumn::type;
                    deleted_mask_updated = true;
                }

deleted_mask_updated is a flag that ripples through the rest of the mutation machinery, and its meaning is: “the mask is being changed, so do not also apply the existing mask.” That distinction matters because there are two different things going on:

  1. A pending DELETE that has not been materialized yet — the predicate is applied on the fly at read time.
  2. A mask column that is already on disk — the stored bits are applied at read time.

When a heavyweight ALTER DELETE (or APPLY DELETED MASK) rewrites a part that already has a mask, the existing mask must not be applied and the new deletion must not be applied to rows that are already dead — otherwise the row count changes twice. The interpreter’s stage machinery handles this by tracking which columns are outputs of which stage:

cpp 
// src/Interpreters/MutationsInterpreter.cpp:1893
        if (settings.return_all_columns || prepared_stages[i].affects_all_columns)
        {
            for (const auto & column : all_columns)
            {
                if (column.name == RowExistsColumn::name && !deleted_mask_updated)
                    continue;

                prepared_stages[i].output_columns.insert(column.name);
            }

            has_filters = true;
            settings.apply_deleted_mask = true;
        }

The if (column.name == RowExistsColumn::name && !deleted_mask_updated) continue; is the load-bearing line. When the mask is not being updated, the mask column is excluded from the stage’s outputs — because the filter that removes the dead rows has already consumed it, and writing it out again would double-count.

The reason lightweight delete is fast is not the mask; it is what happens to the other columns.

A heavyweight ALTER DELETE must rewrite every column of the affected part, because the row count changes and the columns are stored independently (in Wide parts). A lightweight delete does not change the row count — it only adds a one-byte-per-row column. So the other columns’ files can be hardlinked: the new part’s payload.bin is the same inode as the old part’s.

The documentation in the parser says it plainly:

cpp 
// src/Parsers/ParserDeleteQuery.cpp:197
   2. Based on the commands above, affected parts are then mutated, and hardlinks are created for unaffected parts. In the case of wide parts, the `_row_exists` column for each row is updated, and all other columns' files are hardlinked. For compact parts, all columns are re-written because they are all stored together in one file.

So the cost of deleting 10 thousand rows out of 100 million is:

  • Wide parts: write one new file of 100 million bytes (the mask), hardlink everything else. Cost ≈ 100 MB, independent of how many rows matched.
  • Compact parts: everything is in one file, so it must all be rewritten. Compact parts are small by definition, so this is bounded.

Compare with heavyweight ALTER DELETE, which rewrites every column of every affected part: cost ≈ (size of the whole table) × (fraction of parts affected).

That is the entire pitch of lightweight delete, and it is why the feature exists.

Reading: the mask becomes a filter

On the read side, the mask column is turned into a filter step. The implementation is a PrewhereExprStep with no expression DAG at all — just a column name:

cpp 
// src/Storages/MergeTree/MergeTreeBlockReadUtils.cpp:391
PrewhereExprStepPtr createLightweightDeleteStep(bool remove_filter_column)
{
    PrewhereExprStep step
    {
        .type = PrewhereExprStep::Filter,
        .actions = nullptr,
        .filter_column_name = RowExistsColumn::name,
        .remove_filter_column = remove_filter_column,
        .need_filter = true,
        .perform_alter_conversions = true,
        .columns_overwritten_by_chain = {},
        .mutation_version = std::nullopt,
    };

    return std::make_shared<PrewhereExprStep>(std::move(step));
}

filter_column_name = "_row_exists" with actions = nullptr means: “use the column literally named _row_exists as the filter.” Since it is UInt8, the filter keeps rows where the value is non-zero — i.e. where the row exists.

The sequential read path (used by OPTIMIZE, mutations, and single-part reads) sets this up explicitly:

cpp 
// src/Storages/MergeTree/MergeTreeSequentialSource.cpp:371
    /// The part might have some rows masked by lightweight deletes
    bool has_lightweight_delete = info->data_part_info->hasLightweightDelete() || info->alter_conversions->hasLightweightDelete();
    const bool need_to_filter_deleted_rows = apply_deleted_mask && has_lightweight_delete && !info->data_part_info->getPartInfo().isPatch();
    const bool has_filter_column = std::ranges::find(columns_to_read, RowExistsColumn::name) != columns_to_read.end();

    if (need_to_filter_deleted_rows)
    {
        if (!has_filter_column)
            columns_to_read.push_back(RowExistsColumn::name);

        info->mutation_steps.push_back(createLightweightDeleteStep(!has_filter_column));
    }

Three things to notice:

  1. The mask column is added to the read set if the query did not ask for it. The reader has to read the mask to apply it.
  2. !info->data_part_info->getPartInfo().isPatch() — patch parts are excluded. A patch part is a mask; applying the mask to a mask would be wrong.
  3. has_filter_column decides whether the mask column is removed from the output. If the user asked for _row_exists explicitly (a debugging scenario), it is kept; otherwise it is dropped so it does not leak into the result.

The parallel read path does the same thing:

cpp 
// src/Storages/MergeTree/MergeTreeReadPoolBase.cpp:464
    bool has_lightweight_delete = data_part->hasLightweightDelete() || read_task_info.alter_conversions->hasLightweightDelete();

    if (reader_settings.apply_deleted_mask && has_lightweight_delete)
    {
        bool remove_filter_column = std::ranges::find(column_names, RowExistsColumn::name) == column_names.end();
        read_task_info.mutation_steps.push_back(createLightweightDeleteStep(remove_filter_column));
    }

The escape hatch: apply_deleted_mask

The mask application is controlled by a session setting:

cpp 
// src/Core/Settings.cpp:5946
    DECLARE(Bool, apply_deleted_mask, true, R"(
Enables filtering out rows deleted with lightweight DELETE. If disabled, a query will be able to read those rows. This is useful for debugging and \"undelete\" scenarios
)", 0) \

apply_deleted_mask = 0 makes deleted rows visible again. That is a genuine “undelete” — the rows were never removed from disk, only masked, so turning the mask off brings them back.

But the setting is not absolute. If the query itself filters on _row_exists, the deleted rows are hidden regardless:

cpp 
// src/Storages/MergeTree/MergeTreeDataSelectExecutor.cpp:1162
    /// Whether the read drops lightweight-deleted rows: by the deleted mask or by an explicit filter on `_row_exists`.
    bool query_hides_lightweight_deleted_rows = settings[Setting::apply_deleted_mask];
    if (!query_hides_lightweight_deleted_rows)
    {
        auto dag_reads_row_exists = [](const ActionsDAG & dag)
        {
            for (const auto * input : dag.getInputs())
                if (input->result_name == RowExistsColumn::name)
                    return true;
            return false;
        };

        query_hides_lightweight_deleted_rows = (query_info.filter_actions_dag && dag_reads_row_exists(*query_info.filter_actions_dag))
            || (query_info.prewhere_info && dag_reads_row_exists(query_info.prewhere_info->prewhere_actions))
            || (query_info.row_level_filter && dag_reads_row_exists(query_info.row_level_filter->actions));
    }

That flag then feeds the decision about whether a vector similarity index can be trusted for the part — a subtle but important consequence:

cpp 
// src/Storages/MergeTree/MergeTreeDataSelectExecutor.cpp:1268
                    /// A vector similarity index returns exactly as many candidates as the `LIMIT` asks for. A delete
                    /// that the index does not know about (a lightweight delete, materialized or in a patch part, or a
                    /// pending `ALTER DELETE` applied on the fly) does not shrink that shortlist: the deleted candidates
                    /// are dropped after the read with nothing to take their place, and the query returns fewer rows
                    /// than its `LIMIT`. Read such a part in full until a merge or a mutation rebuilds the index.
                    /// With `apply_deleted_mask = 0` lightweight-deleted rows are returned and the read sees exactly
                    /// the rows the index was built on, unless the query filters by `_row_exists` explicitly, which
                    /// drops the deleted candidates in the same way. A pending `ALTER DELETE` is applied regardless.
                    const bool has_deleted_rows = (query_hides_lightweight_deleted_rows
                            && (ranges.data_part->hasLightweightDelete() || alter_conversions->hasLightweightDelete()))
                        || alter_conversions->hasDeleteMutation();
                    if (index->isVectorSimilarityIndex() && has_deleted_rows)
                    {
                        return std::unexpected(PreformattedMessage::create(
                            "Index {} is not used for part {}. Reason: the part has deleted rows that the index still returns",
                            index->index.name, ranges.data_part->name));
                    }

A LIMIT 10 vector search over a part with masked-out rows would return fewer than 10 rows, because the index’s shortlist still contains the dead rows that get dropped afterwards. ClickHouse’s answer is to refuse to use the index for that part.

The same staleness reasoning applies to the minmax index, which is used for the ORDER BY ... LIMIT top-k optimization:

cpp 
// src/Storages/MergeTree/MergeTreeDataSelectExecutor.cpp:912
/// The minmax skip index reflects the physical rows of a part as they were written, so it goes
/// stale when a pending or materialized change is not yet merged into the part:
///  - a lightweight or ordinary delete hides rows, but the minmax still advertises their values;
///  - a lightweight update / patch part rewrites the indexed column, but the minmax still
///    advertises the pre-update values;
///  - an ALTER MODIFY COLUMN changes the indexed column's type, but the minmax still holds bytes
///    serialized with the old type, which order differently under the new type.
/// The top-k granule optimization keeps only the globally extreme granules, so a part whose stale
/// minmax advertises an extreme value can displace and prune a part that holds the live top rows,
/// yielding wrong (often empty) results. Exclude such parts from candidate selection; they are then
/// read in full with mutations and patches applied on read, while the optimization stays active for
/// parts whose top-k index is up to date.
static bool partHasStaleTopKIndex(

This is the general principle: a delete that is only a mask invalidates every index that was built on the pre-delete rows. The minmax still advertises the extremes of the dead rows.

The on-the-fly path: no mutation at all

Before the mask is materialized, the delete is not a mutation at all. It is a pending change that the reader applies.

AlterConversions holds the pending mutations for a part. For a lightweight delete, the mutation arrives as an UPDATE _row_exists = 0 command, and the on-the-fly machinery rewrites it into a DELETE command with the predicate attached:

cpp 
// src/Storages/MergeTree/AlterConversions.cpp:92
static MutationCommand createLightweightDeleteCommand(const MutationCommand & command)
{
    chassert(command.type == MutationCommand::Type::UPDATE);
    auto src_alter = command.ast();
    chassert(src_alter && src_alter->predicate != nullptr);

    auto alter_command = make_intrusive<ASTAlterCommand>();
    alter_command->type = ASTAlterCommand::DELETE;

    if (src_alter->partition)
        alter_command->partition = alter_command->children.emplace_back(src_alter->partition->clone()).get();

    if (src_alter->partitions)
        alter_command->partitions = alter_command->children.emplace_back(src_alter->partitions->clone()).get();

    alter_command->predicate = alter_command->children.emplace_back(src_alter->predicate->clone()).get();

The reason for the rewrite is that an UPDATE of a column that does not exist on disk cannot be evaluated — there is no _row_exists column to read. But a predicate can be evaluated against whatever columns the query reads. So the delete is converted from “set a column to 0” into “drop the rows where the predicate holds”, which is exactly the same semantics, evaluated differently.

The detection is narrow — only the exact _row_exists = 0 form:

cpp 
// src/Storages/MergeTree/AlterConversions.cpp:77
static bool isLightweightDeleteCommand(const String & column_name, const ASTPtr & ast)
{
    if (column_name != RowExistsColumn::name)
        return false;

    const auto * literal = ast->as<ASTLiteral>();
    if (!literal)
        return false;

    if (literal->value.getType() != Field::Types::UInt64)
        return false;

    return literal->value.safeGet<UInt64>() == 0;
}

And the rewritten command has to carry the original mutation’s version, or the patch-visibility window becomes unbounded:

cpp 
// src/Storages/MergeTree/AlterConversions.cpp:118
    /// The rewritten command stands for the same mutation, so it carries its version - as
    /// `createCommandWithUpdatedColumns` does for its own rewrite. Without it the on-fly stage built
    /// from this command has no version bound, `getMaxPatchVersionForStep` returns none, and the
    /// patch-visibility window of the stage is unbounded above: a patch part created *after* this
    /// `DELETE` would be applied before its predicate is evaluated.
    mutation_command->mutation_version = command.mutation_version;

The merge: when the dead rows actually die

A masked row is not gone. It occupies space, it is counted in the part’s row count, and it will be read and filtered on every scan until a merge removes it.

The merge is where the mask is applied for real. But the merge has a complication: the rows to drop may live in different parts, and the merge must decide what the output row count is. The machinery distinguishes a “vertical” merge (where the mask is carried through as a column) from a normal one:

cpp 
// src/Storages/MergeTree/MergeTask.cpp:3503
    global_ctx->vertical_lightweight_delete = isVerticalLightweightDelete(*global_ctx);
    global_ctx->vertical_ttl_delete = isVerticalTTLDelete(*global_ctx, *ctx);

    /// Do not apply mask for lightweight delete in vertical merge, because it is applied in merging algorithm
    bool apply_deleted_mask = !global_ctx->vertical_lightweight_delete;

    if (global_ctx->vertical_lightweight_delete)
    {
        merging_column_names.push_back(RowExistsColumn::name);
    }

In a vertical merge the mask is not pre-applied as a filter; instead the _row_exists column is added to the set of merging columns and carried through the merge algorithm, so the surviving rows keep their mask bits and the dead rows are dropped by the merge itself. The mask column then has to survive into the next part too:

cpp 
// src/Storages/MergeTree/MergeTreeData.cpp:6992
                /// a part written by a lightweight delete keeps its `_row_exists` through the mutation.

The other thing the merge has to get right is the part’s row count. A part with masked rows reports more rows than it has live ones, and the size accounting has to know the difference. ClickHouse tracks this in existing_rows_count:

cpp 
// src/Storages/MergeTree/IMergeTreeDataPart.cpp:2768
        || !(*storage.getSettings())[MergeTreeSetting::exclude_deleted_rows_for_part_size_in_merge]
        || !(*storage.getSettings())[MergeTreeSetting::load_existing_rows_count_for_old_parts])
        existing_rows_count = rows_count;
    else
        existing_rows_count = readExistingRowsCount();

readExistingRowsCount() opens a reader on the mask column alone and counts the non-zero bytes — a cheap, mask-only read:

cpp 
// src/Storages/MergeTree/IMergeTreeDataPart.cpp:2780
UInt64 IMergeTreeDataPart::readExistingRowsCount()
{
    const size_t total_mark = getMarksCount();
    if (!total_mark)
        return rows_count;

    NamesAndTypesList cols;
    cols.emplace_back(RowExistsColumn::name, RowExistsColumn::type);

The escape hatch: APPLY DELETED MASK

Because the mask is just data, you can ask ClickHouse to stop pretending:

sql 
ALTER TABLE t APPLY DELETED MASK;

The parser’s documentation is explicit about what it does:

cpp 
// src/Parsers/ParserAlterQuery.cpp:3594
The command applies mask created by [lightweight delete](/reference/statements/delete) and forcefully removes rows marked as deleted from disk. This command is a heavyweight mutation, and it semantically equals to query ```ALTER TABLE [db].name DELETE WHERE _row_exists = 0```.

It is a heavyweight mutation, because it actually rewrites the parts, dropping the dead rows. Its whole purpose is to reclaim the space that the mask is holding.

Trade-offs

What you gain. A delete that touches 10 thousand rows out of 100 million costs 100 MB of writes (the mask) instead of a full-part rewrite. The other columns are hardlinked — literally zero I/O for them.

What you pay. The dead rows stay on disk until a merge or an explicit APPLY DELETED MASK. The mask column costs one byte per row in every part that has been touched by a delete, forever, until then. And every read of such a part pays for the filter, plus the I/O of reading the mask column even if the query did not ask for it.

The subtle cost: index staleness. Any index built on the pre-delete rows is now wrong — not “less precise”, but wrong, in the sense that it can advertise values that no longer exist. ClickHouse handles this by refusing to use such indexes for affected parts (the vector-similarity and top-k minmax cases above), and by recomputing them during a mutation.

The subtle cost: the mask is not transactional. A DELETE FROM that is still pending is a predicate evaluated on the fly. A DELETE FROM that has been materialized is data. In between, the same logical delete has two different implementations, and the reader has to know which one applies. The AlterConversions machinery exists precisely to bridge that gap.

Not everything can be deleted this way. supportsLightweightUpdate() enumerates the restrictions:

cpp 
// src/Storages/MergeTree/MergeTreeData.cpp:14387
std::expected<void, PreformattedMessage> MergeTreeData::supportsLightweightUpdate() const
{
    /// TODO(unique-key): support lightweight updates.
    if (auto uk_metadata = getInMemoryMetadataPtr(getContext(), false); uk_metadata->hasUniqueKey())
        return std::unexpected(PreformattedMessage::create(
            "Lightweight updates are not supported on tables with UNIQUE KEY"));

    if (format_version < MERGE_TREE_DATA_MIN_FORMAT_VERSION_WITH_CUSTOM_PARTITIONING)
        return std::unexpected(PreformattedMessage::create(
            "Lightweight updates are supported only for tables with custom partitioning"));

    if (merging_params.mode == MergingParams::Summing || merging_params.mode == MergingParams::Aggregating || merging_params.mode == MergingParams::Graphite)
        return std::unexpected(PreformattedMessage::create(
            "Lightweight updates are not supported for tables with {}MergeTree engine", merging_params.getModeName()));

The value-combining engines are excluded because their merge algorithms aggregate rows — a masked row must not contribute to a sum, and the merging algorithms have no notion of a mask.

The bottom line

The lightweight delete is a small idea with a large payoff: turn a delete into a one-byte-per-row boolean column, hardlink everything else, and apply the column as a filter at read time. The rewrite of DELETE into UPDATE _row_exists = 0 is what makes it fit into the existing mutation machinery; the hardlink of the untouched columns is what makes it cheap; and the AlterConversions on-the-fly path is what makes it correct before the mutation has run.

The price is that deletion is deferred, deferred deletion invalidates indexes, and the deferred state has to be tracked carefully enough that a query never sees a half-applied delete. That is the trade ClickHouse makes, and it is why DELETE FROM on a MergeTree table is fast enough to be a routine operation rather than an operation.

ClickhouseInternalsMutationsDeletesPerformance