TopolearnCorrections and GDPR deletes happen, and doing them the OLTP way can bring a cluster to its knees.
Parts are immutable, so changing rows means rewriting or masking data. ClickHouse offers a few tools.
Lightweight DELETE: DELETE FROM t WHERE ... marks rows as deleted in a hidden mask column. They vanish from query results right away and are physically removed during later merges. It is the right default for deletes.
Mutations: ALTER TABLE t UPDATE col = expr WHERE ... and ALTER TABLE t DELETE WHERE ... rewrite every part that contains matching rows, in the background. They are asynchronous by default: the statement returns at once and the work shows up in system.mutations. Add SETTINGS mutations_sync = 1 to wait. A mutation that touches a few rows in each of many parts still rewrites those parts, so it can be very expensive on big tables. You cannot update columns that are part of the sort key.
Patterns that avoid mutations altogether:
- Model changes as new rows and read the latest version with
argMaxor a ReplacingMergeTree (next level). - Drop whole partitions for retention instead of deleting by date.
- Batch corrections: one mutation for many rows, never one per row.
There is no multi-statement transactional UPDATE in the OLTP sense; treat ClickHouse data as append-mostly and design for it.
Example
CREATE TABLE users (id UInt64, email String, plan LowCardinality(String))
ENGINE = MergeTree ORDER BY id;
INSERT INTO users VALUES (1, 'a@x.io', 'free'), (2, 'b@x.io', 'pro'), (3, 'c@x.io', 'free');
-- Lightweight delete: rows disappear from results immediately
DELETE FROM users WHERE id = 3;
-- Mutation: rewrites affected parts in the background
ALTER TABLE users UPDATE plan = 'pro' WHERE id = 1 SETTINGS mutations_sync = 1;
SELECT * FROM users ORDER BY id;
-- 1 a@x.io pro
-- 2 b@x.io pro
SELECT command, is_done FROM system.mutations WHERE table = 'users';
-- (UPDATE _row_exists = 0 WHERE id = 3) 1 <- the DELETE only wrote a mask
-- (UPDATE plan = 'pro' WHERE id = 1) 1Remember
- DELETE FROM is a lightweight delete: masked now, purged on merge
- ALTER TABLE ... UPDATE/DELETE are async mutations that rewrite parts
- Prefer appending new versions or dropping partitions over frequent mutations
Watch out for
- Issuing one ALTER TABLE ... UPDATE per row from an application loop.