Updating and Deleting Data in ClickHouse

ClickHouse is built for data that is written once and read many times, so it does not support row-level UPDATE and DELETE the way an OLTP database like PostgreSQL or MySQL does. There is no transaction that flips a few bytes in place. Instead, modifying existing data means rewriting the immutable parts that contain it, and ClickHouse gives you several mechanisms with very different cost profiles. Picking the wrong one is the difference between a query that finishes in milliseconds and a background operation that rewrites terabytes.

This guide covers the four options (heavyweight mutations, lightweight deletes, lightweight updates, and avoiding mutations entirely through engine choice) and how to tell which one a given workload needs.

Why There Is No In-Place Update

ClickHouse stores table data in immutable parts, sorted by the table's ORDER BY key. "Immutable" is the key word: a part is never edited after it is written. So changing even a single row means reading the parts that contain matching rows, producing new parts with the change applied, and discarding the old parts. That is inherently expensive, and it is why ClickHouse treats updates and deletes as background operations called mutations rather than instant statements.

If you find yourself wanting frequent row-level updates, that is usually a signal to reconsider the table design (for example, a ReplacingMergeTree engine) rather than to lean on mutations.

Heavyweight Mutations: ALTER TABLE UPDATE and DELETE

The original mechanism is the mutation, issued through ALTER TABLE:

-- Update
ALTER TABLE events
UPDATE value = value * 1.1
WHERE event_type = 'purchase';
 
-- Delete
ALTER TABLE events
DELETE WHERE event_date < '2025-01-01';

When you run one of these, ClickHouse identifies every part containing rows that match the WHERE clause and rewrites each of those parts in full, applying the change. A DELETE WHERE event_date < '2025-01-01' does not just drop the matching rows; it rewrites the entire part each matching row lived in. If your predicate touches rows scattered across many parts, the mutation rewrites all of them.

Mutations are asynchronous by default. The ALTER statement returns almost immediately, and the actual work happens in the background. That means the change is not necessarily visible to subsequent SELECT queries right away. To wait for completion, set mutations_sync:

SET mutations_sync = 1;  -- wait for the current replica
SET mutations_sync = 2;  -- wait for all replicas

You monitor in-flight and completed mutations through the system.mutations table:

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

is_done = 0 means the mutation is still running. If a mutation is stuck or wrong, you can cancel it:

KILL MUTATION WHERE mutation_id = '0000000001';

Because each mutation rewrites whole parts, running many small mutations is far more expensive than batching changes into one. Avoid per-row updates in a loop; combine them into a single WHERE predicate where possible.

Lightweight Deletes

The DELETE FROM statement (available for the MergeTree family) is the lightweight alternative to ALTER TABLE ... DELETE:

DELETE FROM events WHERE event_type = 'test';

Despite the name, a lightweight delete is still implemented as a mutation under the hood, but a cheaper one. Instead of immediately rewriting parts, it marks matching rows as deleted using an internal _row_exists mask. Subsequent queries automatically filter out the masked rows, so the data disappears from results right away even though it is still physically on disk. The physical removal happens later, during a normal background merge.

This makes lightweight deletes much faster to issue than a heavyweight ALTER TABLE ... DELETE, and it is the right default for most ad-hoc deletes. The trade-offs to know:

• The masked rows still occupy disk until merges reclaim the space, so a lightweight delete does not free storage instantly.
• Because masked rows are filtered at read time, queries carry a small overhead until merges physically remove the rows.
• It only works on *MergeTree engines.

For deleting a whole time range that aligns with your partition key, neither delete type is the best tool. Use ALTER TABLE ... DROP PARTITION instead, which removes the data directory outright with almost no cost. See the partitioning guide for how to align partitions with your deletion patterns, and TTL for automating age-based deletion without issuing any delete at all.

Lightweight Updates

More recent ClickHouse versions add lightweight updates via the UPDATE statement, mirroring lightweight deletes:

UPDATE events SET value = 0 WHERE event_type = 'refund';

Rather than rewriting parts up front, a lightweight update records the change in a patch and applies it on the fly when rows are read, with the physical rewrite deferred to a later merge. This makes the UPDATE return quickly and makes the new value visible to reads without waiting for a full part rewrite. As with lightweight deletes, the underlying storage is reconciled during background merges. Lightweight updates are newer than the other mechanisms, so confirm support and behavior in your specific server version against the official docs before relying on them in production (as of mid-2026 the feature is still maturing across releases).

Avoiding Mutations Entirely

Often the best update or delete is the one you never issue. ClickHouse offers engine-level patterns that handle changing data as part of normal merges:

• ReplacingMergeTree keeps one row per sorting key, so writing a new version of a row supersedes the old one on merge. This covers most "upsert" needs. Reads use FINAL or an argMax aggregation to see the latest version before merges run.
• CollapsingMergeTree / VersionedCollapsingMergeTree cancel out old rows using a sign column, suited to high-throughput mutable data.
• TTL expires rows automatically based on a time expression, replacing scheduled delete jobs.

These are covered in the MergeTree engines guide. The pattern is the same in each case: instead of editing existing data, you write new data and let the merge process resolve it, which is what ClickHouse is optimized for.

Choosing an Approach

The mental model that keeps you out of trouble: every modification in ClickHouse ultimately rewrites immutable parts, so the cheapest operation is the one that touches the fewest parts. Dropping a partition touches none of the surviving data. Lightweight deletes defer the rewrite. Heavyweight mutations rewrite everything matching immediately. And the engine-based patterns avoid the explicit operation altogether by folding the change into merges you are already paying for.

When you are inspecting system.mutations to see whether a mutation has finished or diagnosing why one is stuck, Mako's AI autocomplete can help you draft the monitoring queries.

Related Guides

• ClickHouse MergeTree Table Engines for the engine-based alternatives to mutations.
• ClickHouse Partitioning for dropping whole partitions instead of deleting rows.
• ClickHouse TTL for automating age-based deletion.

Mako connects to ClickHouse with AI-powered autocomplete for writing and exploring analytical queries. Try it free at mako.ai.