ClickHouse Updates

ClickHouse updates are not one operation but a ladder of five, and the rung chosen decides whether a change costs a few rows of insert, a mask over a part, or a rewrite of every part the predicate touches. The engine’s storage is immutable parts merged in the background, so “change this row” is always translated into “write something new and reconcile it later”; the five rungs differ in what is written and when the reconciliation happens. Choosing the cheapest rung that gives the required semantics is most of the skill.

This page sets out the ladder from cheapest to most expensive, with the semantics, the cost model, the SQL and the failure mode of each rung, then covers the schema side of ALTER, the bulk-change and redo patterns from the archive, and the other meaning of the phrase, version updates, which has its own rules. The MergeTree hub explains the merge loop that every rung depends on.

The archive under this category holds the ALTER statement deep dive, how to avoid mutations, bulk data changes, redo operations for reliability, long-integer query optimisation, the zero-downtime upgrade guide, the 26.8 LTS and 26.x feature posts, real-time analytics on 26.8, and the data-lake post.

Rung 1 of ClickHouse updates: insert a new version and let the merge reconcile

The cheapest ClickHouse update is not an update at all: on a ReplacingMergeTree keyed by the business identifier, a change is a new row with a higher version, and the merge keeps the newest. Cost is one insert; the old row lingers until a merge and reads use FINAL or argMax to see the current state. Deletes are the same with is_deleted = 1 (23.2 and later). This rung carries every CDC feed into ClickHouse and most application-level corrections, and the how to avoid mutations post is the archive’s case for reaching it first.

CREATE TABLE orders
(
    order_id   UInt64,
    status     LowCardinality(String),
    amount     Decimal(18, 2),
    version    UInt64,
    is_deleted UInt8 DEFAULT 0
)
ENGINE = ReplacingMergeTree(version, is_deleted)
ORDER BY order_id;

-- the "update": a new version
INSERT INTO orders VALUES (1001, 'shipped', 120.00, 7, 0);
-- the "delete": a tombstone row
INSERT INTO orders VALUES (1002, '', 0, 8, 1);

-- reads see the latest
SELECT * FROM orders FINAL WHERE order_id IN (1001, 1002);

Rung 2: lightweight DELETE, a mask instead of a rewrite

Lightweight DELETE FROM … WHERE (stable since 23.3) writes a hidden _row_exists mask column for the affected parts and applies it on every read; the rows vanish immediately from the reader’s view and the physical removal happens at the next merge of those parts. Cost is proportional to the number of parts touched, not the rows, and reads pay a small filter until the merge.

It is the ClickHouse updates rung for ad hoc deletes on plain MergeTree tables where a tombstone row is not available. Lightweight UPDATE has been arriving in the 25.x line; confirm its status on the running version before relying on it.

-- verify what will go before it goes
SELECT count() FROM events WHERE tenant_id = 7 AND event_date < '2025-01-01';

-- CONFIRMATION GATE: the count above matches the change ticket
DELETE FROM events WHERE tenant_id = 7 AND event_date < '2025-01-01';

-- the mask is visible until merged
SELECT name, rows, has_lightweight_delete FROM system.parts WHERE table = 'events' AND active AND has_lightweight_delete;

Rung 3: ALTER TABLE UPDATE and DELETE, the mutation behind most ClickHouse updates tickets

A mutation, the rung most people mean by ClickHouse updates, rewrites every part that the predicate could touch, applying the change column by column, as a background job tracked in system.mutations.

It is the rung for bulk corrections that must be physical (a column recomputed for a year of data, a GDPR erasure that must not leave a masked row), and it is expensive in exactly the way the storage model predicts: a mutation touching 40 parts of 10 GB each rewrites 400 GB, competes with merges for the same pool, and cannot be cancelled cleanly once it has started on a part.

The how to make bulk data changes post covers sizing and scheduling mutations; the rule is to scope the predicate to as few partitions as possible and run them one partition at a time off-peak.

-- scope: partitions first, then rows
SELECT partition, count() AS parts, formatReadableSize(sum(bytes_on_disk)) AS bytes
FROM system.parts WHERE table = 'events' AND active AND partition BETWEEN '202501' AND '202503'
GROUP BY partition;

-- one partition at a time, off-peak, behind a gate
ALTER TABLE events UPDATE amount = amount * 100 IN PARTITION '202501' WHERE currency = 'JPY';

-- watch it
SELECT mutation_id, command, parts_to_do, is_done, latest_fail_reason, create_time
FROM system.mutations WHERE table = 'events' AND NOT is_done;

Rung 4: partition swap, the ClickHouse updates rung with a one-statement rollback

When most of a partition changes, rewriting it wholesale is cheaper and safer than mutating it: build the corrected partition in a staging table with an INSERT … SELECT, verify it, and swap it in with REPLACE PARTITION, which is atomic and leaves the old parts for rollback until they are dropped. It is the ClickHouse updates rung for backfills, reprocessing after a bug in the pipeline, and schema-driven recomputation, and it is the only rung whose rollback is a single statement. The redo operations for data reliability post builds a repeatable reprocessing procedure on it.

CREATE TABLE events_fix AS events;
INSERT INTO events_fix SELECT ts, tenant_id, event_type, user_id, fixAmount(amount, currency) AS amount, currency
FROM events WHERE toYYYYMM(ts) = 202501;

-- verify: same row count, checksums on the untouched columns
SELECT (SELECT count() FROM events WHERE toYYYYMM(ts) = 202501) AS before,
       (SELECT count() FROM events_fix) AS after;

-- CONFIRMATION GATE: counts equal, spot checks recorded
ALTER TABLE events REPLACE PARTITION '202501' FROM events_fix;
-- rollback until the staging table is dropped: ALTER TABLE events REPLACE PARTITION '202501' FROM events_backup

Rung 5: rebuild and EXCHANGE, when the table itself changes

When the change is the sort key, the partition expression, the engine, or a type that cannot be converted in place, the table is rebuilt: create the new definition, load it from the old one (in partition-sized batches, dual-writing new data during the load), verify, and EXCHANGE TABLES atomically. It is the most expensive rung, measured in hours to days for large tables, and the one that the engineering hub‘s design reviews exist to avoid. The long-integer query optimisation post is an example of a type change that justified it.

CREATE TABLE events_v2 (...) ENGINE = ReplicatedMergeTree(...) PARTITION BY toYYYYMM(ts) ORDER BY (tenant_id, ts);

INSERT INTO events_v2 SELECT * FROM events WHERE toYYYYMM(ts) = 202501;   -- one partition per batch
-- ... every partition, then a final catch-up for rows written during the load ...

-- verify per partition on both sides, then swap atomically
EXCHANGE TABLES events AND events_v2;
-- events_v2 now holds the old table: keep it until the verification window closes, then drop behind a gate

The schema side of ALTER: cheap and expensive columns

ALTER TABLE also changes structure, the schema side of ClickHouse updates, and the same immutable-part logic decides the cost. ADD COLUMN with a default is metadata-only and instant, because missing columns are materialised on read until a merge writes them; DROP COLUMN is a mutation that removes a file per part but touches nothing else; MODIFY COLUMN to a compatible type is cheap while a conversion that changes the on-disk representation rewrites the column in every part; RENAME COLUMN is metadata; and anything touching the sort key or partition expression is rung 5.

The deep dive into the ALTER statement post covers every form with its cost; the ON CLUSTER form runs the same DDL on every replica through the distributed DDL queue, which is monitored like any other queue.

ALTER TABLE events ON CLUSTER 'ch_prod' ADD COLUMN campaign LowCardinality(String) DEFAULT '';   -- instant
ALTER TABLE events ON CLUSTER 'ch_prod' MODIFY COLUMN amount Decimal(18, 4);                    -- rewrite per part
ALTER TABLE events ON CLUSTER 'ch_prod' DROP COLUMN legacy_flag;                                 -- mutation, one file per part

-- the DDL queue that ON CLUSTER runs through
SELECT entry, host, status, exception_code, query_create_time FROM system.distributed_ddl_queue
WHERE status != 'Finished' ORDER BY query_create_time DESC LIMIT 20;

Choosing the rung for ClickHouse updates: three questions

Does the change need to be physical now, or is a masked or versioned row acceptable until the next merge? If the latter, rungs 1 and 2. Does the change touch a minority of rows in the affected partitions, or most of them? A minority is rung 3; a majority is rung 4. Does the change alter how rows are laid out? Then rung 5, and nothing cheaper will do. The performance pitfalls catalogue lists the usual wrong answer, which is a mutation over a whole table for a change that a versioned insert would have carried.

Mutations under load: the operational rules for ClickHouse updates

Rungs 3 and 5 compete with merges for the background pool, hold parts busy, and on replicated tables run on every replica through the replication queue, so an unbounded mutation on a busy cluster shows up as insert delays and replica lag before it shows up as a finished job. The rules that keep them safe: one partition per mutation, off-peak scheduling, a verification query before and a validation query after, KILL MUTATION behind a confirmation gate when a job is failing on every attempt, and never more than a handful in flight. The replication hub covers the queue side.

-- in flight, and their effect on the pool
SELECT database, table, mutation_id, parts_to_do, is_done, latest_fail_reason FROM system.mutations WHERE NOT is_done;
SELECT count() AS running_merges, countIf(is_mutation) AS of_which_mutations FROM system.merges;

-- a failing mutation: verify, gate, kill, verify
-- CONFIRMATION GATE: recorded in the change ticket; the change will be re-applied by a corrected statement
-- KILL MUTATION WHERE table = 'events' AND mutation_id = '${MUTATION_ID}';

The other meaning: ClickHouse updates of the server itself

A version update follows a different ladder: read the changelog’s backward-incompatible section against the settings and features in use, pin compatibility to the current version, upgrade one replica at a time with SYSTEM SYNC REPLICA between, compare the top query shapes against the baseline, and lift the pin one setting at a time weeks later. The zero-downtime upgrade guide post is the six-step procedure; the 26.8 LTS changes, 26.x in production and real-time analytics on 26.8 posts cover what the current LTS brings, and the release notes hub keeps the timeline.

-- before the binary moves: the inventory the changelog is read against
SELECT version();
SELECT name, value FROM system.settings WHERE changed;
SELECT name, value FROM system.merge_tree_settings WHERE changed;
SELECT name FROM system.settings WHERE name LIKE 'allow_experimental_%' AND value = '1';

-- during: behaviour pinned
SET compatibility = '25.8';   -- in the default profile, until the lift phase

Verification before and validation after, on every rung

Every rung above changes production data, so each runs inside the same frame: a verification query that records what will change (row count, a checksum over the affected columns, the partitions touched), a confirmation gate that compares it with the change ticket, the change itself, and a validation query that proves the result.

For rung 1 the validation is a FINAL read of the affected keys; for rung 2 a count of masked parts; for rung 3 the is_done flag and a recount; for rungs 4 and 5 counts and checksums per partition on both sides before the swap. The queries are written before the change, not after, and the outputs are attached to the ticket.

-- the frame, for a rung-3 change on one partition
-- 1. verify
SELECT count() AS rows, sum(cityHash64(order_id, amount)) AS checksum
FROM events WHERE toYYYYMM(ts) = 202501 AND currency = 'JPY';
-- 2. gate: rows and checksum recorded in the ticket
-- 3. change
ALTER TABLE events UPDATE amount = amount * 100 IN PARTITION '202501' WHERE currency = 'JPY';
-- 4. validate, once system.mutations shows is_done
SELECT count() AS rows, min(amount), max(amount)
FROM events WHERE toYYYYMM(ts) = 202501 AND currency = 'JPY';

Also filed here

The data lake for an open-source ClickHouse stack post sits in this category because the lakehouse pattern changes what an update means: with Iceberg writes production-ready since 26.x, some corrections are made in the lake and re-read rather than mutated in ClickHouse. The archival store hub covers that design.

Two habits make the frame cheap to keep: the verification and validation queries live in the runbook template, not in someone’s head, and the change ticket is not closed until the validation output is attached. On a replicated cluster the validation runs on a replica other than the one the change was issued on, which also proves that the replication queue carried it.

The ladder is also a capacity question. An estate whose corrections arrive as versioned inserts spends its background pool on ordinary merges; one that reaches for mutations by habit spends it on rewrites, and the difference shows in the merge budget from the engineering hub‘s third review long before it shows in a ticket.

Version notes

Lightweight DELETE has been stable since 23.3; is_deleted on ReplacingMergeTree since 23.2; EXCHANGE TABLES since 21.x on Atomic databases; REPLACE PARTITION since 19.x. Lightweight UPDATE is a 25.x feature whose production status must be confirmed on the running version. The ALTER statement reference is the source for the forms above.

Reading the archive

The archive spans 22.x to 26.x, so the lightweight rungs are newer than the oldest posts, which predate them and reach for mutations more readily than a current design would.

Read the ALTER deep dive for the forms of ClickHouse updates, then how to avoid mutations and bulk data changes for rungs 1 to 3, redo operations for rung 4, and the long-integer post for a rung-5 example. The upgrade guide and the three 26.x posts cover version updates; the data-lake post covers the case where the update happens outside ClickHouse.

ChistaDATA reviews every bulk change and every version update as a runbook under 24×7 support, with the verification and validation queries written before the change and a confirmation gate on every destructive statement, and runs the rung-5 rebuilds as ClickHouse consulting engagements. Every rung on this page is tested on staging with production-shaped data first, and none runs in production without a tested backup of the affected table.