ClickHouse Delete Rows: Lightweight DELETE, Mutations or DROP PARTITION

When you need to delete rows in ClickHouse, the SQL looks familiar, but the engine underneath is not. MergeTree tables store data in immutable parts, so a "delete" either hides rows and waits for merges to clean them up, or rewrites whole parts on disk. On a small table you barely notice the difference. On a table with billions of rows the choice decides whether the delete finishes in seconds, runs for a day, or gets stuck and blocks every change after it.

Three ways to delete, from cheapest to most expensive

1. Drop whole partitions (or TRUNCATE the table) when the data to remove lines up with the partition key. This removes parts as files without rewriting anything.
2. Lightweight DELETE FROM for targeted deletes. It marks rows as deleted and lets later merges remove them.
3. ALTER TABLE ... DELETE, a classic mutation that rewrites every part containing matching rows.

The DELETE documentation itself points to the first option: for frequent bulk deletes on a table with a suitable partitioning key, ALTER TABLE ... DROP PARTITION is faster.

Lightweight DELETE: what really happens

-- lightweight delete: marks rows, physical removal happens in later merges
DELETE FROM events WHERE user_id = 42;

-- limit it to one partition (needed explicitly on non-replicated MergeTree)
DELETE FROM events IN PARTITION '202611' WHERE user_id = 42;

Lightweight DELETE is still a mutation, but a cheap one. ClickHouse adds a hidden _row_exists column to the affected parts and runs the delete as an update of that column to 0. SELECT queries are rewritten to skip rows where it is false. In wide parts only this one column is written; compact parts are rewritten entirely.

Things to know before relying on it:

• The data stays on disk for a while. Rows are physically removed only by later merges, so space is not freed immediately. If you need predictable cleanup, the documentation suggests the min_age_to_force_merge_seconds table setting or a classic ALTER TABLE ... DELETE.
• By default the statement waits until rows are marked. lightweight_deletes_sync defaults to 2, which in the current source means waiting for all replicas; 0 returns immediately, and rows may stay visible until the background mutation finishes.
• Projections block it. By default DELETE fails on tables with projections; the MergeTree setting lightweight_mutation_projection_mode (default throw) can change that to drop or rebuild.
• Reads pay a price. The documentation warns that deleting large volumes this way can hurt SELECT performance, because queries keep filtering masked rows until merges remove them.
• Partition pruning. On ReplicatedMergeTree, conditions on the partition key in WHERE are used automatically to touch only matching partitions (optimize_mutations_with_partition_pruning, on by default). On non-replicated MergeTree you must add IN PARTITION yourself.

ALTER TABLE DELETE: a rewrite in disguise

-- classic mutation: rewrites every affected part
ALTER TABLE events DELETE WHERE event_date < '2024-01-01';

-- usually far cheaper for old data: drop whole partitions
ALTER TABLE events DROP PARTITION '202312';

The mutations documentation is blunt about how this works: mutations on MergeTree tables execute by rewriting whole data parts. A part with one matching row out of fifty million is written again in full. Some consequences:

• No atomicity and no rollback. Parts are swapped for mutated ones as soon as each is ready, so a query that runs during the mutation sees a mix. Once submitted, a mutation cannot be rolled back.
• It survives restarts. A submitted mutation continues after a server restart, so "restart and hope" does not cancel it.
• Inserts are not blocked. Rows inserted before the mutation was submitted are affected; rows inserted later are not.
• Strict ordering. Mutations are applied to each part in creation order. A slow mutation holds up every later mutation on the same table, including later lightweight deletes.
• Asynchronous by default. mutations_sync defaults to 0: the ALTER returns immediately and the work runs in the background. 1 waits on the current server, 2 on all replicas, 3 only on active replicas.

Watching and stopping mutations

Every mutation has a row in system.mutations:

SELECT database, table, mutation_id, command, create_time,
       parts_to_do, is_done, latest_failed_part, latest_fail_reason
FROM system.mutations
WHERE is_done = 0
ORDER BY create_time;

-- cancel a stuck mutation (already rewritten parts stay rewritten)
KILL MUTATION WHERE database = 'default' AND table = 'events'
  AND mutation_id = 'mutation_3.txt';

parts_to_do shows how many parts are still waiting. latest_fail_reason is the first place to look when a mutation does not progress: common causes are an expression that fails on some rows, or not enough disk space or memory for the rewrite. Note that for replicated tables, parts_to_do = 0 does not always mean the mutation is finished, because a long insert can still produce a part that needs mutating.

KILL MUTATION cancels a stuck mutation, but changes already made are not rolled back. Parts that were rewritten stay rewritten; parts that were not keep the old rows. After killing a delete, the table is in a partly deleted state, and you need to decide whether to resubmit a corrected command.

Finished mutations are kept in the list up to finished_mutations_to_keep (100 by default in the MergeTree settings). On replicated tables the mutation queue lives in Keeper, so if Keeper is unavailable, mutations stop as well; we explained that dependency in table is in readonly mode.

Large tables: what changes

• Disk space. A rewrite writes a new version of each affected part before the old one is removed. If your WHERE does not match the partition key, every part may qualify, and you effectively rewrite the whole table. Check free space against the size of the affected partitions, not the size of the deleted rows.
• Background pool. Mutations compete with merges for the same background threads, CPU and disk. A large rewrite slows down merges, and on a table with heavy inserts that can push you toward Too many parts.
• Mutation limits. MergeTree settings artificially slow down mutations once a table has 500 unfinished ones (number_of_mutations_to_delay) and reject new ones with "Too many mutations" at 1000 (number_of_mutations_to_throw). A GDPR job that sends one DELETE per user can hit that quickly. Batch the IDs into one statement with IN.
• Replicas. Mutations are coordinated through Keeper, and with mutations_sync = 0 your client has no idea when the last replica finished. For data that must be gone, wait with 2 or check system.mutations on every replica.
• Lightweight is not free at scale. Marking a large share of a big table as deleted leaves a lot of masked rows for queries to skip, and the space comes back only after merges. For deleting old data, partitions or TTL are almost always better.

Design for deletes up front

The cheapest delete is one you planned when you created the table. Partition by month or day if old data is removed by age, then drop partitions or use a TTL clause to expire data automatically. If you need to delete individual users or records regularly, keep those deletes batched and expect masked rows until merges catch up. For updates rather than deletes, newer ClickHouse versions also offer lightweight UPDATE with patch parts. The documentation says it is designed for small changes, up to about 10% of a table, and that it adds overhead to queries that must apply patches.

Checklist before deleting rows in ClickHouse

1. Can the delete be expressed as dropping partitions or as a TTL? Use that.
2. Check system.mutations for unfinished or failing mutations on the table.
3. For lightweight DELETE, check for projections and add IN PARTITION on non-replicated tables.
4. For ALTER TABLE ... DELETE, estimate the size of affected parts and confirm free disk space.
5. Batch many small deletes into one statement.
6. Decide how to wait: mutations_sync or lightweight_deletes_sync, or monitor system.mutations on every replica.
7. Have a plan for KILL MUTATION, remembering that it does not undo finished parts.