Mutations (UPDATE / DELETE)

ALTER TABLE ... UPDATE and ALTER TABLE ... DELETE exist and use familiar syntax, but they don't work anything like an OLTP UPDATE/DELETE. Understanding why comes straight from the same fact covered in Parts & Background Merges: parts are immutable. Nothing in a part is ever edited in place — not even by a mutation.

What actually happens

A mutation is applied asynchronously, part by part. For every existing part that contains at least one row matching the mutation's condition, ClickHouse rewrites the entire part from scratch — every column, every row, not just the ones that changed — with the update or deletion applied, then atomically swaps the new part in for the old one.

ALTER TABLE events UPDATE status = 'archived' WHERE event_date < '2026-01-01'

Mutation submittedqueued, returns immediately

Matching parts foundany part with 1+ matching row

Atomic swapnew part replaces old

Full part rewriteevery row, every column

ALTER TABLE events
UPDATE status = 'archived'
WHERE event_date < '2026-01-01';

ALTER TABLE events
DELETE WHERE event_date < '2020-01-01';

ALTER TABLE ... UPDATE/DELETE statements return immediately after being queued — they don't block waiting for the rewrite to finish. Track progress with:

SELECT * FROM system.mutations WHERE table = 'events' AND NOT is_done;

See System Tables for more on querying operational state like this directly with SQL.

Common mistake

A single UPDATE touching even a small fraction of rows can force a rewrite of an entire large partition's worth of parts, competing for I/O with normal background merges. Mutations are for occasional corrections and backfills, not a routine part of your application's write path — if a workload needs frequent row-level updates, that's a sign ClickHouse (or at least this table's design) is the wrong tool for that part of the job.

Lightweight DELETE: the cheaper alternative

Newer ClickHouse versions support DELETE FROM table WHERE ... as a distinct, lighter-weight operation. Instead of immediately rewriting affected parts, it marks the matching rows as deleted in a mask; those rows are then filtered out at query time and physically dropped later, as a side effect of normal background merges — the same mechanism that already reclaims space for other reasons.

DELETE FROM events WHERE event_date < '2020-01-01';

This is meaningfully cheaper for deletes specifically, but it is still not free, and it doesn't help with UPDATE — there is no equivalent "lightweight update", because changing a value (as opposed to hiding a row) has no way to be deferred the same way.

Production note

If a table's natural access pattern really is "replace/deduplicate by key on write," reach for ReplacingMergeTree instead of planning around frequent mutations — let merges resolve the latest version the way the engine already does it for free, rather than paying full part rewrites on every correction.