ClickHouse stuck mutations are one of the most confusing problems for teams new to ClickHouse. ALTER TABLE ... UPDATE or DELETE returns immediately, yet the data never changes, or changes on one replica but not another. The reason is that a mutation is not a query. It is a background job that rewrites data parts, and when it fails it keeps retrying quietly. The diagnosis starts in system.mutations.
How mutations actually work
A few facts from the documentation explain almost every "stuck" case (ALTER):
- Mutations are asynchronous by default. The ALTER returns once the mutation entry is recorded. The work happens in the background, much like merges.
- On MergeTree tables a mutation rewrites whole data parts. Changing one row in a 50 GB part means writing new files for that part, not editing the row in place.
- Mutations are totally ordered by creation time and applied to each part in that order. A mutation that cannot finish holds up every later mutation of that table.
- Data inserted before the mutation was submitted is mutated. Data inserted after it is not. Mutations don't block inserts.
- There is no atomicity: while a mutation runs, a SELECT can see a mix of mutated and unmutated parts.
- Lightweight
DELETE FROMis also a mutation. It becomesALTER TABLE ... UPDATE _row_exists = 0 WHERE ...(DELETE). It is cheaper, but it waits in the same queue.
If you want the client to wait, the mutations_sync setting controls it. 0 is asynchronous (the default), 1 waits on the current server, 2 waits on all replicas, and 3 waits only for active replicas (mutations_sync).
Step 1: list unfinished mutations
SELECT database, table, mutation_id, command, create_time,
parts_to_do, is_done, latest_failed_part, latest_fail_time, latest_fail_reason
FROM system.mutations
WHERE is_done = 0
ORDER BY create_time;
The columns that matter (system.mutations):
parts_to_do: how many parts still need to be rewritten. If it goes down over time, the mutation is slow, not stuck.latest_fail_reason,latest_failed_part,latest_fail_time: the last exception, and the part and time it happened. If these are filled in, you usually have your answer.command: the exact text afterALTER TABLE. Read it carefully. Many stuck mutations are someone's typo from three days ago.
On a replicated cluster, check every replica, because each one executes the mutation on its own parts:
-- the same across all replicas of a cluster
SELECT hostName() AS host, database, table, mutation_id, parts_to_do, latest_fail_reason
FROM clusterAllReplicas('default', system.mutations)
WHERE is_done = 0;
Cause 1: the expression fails on some data
ALTER TABLE events UPDATE amount = toDecimal64(raw_amount, 2) WHERE 1;
-- returns immediately, then fails in the background on rows where
-- raw_amount is not a number; latest_fail_reason shows the exception
This is the example the KILL documentation itself uses: a mutation that calls a function which throws on some rows can never finish (KILL). It retries, fails on the same part, and blocks everything queued after it. Fixing the data does not help, because the mutation will meet the next bad value. The fix is to kill it and resubmit a version that handles bad input, for example with an OrNull or OrZero conversion function.
Cause 2: not enough disk space
A mutation writes the new version of a part before the old one is removed, so it needs free space for what it rewrites. In our experience, heavy mutations (ALTER TABLE ... DELETE rewrites every column) on tables with very large parts stall on servers that look fine at 80% disk usage. Check free space against your biggest parts:
SELECT name, formatReadableSize(free_space) AS free, formatReadableSize(total_space) AS total
FROM system.disks;
-- size of the largest active parts of the table being mutated
SELECT partition, name, formatReadableSize(bytes_on_disk) AS size
FROM system.parts
WHERE database = 'default' AND table = 'events' AND active
ORDER BY bytes_on_disk DESC
LIMIT 5;
Cause 3: replicas that are behind or read-only
On ReplicatedMergeTree, mutation entries live in ZooKeeper or ClickHouse Keeper, and each replica works through them (ALTER). A replica that is lagging, offline or in read-only mode cannot finish its part of the work. A client running with mutations_sync = 2 then waits for it. Look at the replication queue for MUTATE_PART tasks:
SELECT database, table, type, new_part_name, num_tries, num_postponed,
postpone_reason, last_exception, is_currently_executing
FROM system.replication_queue
WHERE type = 'MUTATE_PART'
ORDER BY create_time
LIMIT 20;
num_tries and last_exception show tasks that keep failing. num_postponed and postpone_reason show tasks that are waiting their turn, for example behind a big merge or for a part to be fetched from another replica (system.replication_queue).
Cause 4: parts_to_do is 0 but the mutation is not done
The documentation describes this case: for replicated tables, a mutation can stay open because a long-running INSERT is creating a new part that still has to be mutated (system.mutations). Wait for the insert to finish before you conclude anything is broken.
Cause 5: too many mutations
Each mutation is cheap to submit and expensive to execute. Applications that send one ALTER UPDATE per business event pile them up fast. The MergeTree settings number_of_mutations_to_delay (default 500) and number_of_mutations_to_throw (default 1000) slow down and then reject new mutations with a "Too many mutations" exception (MergeTreeSettings.cpp). Raising the limits only hides the design problem. Batch the changes into fewer mutations, or redesign the table, as described in our article on deleting rows in ClickHouse.
Killing a mutation safely
-- mutation_id: 'mutation_N.txt' for MergeTree, a znode name like '0000000012' for Replicated
-- check first: lists what would be cancelled, cancels nothing
KILL MUTATION WHERE database = 'default' AND table = 'events'
AND mutation_id = 'mutation_1842.txt' TEST;
KILL MUTATION WHERE database = 'default' AND table = 'events'
AND mutation_id = 'mutation_1842.txt';
KILL MUTATION cancels and removes the mutations selected by the WHERE clause. The TEST form only shows what would be cancelled. Two things to know before you run it (KILL):
- Changes already made are not rolled back. Parts already rewritten keep the new values, others keep the old ones. After a kill, the table can be half updated. Plan the corrected mutation so that it is safe to run on both kinds of parts.
- Always filter on mutation_id.
KILL MUTATION WHERE table = 'events'cancels every pending mutation of that table, including the ones that were fine.
A stuck mutation blocks the mutations behind it. Because mutations are applied in order, one failing UPDATE from Monday can explain why Tuesday's DELETE and Wednesday's MATERIALIZE INDEX "did nothing". Always look at the oldest unfinished mutation first.
On large tables and clusters
A mutation on a small test table finishes before you can look at it. On billions of rows, the cost model dominates:
- Write amplification. A mutation rewrites every part that may contain matching rows. Updating a few thousand rows spread over two years of data can mean rewriting most of the table. Use
IN PARTITIONto limit the work when you know where the rows are (ALTER UPDATE). - Competition with merges. Mutations use the same background resources as merges. In our experience, a heavy mutation on a busy table lets parts pile up and can push the table toward "too many parts".
- Disk headroom on every replica. Each replica rewrites its own copy of the parts. Check free space on all of them, not just the one you are connected to.
- Keys cannot be updated.
ALTER UPDATEdoes not support columns used in the primary or partition key (ALTER UPDATE). Changing them means inserting corrected rows and removing the old ones. - Retention of history. Only the last
finished_mutations_to_keepfinished entries are kept (default 100), so investigate soon. A week later, the evidence may be gone. - Monitoring. Alert on the age of the oldest unfinished mutation and on non-empty
latest_fail_reason, not just on the count.
-- touch one partition instead of the whole table
ALTER TABLE events UPDATE status = 'refunded'
IN PARTITION '202611'
WHERE order_id IN (1001, 1002, 1003);
Key takeaways
- Mutations are asynchronous background rewrites of whole parts; the ALTER returning means nothing about completion.
- Start with system.mutations WHERE is_done = 0 and read latest_fail_reason.
- Mutations run in order: one failing mutation blocks all later ones on that table.
- On replicated tables, check every replica and system.replication_queue for MUTATE_PART tasks.
- KILL MUTATION does not roll back finished parts; filter on mutation_id and use TEST first.
- At scale, limit mutations with IN PARTITION, keep disk headroom and batch changes instead of firing one ALTER per event.