How to Handle Updates and Deletes in ClickHouse® (Mutations)

Introduction

ClickHouse® is designed for high-speed analytical queries and large-scale data ingestion. Since it is optimized for append-only workloads, updating or deleting existing records works differently than in traditional row-oriented databases.

Instead of modifying rows in place, ClickHouse® uses Mutations—background operations that rewrite entire data parts on disk.

In this blog, you'll learn:

• What mutations are
• How updates and deletes work
• Lightweight deletes
• Partition-level deletion
• Monitoring mutation progress
• Best practices for minimizing performance impact

What Are Mutations?

A mutation is a background operation that modifies or removes existing data by rewriting the affected data parts on disk.

Mutations are triggered using:

• ALTER TABLE ... UPDATE
• ALTER TABLE ... DELETE

Key Characteristics

• Asynchronous – The command returns immediately, while the actual work happens in the background.
• Part-based – ClickHouse rewrites entire data parts rather than individual rows.
• Resource intensive – Large mutations consume CPU, disk I/O, and storage bandwidth.
• Non-transactional – Mutations cannot be rolled back.
• Trackable – Progress can be monitored using the system.mutations table.

Because of these characteristics, mutations are best suited for:

• Occasional data corrections
• Compliance-related deletions
• Bulk updates

They should not be used for frequent row-level modifications.

Sample Table

We'll use the following orders table throughout this article.

CREATE TABLE default.orders
(
    order_id UInt32,
    customer_id UInt32,
    country LowCardinality(String),
    status LowCardinality(String),
    amount Float64,
    order_date DateTime
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(order_date)
ORDER BY (country, status, order_date);

Insert sample data:

INSERT INTO default.orders VALUES
(1,101,'IN','completed',1200.00,'2024-01-05 10:00:00'),
(2,102,'US','pending',450.00,'2024-01-06 11:00:00'),
(3,103,'GB','completed',890.00,'2024-01-07 09:30:00'),
(4,104,'IN','cancelled',300.00,'2024-01-08 14:00:00'),
(5,105,'DE','completed',670.00,'2024-01-09 16:00:00');

1. Updating Data

Syntax

ALTER TABLE table_name
UPDATE column1 = value1,
       column2 = value2
WHERE condition;

Example 1: Update a Single Row

Update order 2 from pending to completed.

ALTER TABLE default.orders
UPDATE status = 'completed'
WHERE order_id = 2;

Result:

Example 2: Update Multiple Columns

ALTER TABLE default.orders
UPDATE
    status = 'completed',
    amount = 500
WHERE order_id = 4;

Result:

Example 3: Bulk Update

Update all orders from India.

ALTER TABLE default.orders
UPDATE status = 'reviewed'
WHERE country = 'IN';

Result:

2. Deleting Data Using Mutations

Syntax

ALTER TABLE table_name
DELETE WHERE condition;

Example 1: Delete a Single Row

ALTER TABLE default.orders
DELETE WHERE order_id = 4;

Remaining rows:

Example 2: Delete Older Data

Delete orders before January 7, 2024.

ALTER TABLE default.orders
DELETE
WHERE order_date < '2024-01-07';

Remaining rows:

3. Lightweight Deletes (DELETE FROM)

Starting with ClickHouse v22.8, lightweight deletes provide a much faster way to remove rows.

Instead of rewriting data parts immediately, ClickHouse marks rows as deleted. Physical removal occurs during future merge operations.

Syntax

DELETE FROM table_name
WHERE condition;

Example

DELETE FROM default.orders
WHERE status = 'cancelled';

Mutation vs Lightweight Delete

4. DROP PARTITION (Fastest Bulk Delete)

For partitioned tables, deleting an entire partition is significantly faster than deleting rows individually.

ALTER TABLE default.orders
DROP PARTITION '202401';

Advantages:

• Instant operation
• No mutation
• Minimal resource usage
• Ideal for retention policies

This is the recommended approach for time-series data.

5. TRUNCATE TABLE

To remove all rows from a table:

TRUNCATE TABLE default.orders;

Compared to:

ALTER TABLE default.orders
DELETE WHERE 1=1;

TRUNCATE is much faster because it removes the entire table contents instead of generating a mutation.

Warning: This operation permanently deletes all data.

6. Monitoring Mutations

Since mutations execute asynchronously, ClickHouse provides the system.mutations table to monitor progress.

SELECT
    mutation_id,
    command,
    is_done,
    parts_to_do,
    create_time
FROM system.mutations
WHERE database = 'default'
  AND table = 'orders'
ORDER BY create_time DESC;

Example output:

Interpretation:

• is_done = 1 → Mutation completed.
• parts_to_do > 0 → Mutation is still running.

Cancelling a Mutation

If a mutation is taking too long or impacting cluster performance:

KILL MUTATION
WHERE database = 'default'
  AND table = 'orders'
  AND mutation_id = '0000000001';

Engine Alternatives

If your workload requires frequent updates or deletes, mutations may not be the best solution.

Instead, consider using:

• ReplacingMergeTree
• CollapsingMergeTree

These table engines are designed to efficiently handle changing data while maintaining ClickHouse's high-performance architecture.

Best Practices

• Avoid frequent mutations whenever possible.
• Use Lightweight Deletes (DELETE FROM) for small, frequent deletions.
• Use DROP PARTITION for deleting large amounts of time-based data.
• Always include a WHERE clause to avoid accidental data loss.
• Monitor long-running mutations using system.mutations.
• Schedule heavy mutations during off-peak hours.
• Design your schema to minimize the need for updates and deletes.

Quick Reference

Final Thoughts

Handling updates and deletes in ClickHouse® requires a different mindset than traditional relational databases.

Because ClickHouse is optimized for append-only analytical workloads, updates and deletes are intentionally expensive. Rather than modifying rows directly, ClickHouse rewrites data parts in the background through mutations.

Fortunately, ClickHouse offers multiple strategies depending on your use case:

• Mutations for occasional updates and deletions
• Lightweight Deletes for faster row-level removals
• DROP PARTITION for instant bulk deletion of partitioned data
• ReplacingMergeTree and CollapsingMergeTree for workloads with frequent data changes

The key to maintaining high performance is choosing the right deletion or update strategy and designing your schema to minimize the need for mutations from the outset.