Avoid mutations

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/best-practices/avoid_mutations.md)

In ClickHouse, **mutations** refer to operations that modify or delete existing data in a table—typically using `ALTER TABLE ... DELETE` or `ALTER TABLE ... UPDATE`. While these statements may appear similar to standard SQL operations, they're fundamentally different under the hood.

Rather than modifying rows in place, mutations in ClickHouse are asynchronous background processes that rewrite entire [data parts](/docs/parts) affected by the change. This approach is necessary due to ClickHouse's column-oriented, immutable storage model, and it can lead to significant I/O and resource usage.

When a mutation is issued, ClickHouse schedules the creation of new **mutated parts**, leaving the original parts untouched until the new ones are ready. Once ready, the mutated parts atomically replace the originals. However, because the operation rewrites entire parts, even a minor change (such as updating a single row) may result in large-scale rewrites and excessive write amplification.

For large datasets, this can produce a substantial spike in disk I/O and degrade overall cluster performance. Unlike merges, mutations can't be rolled back once submitted and will continue to execute even after server restarts unless explicitly cancelled—see [`KILL MUTATION`](/docs/sql-reference/statements/kill#kill-mutation).

Monitoring the number of active or queued mutations in ClickHouse

For how to monitor the number of active or queued mutations refer to the following [knowledge base article](/docs/knowledgebase/view_number_of_active_mutations).

Mutations are **totally ordered**: they apply to data inserted before the mutation was issued, while newer data remains unaffected. They don't block inserts but can still overlap with other ongoing queries. A SELECT running during a mutation may read a mix of mutated and unmutated parts, which can lead to inconsistent views of the data during execution. ClickHouse executes mutations in parallel per part, which can further intensify memory and CPU usage, especially when complex subqueries (like x IN (SELECT ...)) are involved.

As a rule, **avoid frequent or large-scale mutations**, especially on high-volume tables. Instead, use alternative table engines such as [ReplacingMergeTree](/docs/guides/replacing-merge-tree) or [CollapsingMergeTree](/docs/engines/table-engines/mergetree-family/collapsingmergetree), which are designed to handle data corrections more efficiently at query time or during merges. If mutations are absolutely necessary, monitor them carefully using the system.mutations table and use `KILL MUTATION` if a process is stuck or misbehaving. Misusing mutations can lead to degraded performance, excessive storage churn, and potential service instability—so apply them with caution and sparingly.

For deleting data, you can also consider [Lightweight deletes](/docs/guides/developer/lightweight-delete) or the management of data through [partitions](/docs/best-practices/choosing-a-partitioning-key) which allow entire parts to be [dropped efficiently](/docs/sql-reference/statements/alter/partition#drop-partitionpart).

Was this page helpful?