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';

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.

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.