Stuck ClickHouse mutations: find and cancel

Finding and cancelling stuck ClickHouse mutations starts with knowing what a mutation is doing

When an ALTER TABLE ... UPDATE or DELETE has been running far longer than expected, or a column change keeps failing, the first step is to find out what the mutation is doing before cancelling anything. In ClickHouse, a mutation is an asynchronous background operation that rewrites the data parts of a table to apply an UPDATE, DELETE or schema change. Unlike a normal insert, which writes new parts, a mutation takes existing parts, rewrites each one with the change applied, and swaps the new parts in. Because the rewrite touches every affected part, a broad predicate on a large table can keep background merge and mutation threads busy for a long time, and queries against those parts can slow down while it runs. The rewrite is the reason mutations need watching. Each mutation is recorded in the system.mutations table, where the is_done column, the parts_to_do count and the latest_fail_reason field show whether it is progressing, queued, or repeatedly failing on a specific part. A mutation that stays at a nonzero parts_to_do with the same failure reason across checks is stuck, and a stuck mutation keeps retrying in the background. Your team should own a check of this table as part of routine monitoring, because a failing mutation often produces no error in the application layer. The KILL MUTATION statement can cancel a mutation that is no longer wanted, but it does not undo parts that were already rewritten, so confirm the affected rows and parts before you act. Bring in outside help when a stuck mutation sits on a replicated table, when the failure reason points at Keeper or replica state, or when the table is large enough that a wrong cancellation or a retried rewrite could affect production. Confirm with your own team which ClickHouse release line you run and how its system.mutations fields behave on that version, since the documented behavior is tied to the version in use, and check the ClickHouse production FAQ for guidance on testing changes before they reach production.

Why a single ALTER UPDATE can rewrite far more data than expected

A mutation such as ALTER UPDATE or ALTER DELETE does not touch only the rows it changes. ClickHouse rewrites every data part that contains at least one row matching the predicate, so the work scales with how many parts the filter reaches, not with the row count in the statement. A filter on a column that is spread across most partitions, or that is not part of the ordering key, can force a rewrite of a large share of the table even when only a few thousand rows change. Before running a mutation on a large table, check how the filter maps to partitions and parts, and confirm with the ClickHouse documentation for your version how mutations select parts and whether any newer lightweight update behavior applies to your table engine.

A long mutation shares the server with everything else. Each rewritten part is a new physical write, which competes with background merges for disk I/O and CPU, and it also competes with user queries that read the same parts. Merges that are blocked or slowed can push part counts up, which feeds the too many parts problem covered elsewhere on this page, while queries slow down during the rewrite window. Plan the mutation as a load event: schedule it outside peak hours, watch disk and merge activity while it runs, and be prepared to cancel it if the impact grows beyond what your service levels allow.

On a replicated table, the mutation is recorded once in ZooKeeper or ClickHouse Keeper, but each replica performs the rewrite of its own parts. The cost is therefore multiplied by the replica count, and a replica that is already short on resources can fall behind or fail its part of the mutation. Confirm with the replication documentation how your version coordinates mutations across replicas, and check whether your table layout, partition key and replica count make the expected rewrite affordable on the hardware you run. Test the same statement on a pre-production copy with representative data volumes before the production run.

Which response fits each kind of stuck mutation

This table matches five common mutation situations to what to check in system.mutations, the response, and the main risk. Read it before running any KILL MUTATION, because the right response depends on what the mutation is doing and what depends on it.

Situation	What to check in system.mutations	Response	Main risk
Queued behind other mutations	Creation time, parts_to_do, and whether earlier mutations on the same table are still running	Wait for the earlier mutations to finish, then confirm this one starts. Do not cancel it yet.	Cancelling it only moves the problem, and a later mutation may be the one you actually need to stop
Failing repeatedly with the same error	latest_fail_reason and latest_fail_time, plus whether the same error repeats across retries	Confirm the cause (for example, a missing column or a type conflict) and fix the table or the statement, then decide whether to cancel	Cancelling before the cause is known can leave data half-changed or hide a schema problem from the team
Running far longer than usual for this table	parts_to_do trend over time, the table size, and how much of the workload the rewrite touches	Check the rewrite scope with the owner and compare against earlier runs before deciding	Cancelling a mutation that is almost done wastes the work, and letting an oversized rewrite continue can slow merges and queries
Started by mistake	The exact command text, the database and table, the user who ran it, and the create time	Confirm with the person who ran it and the table owner, then cancel with KILL MUTATION using the mutation ID	Partial changes may already be applied to some parts, so the table can end up in a mixed state that needs a check
Needed, but blocking other work	Whether it is on a critical table, what other mutations or merges wait on it, and the expected completion point	Schedule the work for a quieter window or split it, and confirm the business owner accepts the delay before any cancellation	Cancelling a required change leaves the intended data change undone, and rerunning it later may hit the same load

Finding and cancelling a stuck mutation without making the outage worse

This sequence moves from identifying an unfinished mutation to cancelling it with a narrow filter, so the fix removes the stuck work without widening the damage to the table.

Query system.mutations for unfinished rows
Run a query against system.mutations filtered to rows where is_done is 0, and read the latest_fail_reason and latest_fail_time columns for each one. The operator on call does this first, usually from the ClickHouse client on a replica. The step is done when you have a list of unfinished mutation IDs with their database, table and failure reasons, or confirmation that none remain. Record the command text and timestamps so the decision can be reviewed later.
Identify the affected table and replica
For each unfinished mutation, confirm the database and table it targets, then check the replication state of that table on each replica in system.replicas, including queue size and any exception fields. A mutation may be stuck on one replica while others have finished it. The step is done when you can name the table, the replicas involved, and whether the replication queue is blocked by this mutation or by something else.
Confirm the filter and expected scope with the submitter
Read the mutation_command column for the stuck mutation and compare the WHERE clause with what the requester intended. Contact the person or team that submitted it and agree on the rows that should be affected, the expected row count, and whether the change can be re-submitted later in smaller batches. The step is done when the submitter confirms the intended scope in writing, or when the mutation is identified as one nobody still needs.
Run KILL MUTATION with a precise filter
Issue KILL MUTATION with a WHERE clause that matches only the mutation ID or the exact database, table and mutation_id you confirmed, rather than a broad filter that could cancel unrelated work. Run it on the cluster as your deployment requires, and keep the command and its output in the change record. The step is done when the command returns without error for the intended mutation only.
Verify the mutation is gone and merges recover
Re-run the system.mutations query and confirm the cancelled mutation no longer appears as unfinished. Then check system.merges and system.replication_queue for the table to see that background merges are running and the queue is draining, and confirm that test inserts succeed on each replica. The step is done when the mutation is absent, merges progress and inserts are accepted.
Confirm table state before any retry
A cancelled mutation may leave data partially rewritten, because parts already processed may carry the change while others do not. Compare row counts and sample the affected rows against the intended scope on each replica, and check that replicas agree. The step is done when the table state matches what the submitter expects, or when you have a documented plan to correct the remaining rows. Do not re-submit the mutation until this check passes.
Resubmit only after a tested, narrower change
If the change is still needed, rehearse the mutation on a pre-production copy with representative data, then submit it with a narrower filter or in smaller batches so that each rewrite is bounded. Watch system.mutations while it runs. The step is done when the rehearsal finishes cleanly and the production mutation completes with the expected rows changed.

Operational risks when cancelling or retrying a mutation

Cancelling or re-running a mutation changes data that is already partly rewritten, so each action carries risk beyond the statement itself. The cards below cover the mistakes that most often turn a stuck mutation into a larger incident, with the check to run and the role that should sign off before anyone touches a production table.

Cancelling without confirming the filter

A mutation is an ALTER TABLE ... UPDATE or DELETE with a WHERE clause, and KILL MUTATION stops the rewrite from that point. Before cancelling, the operator should read the mutation_id, the command text in system.mutations, and the parts_to_do count, then confirm which rows the statement targets. Approval should come from the data owner, since a cancelled delete can leave records that should have been removed.

Retrying the same statement unchanged

A mutation that failed on one part often fails again when resubmitted with identical SQL, because the cause (a type mismatch, a missing column or a resource limit) has not changed. Check the latest_fail_reason and latest_fail_time columns and fix the cause before issuing a new statement. The database engineer who owns the schema should approve any resubmission.

Losing track of which replica applied what

On a replicated table, a mutation is coordinated through Keeper and each replica applies its own parts, so a status check on one node can miss progress or failures on another. Query the mutation status across the cluster, compare results per replica, and record which replicas reported completion. The platform owner should confirm that every replica converged before the change is closed.

Missing the restore decision after a partial rewrite

A mutation that stops partway can leave some parts rewritten and others untouched, and replication copies that state to every replica. Replication does not protect against this kind of human error, so the team should decide early whether a restore from a backup is needed and whether the backup they plan to use has been test-restored. The incident owner and the data owner should make that call together.

Running several mutations on one table

Queued mutations on the same table are applied in order, so a second large rewrite can extend the time the first one holds resources and delays merges. Before submitting another change, list the active rows in system.mutations for that table and check the queue depth. The change owner should approve adding work to a table that already has pending mutations.

Giving broad rights to run KILL MUTATION

Any account allowed to issue mutations or kill them can stop or alter production data, and the default user starts with all permissions. Review which users and roles hold the rights to run ALTER and KILL MUTATION, and grant them only to named operators on a dedicated role. The security owner should approve each grant, and the access control documentation describes how to restrict the default user and enforce role-based rights.

Preventing large rewrites from hurting production by team and situation

Analytics teams often run ad hoc corrections, such as fixing a mislabeled dimension or removing a batch of bad rows. These requests tend to arrive without a rewrite estimate, and the person running them may not know how many data parts the change touches. Agree on a process before the first correction runs.

Test on representative data: Run the exact ALTER TABLE ... UPDATE or DELETE on a copy of a recent partition in a pre-production cluster, and record how long it takes and how many parts it touches.
Batch by partition: Restrict each statement with a partition predicate so that one mutation rewrites one partition at a time, and wait for system.mutations to show completion before submitting the next.
Schedule outside peak hours: Agree on a window with the teams that query the table, and confirm who can stop a running mutation during that window.
Review the changelog: Check the release notes for the target version before running large corrections, since mutation behavior can change between releases.
Confirm a restore path: Take a backup or part freeze of the affected partitions before the change, and confirm that someone has restored a sample of it recently.

Before approving a correction, check that the test run, the partition list and the rollback plan are written down in the ticket.

Platform teams often receive deletion requests driven by compliance obligations, such as erasing personal data for a subject access or erasure request. These requests carry a deadline, and they can cover many partitions across many tables. The control problem is to meet the obligation without launching a broad rewrite that stalls replicas.

Scope the deletion precisely: Map each request to the tables and partitions that hold the data, and prefer partition-level handling where the data model allows it.
Stage the rewrite on a spare cluster: Run the deletion first on a restored copy so the team can measure its effect on merges and query latency before production.
Throttle by partition: Submit deletions in small, partition-scoped batches, and do not start a new batch while system.mutations still lists failing or long-running work.
Check replica health first: Confirm that all replicas are healthy and that Keeper is responding before submitting work, because a stuck mutation on one replica affects every replica that shares the table.
Record the evidence: Keep the request, the statements, the timestamps and the backup reference together so that auditors can trace each deletion.

Before each batch, confirm with your compliance owner which tables are in scope and that a tested restore exists for the rest of the data.

During an incident, a stuck or failing mutation can block merges, consume I/O and hold up inserts. Responders are under pressure to act quickly, but a hurried cancellation or resubmission can make the situation worse. The controls below should be agreed in advance so that the response team follows them under stress.

Assess before acting: Read system.mutations for the affected table, including the failure reason and the parts still pending, before running any cancellation.
Cancel with a defined scope: Use KILL MUTATION with the mutation identifier, and record who ran it and when, so that the state can be explained later.
Do not resubmit blindly: Confirm the root cause, such as a bad predicate, a full disk or a replica issue, before submitting the same statement again.
Escalate to a named owner: Assign one person to decide whether to pause ingestion, fail over reads or restore data from backup.
Use the tested restore path: If data was damaged, restore from a backup that has been checked in a spare cluster, since an untested backup may not restore when needed.

After the incident, check that the runbook, the cancellation record and the backup reference were updated, and that the team reviewed the change process.

Limits to confirm for your ClickHouse version and table design

A mutation rewrites data in place, so its effect depends on the exact server version, the support status of that version, how replicas apply it, and how the table is laid out. Confirm each item below before you run a large change on production, and use the vendor documentation for behavior and your own staging environment for timing and scope.

Mutation behavior in your version

The columns, status values and KILL MUTATION semantics in system.mutations can change between releases, so read the reference for the exact build you run. Confirm whether a cancelled mutation leaves partially rewritten parts behind and how a failed mutation reports its error. Then reproduce a kill on a staging copy of one table before you rely on it during an incident.

Support status of your version

Check the security policy for the versions still receiving security updates, and treat any version outside that list as a risk to plan around. A mutation bug fixed in a later release may not be backported to your line. If you run an older build, schedule an upgrade before you depend on mutations for large corrections.

Mutation effects across replicas

On a replicated table, the mutation is coordinated through Keeper and each replica applies it to its own parts. Confirm how the mutation status looks on every replica, not only the one you query, and whether a replica that is behind or offline delays completion. Verify that a replica which recovers mid-mutation catches up to the same final state.

Ordering key and partition scope

The ordering key and partition key decide how many parts a WHERE clause touches, which sets how much data a mutation rewrites. Before a large UPDATE or DELETE, confirm the predicate filters on partition or key columns and estimate affected parts with a count query on staging. A predicate on a non-key column can force a rewrite of most of the table.

Backup coverage before large changes

Replication does not protect against a mutation that writes wrong values, because every replica applies the same change. Confirm your backup includes the target table, that the most recent backup is complete, and that you can restore it to a spare cluster. Record the backup name and time so you can roll back a bad rewrite.

Keeper load during heavy background work

Mutations add coordination traffic and background merge work, and Keeper serves both. Confirm the Keeper nodes run on dedicated hosts with enough memory for your cluster size, and watch Keeper latency and leader stability while the mutation runs. Set a rule to pause large rewrites if Keeper warnings appear.

Questions teams ask before bringing in outside help with stuck mutations

What drives the cost of fixing stuck ClickHouse mutations?

The cost depends on how much work the incident touches, not on the mutation command itself. The main drivers are the size of the affected tables, the number of replicas and shards that must be checked, whether a staging copy with representative data exists for testing, and how many tables are affected. A one-off cancellation on one table is a small task. Recovering a rewrite that has run for hours across several replicas, with failed parts and no clear record of who issued the change, takes longer. Ask any quote to itemize the tables in scope, the cluster topology, the staging access assumed, and whether the work is a single incident or a review of the wider mutation pattern. DeployOpen plans work from a written scope, so the quote should state exactly what is included.

How long does it take to resolve a stuck mutation?

Duration depends on how far the mutation has progressed and how many parts it has already rewritten. A mutation that has not started rewriting data can usually be cancelled quickly, while one that has rewritten many parts across replicas needs more checking afterward. Time is also spent confirming that replicas agree, that no part is left in a failed state, and that the change you intended has either been applied or removed. Ask the provider to state the expected steps and the checks each step requires, and to say which of those depend on access to your environment. Any figure for duration should appear in the written quote, tied to the scope it covers.

What does the client's team need to provide for this work?

The work needs read access to the cluster's system tables, the mutation history, and the query and definition of each stuck mutation. It also needs a named owner who can approve the cancellation or the rewrite, because cancelling a mutation changes what data ends up in the table. Expect to provide the ClickHouse version, the topology of replicas and shards, the ClickHouse Keeper setup, and recent backup status. A staging environment with representative data makes the rewrite safer to test. Confirm in the quote who holds production credentials and how actions are logged and approved during the engagement.

Can an internal engineer cancel a stuck mutation without outside help?

Yes, a team that runs ClickHouse can often cancel a single mutation without outside help, provided it reads the state first. The core steps are to query system.mutations for rows where is_done is 0 and to read latest_fail_reason, then cancel the mutation with KILL MUTATION using the table and mutation identifier. The risk lies in what happens next: partially applied changes, replicas that disagree, and parts that have already been rewritten. Outside help is most useful when the mutation spans many tables, when no one on the team knows what the command was meant to do, or when the cluster is already under load. Check the official mutation documentation for the exact syntax supported by your version before running anything.

When does a retainer make more sense than a one-time fix?

A retainer fits teams whose clusters change often, where stuck mutations recur or where upgrades, backups and monitoring need steady attention. A one-time fixed-scope engagement fits a single incident with a clear boundary. DeployOpen offers ongoing support, upgrades and continuous testing as retainers, which can include regular review of mutation queues and alerting. Ask for the coverage hours, response expectations and what the retainer excludes, and compare that against how often your team has needed help in the past year. Retainer pricing is quoted separately from the written scope, so request the terms in writing.

Should we take a backup before rewriting a large table?

Yes, a verified backup should exist before any large rewrite, because replication does not protect against human error or corruption. ClickHouse BACKUP and RESTORE can take full or incremental backups of tables, partitions or databases to local disk, S3-compatible storage or Azure Blob Storage. The backup documentation also warns that a backup never test-restored will probably not restore properly when needed. Before a rewrite, confirm that a recent backup exists for each affected table, that it has been restored successfully on a spare cluster, and that you know how long the restore takes. Ask any provider to include this check in the scope rather than assume it.

How do we prevent stuck mutations from happening again?

Repeat incidents usually trace to a few habits: large ALTER TABLE ... UPDATE or DELETE statements run directly on production, no alert on system.mutations, and no staging test of the rewrite. Prevention starts with monitoring queued and failing mutations, setting an alert on mutations that stay unfinished or record a failure reason, and reviewing the definitions before they run. Teams often move row-level changes into a designed ingestion or partition strategy where possible. Ask any provider to define the alert thresholds, the review step for mutations, and the staging test that must pass first. Confirm which of these your own team will own after the engagement ends.

What does a fixed-scope engagement for this topic cover?

A fixed-scope engagement sets out, in writing before work starts, the systems covered, the deliverables and the timeline. For stuck mutations, that usually includes reviewing the mutation state across the cluster, cancelling or completing the affected mutations under an agreed approval process, confirming replica consistency, checking the backup position, and documenting what was done. It may also include setting up monitoring on system.mutations and a short handover. It does not usually include changes to unrelated tables, application code or the wider data model unless the written scope names them. Request a scope document that lists each deliverable and the environment it applies to, so the boundary is clear before any production change is made.

How DeployOpen scopes and supports mutation work on your ClickHouse cluster

DeployOpen approaches stuck mutation work as part of running self-managed ClickHouse in your own infrastructure. We start by reviewing how your cluster queues and applies mutations, then agree on monitoring, cancellation procedures and rewrite controls in writing before any change touches production data.

How we scope

Review your current mutation history in system.mutations, including which tables, partitions and statements generate the most rewrite load
Agree in writing which mutations may be cancelled, who approves a KILL MUTATION, and which tables are off limits
Confirm the cluster topology, replica count and backup posture that the mutation plan must work around

How we build

Build monitoring queries and alert rules for queued and failing mutations on each replica, using the same schema you run in production
Rehearse KILL MUTATION and rewrite-reduction changes in a pre-production environment with representative data and queries
Document batching rules, delete and update patterns and off-peak scheduling so large rewrites stay away from production traffic

How we hand over

Deliver a runbook that covers detection, confirmation of the failing part, cancellation steps and post-cancel verification
Walk your operators through the monitoring dashboards and alerts in a session tied to your own cluster
Record the agreed changes, settings and test results so your team can audit and repeat the procedure later

How we support

Engagements are fixed-scope, with deliverables and timelines agreed in writing before work starts
Ongoing support, upgrades and continuous testing are available as a retainer if your team wants continued cover
Retainer scope, response expectations and review cadence are confirmed in the written agreement, and quotes follow from that scope

Finding and cancelling stuck ClickHouse mutations