ClickHouse UPDATE/DELETE Mutations Performance Impact

ALTER TABLE ... UPDATE and ALTER TABLE ... DELETE are the standard mechanisms for modifying existing data in ClickHouse. Unlike in a traditional RDBMS where a row is updated in place, ClickHouse implements these operations as mutations — background jobs that rewrite entire data parts on disk. A mutation that touches a single row still rewrites every row in the data part that contains it. On large tables with many parts, this results in substantial I/O and CPU load that competes directly with merges and queries.

What This Means

Mutations in ClickHouse are not transactional row updates. They are deferred, destructive part-rewriting operations with the following properties:

• Only available for MergeTree family engines. Log, Memory, and other engine families do not support ALTER TABLE UPDATE/DELETE.
• Asynchronous by default. The ALTER TABLE ... UPDATE/DELETE statement returns immediately; the actual rewrite happens in the background. The mutations_sync setting (default: 0) controls this.
• Parts are rewritten, not rows. Each affected data part is fully read, transformed, and written as a new temporary part. For Wide format parts (each column in a separate file), unaffected columns are hardlinked rather than rewritten — only mutated columns get new files. For Compact format parts (all columns in one file per part), all columns must be rewritten regardless of how many columns the mutation touches.
• Primary key and sorting key columns cannot be updated. Columns in the ORDER BY expression are immutable via ALTER TABLE UPDATE. Attempting this throws an error.
• Mutations execute sequentially per table. Multiple queued mutations run one after another, though ClickHouse may merge consecutive pending mutations into a single rewrite pass.
• Mutations share the background thread pool with merges. While mutations are running, merge throughput drops and part counts can grow, potentially triggering insert throttling.

Common Causes of Performance Problems

1. Frequent small mutations. Issuing many separate ALTER TABLE UPDATE or ALTER TABLE DELETE statements creates many queued mutations. Even though each may seem small, they serialize and each one triggers its own part-rewrite cycle.

2. Mutations on wide tables with many parts. The more parts a table has (due to frequent small inserts or slow merges), the more individual rewrite jobs a mutation spawns.

3. Compact format parts. Tables using Compact format (common for smaller parts) require full part rewrites on mutation even when only one column changes.

4. Mutations running on replicated tables. In ReplicatedMergeTree, each mutation must complete on every replica, multiplying total I/O. The max_replicated_mutations_in_queue setting (default: 8) limits simultaneous mutation tasks in the replica queue.

5. Concurrent reads during mutation. A SELECT running while a mutation is in progress may read a mix of mutated and unmutated parts, producing an inconsistent view. Mutations do not block reads.

6. Background pool exhaustion. The number_of_free_entries_in_pool_to_execute_mutation setting (default: 20) prevents mutation scheduling when the background pool has fewer than this many free threads. If the pool is busy with merges, mutations stall.

How to Fix

1. Combine multiple column updates into a single mutation

Multiple column assignments in one ALTER TABLE UPDATE statement create a single mutation. Separate statements each create their own mutation.

-- Inefficient: two mutations, two rewrite passes
ALTER TABLE orders UPDATE status = 'closed' WHERE created_at < '2023-01-01';
ALTER TABLE orders UPDATE updated_at = now() WHERE created_at < '2023-01-01';

-- Efficient: one mutation, one rewrite pass
ALTER TABLE orders UPDATE status = 'closed', updated_at = now() WHERE created_at < '2023-01-01';

2. Wait for mutation completion when needed

By default, mutations are async. Use mutations_sync when downstream steps depend on the mutation finishing.

-- Wait for the mutation to complete on the current replica
ALTER TABLE orders UPDATE status = 'closed' WHERE id = 42 SETTINGS mutations_sync = 1;

-- Wait for the mutation to complete on all replicas
ALTER TABLE orders UPDATE status = 'closed' WHERE id = 42 SETTINGS mutations_sync = 2;

-- Set for the session
SET mutations_sync = 1;
ALTER TABLE orders UPDATE status = 'closed' WHERE created_at < '2023-01-01';

3. Use lightweight DELETE for row removal (ClickHouse 23.3+)

Lightweight DELETE (DELETE FROM syntax) was GA since ClickHouse 23.3 (it required allow_experimental_lightweight_delete = 1 before 23.3). Instead of rewriting parts immediately, it marks rows deleted via a hidden _row_exists column (UInt8: 1 = visible, 0 = deleted). SELECT queries are automatically rewritten to add PREWHERE _row_exists = 1, so deleted rows are invisible immediately. Physical removal from disk happens lazily during background merges.

-- Lightweight DELETE: marks rows deleted immediately, cleans up during merges
DELETE FROM events WHERE user_id = 12345;

-- Lightweight DELETE targeting a specific partition
DELETE FROM events IN PARTITION '202301' WHERE user_id = 12345;

-- Make lightweight DELETE asynchronous (default is synchronous, lightweight_deletes_sync = 2)
DELETE FROM events WHERE user_id = 12345 SETTINGS lightweight_deletes_sync = 0;

Note: lightweight_deletes_sync defaults to 2 (wait all replicas synchronously), which is the opposite of heavy mutation behavior. Set it to 0 for fire-and-forget deletes.

Lightweight DELETE does not work by default on tables with projections. Use the lightweight_mutation_projection_mode setting (throw / drop / rebuild) to control this.

4. Use TTL for time-based deletion

TTL-based expiration removes data during normal background merges without triggering explicit mutations:

ALTER TABLE events MODIFY TTL event_date + INTERVAL 90 DAY;

5. Redesign for append-only patterns

ClickHouse is optimized for append-only workloads. Several engine variants handle mutable data without explicit mutations:

• ReplacingMergeTree: deduplicates rows with the same sorting key, keeping the row with the highest version value. "Updates" are inserts with a higher version.
• CollapsingMergeTree / VersionedCollapsingMergeTree: sign-based row collapsing for streaming update patterns.

CREATE TABLE orders (
    id UInt64,
    version UInt64,
    status String,
    updated_at DateTime
) ENGINE = ReplacingMergeTree(version)
ORDER BY id;

-- "Update" by inserting a new version
INSERT INTO orders VALUES (42, 2, 'closed', now());

6. Run large mutations during low-traffic windows

Use ALTER TABLE ... ON CLUSTER during maintenance windows when query load is minimal:

ALTER TABLE events ON CLUSTER my_cluster DELETE WHERE event_date < '2022-01-01';

7. Force physical cleanup after lightweight deletes

Lightweight deletes do not immediately free disk space. To reclaim storage, trigger a merge:

OPTIMIZE TABLE events FINAL;

Use with caution on large tables — OPTIMIZE TABLE FINAL can be expensive.

Root-Cause Analysis

Check all pending mutations

SELECT
    database,
    table,
    mutation_id,
    command,
    create_time,
    parts_to_do,
    is_done,
    latest_fail_reason
FROM system.mutations
WHERE is_done = 0
ORDER BY create_time;

parts_to_do shows how many data parts still need processing. Note: for replicated tables, parts_to_do = 0 with is_done = 0 can occur when a long-running INSERT is creating a new part that the mutation still needs to process.

Check mutations for a specific table

SELECT
    mutation_id,
    command,
    create_time,
    parts_to_do,
    parts_to_do_names,
    latest_fail_reason
FROM system.mutations
WHERE database = 'mydb' AND table = 'orders' AND is_done = 0;

Check mutations across all cluster replicas

SELECT
    hostName() AS host,
    database,
    table,
    mutation_id,
    parts_to_do,
    is_done,
    latest_fail_reason
FROM clusterAllReplicas('my_cluster', system.mutations)
WHERE is_done = 0;

Find stuck or failed mutations

SELECT
    database,
    table,
    mutation_id,
    command,
    latest_fail_reason,
    latest_fail_time
FROM system.mutations
WHERE is_done = 0 AND latest_fail_reason != '';

Check current mutation-related settings

-- Session-level settings
SELECT name, value, default
FROM system.settings
WHERE name LIKE '%mutation%' OR name LIKE '%lightweight_delete%';

-- MergeTree-level settings
SELECT name, value, default
FROM system.merge_tree_settings
WHERE name LIKE '%mutation%';

Detect lightweight deletes in specific parts

SELECT part_name, column_bytes_on_disk
FROM system.parts_columns
WHERE table = 'events' AND column = '_row_exists';

Parts without any _row_exists entry have no lightweight deletes applied.

Kill a Mutation

KILL MUTATION stops further processing of a mutation. Parts already rewritten keep their changes. Parts not yet processed are skipped. This is not a rollback — the operation is irreversible for completed parts.

-- Kill all pending mutations for a table
KILL MUTATION WHERE database = 'mydb' AND table = 'orders';

-- Kill a specific mutation by ID
KILL MUTATION WHERE database = 'mydb' AND table = 'orders' AND mutation_id = 'mutation_3.txt';

-- Preview without executing
KILL MUTATION WHERE database = 'mydb' AND table = 'orders' TEST;

The mutation_id format differs: replicated tables use a znode name (e.g., 0000000003); non-replicated tables use a filename (e.g., mutation_3.txt).

On ClickHouse Cloud, killed mutations show is_killed = 1 in system.mutations. This column is not present on self-hosted deployments.

Preventive Measures

• Design schemas around append-only inserts using ReplacingMergeTree, CollapsingMergeTree, or VersionedCollapsingMergeTree to minimize the need for explicit mutations.
• Use TTL expressions for data expiration rather than periodic DELETE mutations.
• Never issue mutations in application hot paths — mutations are not suitable as row-level update mechanisms.
• Batch mutations: one mutation affecting many rows is far cheaper than many mutations each affecting a few rows.
• Monitor system.mutations continuously and alert when parts_to_do is large or latest_fail_reason is non-empty.
• Tune number_of_free_entries_in_pool_to_execute_mutation and max_replicated_mutations_in_queue if mutations are stalling due to pool saturation.
• On ClickHouse 25.1+, consider apply_mutations_on_fly = 1 for workloads that issue mutations and then immediately query the affected data. This applies pending mutations in memory during SELECT execution before background materialization completes (must be explicitly enabled; not on by default).

Resolve Mutation Problems Automatically with Pulse

Pulse monitors system.mutations continuously across all nodes and replicas, alerting you when mutations are stuck, failing, or accumulating. Pulse surfaces mutation backlog, failed-part errors, and pool saturation in a single dashboard, so you can act before a mutation queue turns into a prolonged performance incident. Learn more at pulse.support.

Frequently Asked Questions

Q: Does ALTER TABLE UPDATE modify rows in place like PostgreSQL?
A: No. ClickHouse rewrites entire data parts. Even updating one row causes the entire part containing that row to be rewritten. This is fundamentally different from row-level update mechanisms in traditional RDBMS.

Q: What is the default behavior — does ALTER TABLE UPDATE block?
A: No. mutations_sync defaults to 0, meaning the statement returns immediately and the mutation runs in the background. Set mutations_sync = 1 or 2 to wait for completion.

Q: Is lightweight DELETE (DELETE FROM) the same as ALTER TABLE DELETE?
A: No. Lightweight DELETE marks rows with a hidden _row_exists column and provides immediate visibility of deletions, but does not rewrite parts immediately. ALTER TABLE DELETE is a heavy mutation that rewrites parts right away. Lightweight DELETE is generally preferred for targeted row removal on ClickHouse 23.3+.

Q: Does KILL MUTATION undo changes already applied?
A: No. KILL MUTATION only cancels processing for parts not yet mutated. Parts that have already been rewritten keep the mutation applied. There is no rollback. If you need to revert, you must restore from backup.

Q: Can I update columns that are part of the primary key?
A: No. Columns in the ORDER BY (sorting key) expression cannot be updated via ALTER TABLE UPDATE. This is a hard constraint and throws an error. If you need to change primary key values, you must delete and reinsert the rows.

Q: Why does parts_to_do = 0 not always mean the mutation is done?
A: For replicated tables, a mutation can show parts_to_do = 0 with is_done = 0 when a long-running INSERT is creating a new part that the mutation still needs to process. Wait until is_done = 1 before concluding the mutation has fully completed.

Related Reading

• ClickHouse ALTER TABLE Guide
• ClickHouse MergeTree Engine
• ClickHouse MergeTree Reference
• ClickHouse Aggressive Merges Tuning
• ClickHouse OPTIMIZE TABLE: FINAL Timeout
• ClickHouse Replication Queue Guide
• ClickHouse System Tables Disk Usage