ClickHouse Mutations

ClickHouse has no row-level update. ALTER TABLE ... UPDATE and ALTER TABLE ... DELETE are mutations: asynchronous background jobs that rewrite every affected part.

ALTER TABLE events DELETE WHERE tenant_id = 42;
ALTER TABLE events UPDATE event_type = 'checkout' WHERE event_type = 'purchase';

Tracking a mutation

SELECT database, table, mutation_id, command,
       parts_to_do, is_done, latest_fail_reason, create_time
FROM system.mutations
WHERE NOT is_done;
 
-- Cancel one that is still running.
KILL MUTATION WHERE mutation_id = '0000000042';

parts_to_do counts the parts remaining. A mutation that fails records the reason and blocks the mutations queued behind it for that table — check latest_fail_reason when mutations appear stuck.

Lightweight deletes

DELETE FROM events WHERE tenant_id = 42;

DELETE FROM marks rows deleted with a mask instead of rewriting parts immediately. It returns much faster, and the rows are physically removed at the next merge. Deleted rows still occupy disk until then, and the mask is applied at query time, so heavy use costs read performance.

It is the right tool for small, occasional deletions. It is not a substitute for partition-based retention.

Prefer designs that avoid mutations

Drop a partition for range deletion — see Partitioning.

Use TTL for age-based expiry — see TTL.

Use ReplacingMergeTree for "update" semantics: insert a new row with a higher version and let merges discard the old one.

Deduplication

Duplicates arrive from at-least-once pipelines and from retried inserts. There are three levels of defence.

Insert-level. Replicated tables hash each inserted block and ignore an exact repeat within a window. Give the batch an explicit token so retries deduplicate reliably:

SET insert_deduplication_token = 'orders-2026-07-31-batch-17';

Merge-level. ReplacingMergeTree keeps one row per sorting key at merge time:

CREATE TABLE orders_latest
(
    order_id   UInt64,
    updated_at DateTime,
    status     LowCardinality(String)
)
ENGINE = ReplacingMergeTree(updated_at)
ORDER BY order_id;

Query-level, because merge-level deduplication is eventual:

-- Correct but expensive: forces a merge-like read.
SELECT * FROM orders_latest FINAL WHERE order_id = 4711;
 
-- Usually better: aggregate explicitly.
SELECT order_id, argMax(status, updated_at) AS status
FROM orders_latest
WHERE order_id = 4711
GROUP BY order_id;