37일차: ClickHouse ® 에서 업데이트 및 삭제를 처리하는 방법 (돌연변이)

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);

Enter fullscreen mode Exit fullscreen mode

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');

Enter fullscreen mode Exit fullscreen mode

1. Updating Data

Syntax

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

Enter fullscreen mode Exit fullscreen mode

Example 1: Update a Single Row

Update order 2 from pending to completed.

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

Enter fullscreen mode Exit fullscreen mode

Result:

order_id status 2 completed

Example 2: Update Multiple Columns

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

Enter fullscreen mode Exit fullscreen mode

Result:

order_id status amount 4 completed 500.00

Example 3: Bulk Update

Update all orders from India.

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

Enter fullscreen mode Exit fullscreen mode

Result:

order_id country status 1 IN reviewed 4 IN reviewed

2. Deleting Data Using Mutations

Syntax

ALTER TABLE table_name
DELETE WHERE condition;

Enter fullscreen mode Exit fullscreen mode

Example 1: Delete a Single Row

ALTER TABLE default.orders
DELETE WHERE order_id = 4;

Enter fullscreen mode Exit fullscreen mode

Remaining rows:

order_id status 1 completed 2 pending 3 completed 5 completed

Example 2: Delete Older Data

Delete orders before January 7, 2024.

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

Enter fullscreen mode Exit fullscreen mode

Remaining rows:

order_id order_date 3 2024-01-07 09:30:00 4 2024-01-08 14:00:00 5 2024-01-09 16:00:00

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;

Enter fullscreen mode Exit fullscreen mode

Example

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

Enter fullscreen mode Exit fullscreen mode

Mutation vs Lightweight Delete

Feature ALTER TABLE DELETE DELETE FROM Speed Slower Faster Data Rewrite Immediate background mutation During future merges Availability All versions v22.8+ Best Use Case Large bulk deletes Frequent row deletions

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';

Enter fullscreen mode Exit fullscreen mode

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;

Enter fullscreen mode Exit fullscreen mode

Compared to:

ALTER TABLE default.orders
DELETE WHERE 1=1;

Enter fullscreen mode Exit fullscreen mode

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;

Enter fullscreen mode Exit fullscreen mode

Example output:

mutation_id command is_done parts_to_do create_time 0000000001 UPDATE status=’completed’ 1 0 2024-01-10 10:05:00 0000000002 DELETE WHERE order_id=4 1 0 2024-01-10 10:06:00

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';

Enter fullscreen mode Exit fullscreen mode

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

Operation Command Update rows ALTER TABLE ... UPDATE Delete rows (mutation) ALTER TABLE ... DELETE Lightweight delete DELETE FROM ... Drop partition ALTER TABLE ... DROP PARTITION Delete all rows TRUNCATE TABLE Monitor mutations SELECT * FROM system.mutations Cancel mutation KILL MUTATION

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.