ClickHouse — A Columnar Analytics Database from the Inside

Rewriting parts, masking deletes, and lifetimes applied at merge

Goal

On immutable parts, you confirm with system.mutations, system.part_log and system.parts what UPDATE, DELETE and TTL actually do, and you find a stuck mutation and end it.

Why it matters

In ClickHouse, one line of UPDATE rewrites a whole part, a lightweight DELETE only hides rows, and TTL waits until a merge comes. If you do not know these differences, a small correction becomes a disk I/O storm, rows you believed deleted remain on disk, and one failed mutation blocks every modification to the table. This lab's grader does not trust the numbers you wrote — it recomputes the rows that should remain from the original generating expression, also counts with the mask turned off (apply_deleted_mask = 0), and compares against the records in system.part_log.

Steps

Create the database mut and the table mut.events — columns event_date Date, ts DateTime, user_id UInt32, email String, amount UInt32, status LowCardinality(String), legal_hold UInt8 (in this order), MergeTree, ORDER BY (user_id, ts). Insert 400,000 rows once with /opt/lab/fixtures/mutation/events.sql and make it one part with OPTIMIZE TABLE mut.events FINAL.
Run ALTER TABLE mut.events UPDATE status = 'refunded' WHERE status = 'refund_req' with mutations_sync = 2, and write its mutation_id, the active part names before and after (part_before and part_after), and the number of rows that MutatePart wrote in system.part_log (rows_rewritten) to /root/ch/mutation/mutation.json.
Delete the rows of user_id = 777 with ALTER TABLE ... DELETE (mutations_sync = 2).
Delete the rows of user_id = 888 with a lightweight DELETE (DELETE FROM). Right away, write that user's rows counted with an ordinary SELECT (visible_rows), the rows counted with SETTINGS apply_deleted_mask = 0 (masked_rows), and the active part rows in system.parts (part_rows) to /root/ch/mutation/lwd.json, and then run OPTIMIZE TABLE mut.events FINAL.
Attach TTL if(legal_hold = 1, toDate('2100-01-01'), event_date + INTERVAL 90 DAY) to the email column (MODIFY COLUMN email String TTL ...), and apply it to the existing parts.
Attach the table TTL event_date + INTERVAL 180 DAY DELETE WHERE status = 'test' (MODIFY TTL) and apply it to the existing parts.
Create the table mut.hits (ts DateTime, user_id UInt32, hits UInt32, max_hits UInt32 DEFAULT hits, sum_hits UInt64 DEFAULT hits) as MergeTree, PRIMARY KEY (user_id, toStartOfDay(ts), ts), TTL ts + INTERVAL 30 DAY GROUP BY user_id, toStartOfDay(ts) SET max_hits = max(max_hits), sum_hits = sum(sum_hits), insert /opt/lab/fixtures/mutation/hits.sql once, and then apply the summary with OPTIMIZE TABLE mut.hits FINAL.
Submit ALTER TABLE mut.events UPDATE amount = toUInt32(email) WHERE legal_hold = 1 (without waiting), and when the failure is recorded, write that mutation's mutation_id, is_done, parts_to_do and latest_fail_error_code_name (key error_code_name) to /root/ch/mutation/stuck.json, and then end it with KILL MUTATION.

Notes

Checking mutations: SELECT mutation_id, command, is_done, parts_to_do, latest_fail_reason FROM system.mutations WHERE database = 'mut'.
How parts were rewritten: after SYSTEM FLUSH LOGS, SELECT event_type, part_name, merged_from, rows FROM system.part_log WHERE database = 'mut' ORDER BY event_time_microseconds.
TTL is applied at merge time. To apply it now, ALTER TABLE ... MATERIALIZE TTL SETTINGS mutations_sync = 2 or OPTIMIZE TABLE ... FINAL.
Common mistakes: running OPTIMIZE first in step 4 and missing the masked state, leaving out the WHERE in step 6 and deleting every row older than 180 days (= all of them), and forgetting KILL in step 8 so that all later mutations are blocked.
The fixtures are 2024 data. If you break something, the fastest way is DROP DATABASE mut and start again from step 1.
Official docs: Avoid mutations · ALTER TABLE ... UPDATE · ALTER TABLE ... DELETE · Lightweight delete · Manage data with TTL · ALTER TABLE ... MODIFY TTL · system.mutations · system.part_log

An event table with one part

Create the database mut and the table mut.events. The columns are, in order, event_date Date, ts DateTime, user_id UInt32, email String, amount UInt32, status LowCardinality(String), legal_hold UInt8, it is MergeTree, and ORDER BY (user_id, ts). Insert 400,000 rows once with /opt/lab/fixtures/mutation/events.sql and make it one part with OPTIMIZE TABLE mut.events FINAL.

If you make it one part, you can follow in one line how the part name changes with each later mutation. The data is all from 2024, so the TTL you attach later deletes the same rows no matter when you grade.

Writing a whole part to change 2%

Run ALTER TABLE mut.events UPDATE status = 'refunded' WHERE status = 'refund_req' SETTINGS mutations_sync = 2. Write that mutation_id from system.mutations, the active part names before and after the run, part_before and part_after, and the rows of the MutatePart record that created part_after in system.part_log as rows_rewritten, to /root/ch/mutation/mutation.json.

A part cannot be fixed, so a mutation writes the new part whole. The number at the end of the new part name is the mutation number. part_log is flushed every second, so look after SYSTEM FLUSH LOGS, and check whether merged_from points to the original part. Compare the number of rows changed with the number of rows rewritten.

A heavy delete — ALTER TABLE ... DELETE

Delete the rows of user_id = 777 with ALTER TABLE mut.events DELETE WHERE user_id = 777 SETTINGS mutations_sync = 2.

ALTER DELETE is also a mutation. It rewrites the parts holding those rows with the rows left out. When it finishes, those rows must not be visible even in a query with the mask turned off (SETTINGS apply_deleted_mask = 0).

A lightweight DELETE only hides

Run DELETE FROM mut.events WHERE user_id = 888, and right away write that user's rows counted with an ordinary SELECT as visible_rows, the rows counted with SETTINGS apply_deleted_mask = 0 as masked_rows, and the active part rows in system.parts as part_rows to /root/ch/mutation/lwd.json. Then run OPTIMIZE TABLE mut.events FINAL.

A lightweight DELETE turns into a mutation that writes 0 to the hidden column _row_exists (look at the command in system.mutations). SELECT looks at that mark and hides the rows, but the rows are still in the part, so rows does not decrease. They are actually removed at merge time.

Column TTL — only the expired values are erased

Run ALTER TABLE mut.events MODIFY COLUMN email String TTL if(legal_hold = 1, toDate('2100-01-01'), event_date + INTERVAL 90 DAY) and apply it to the existing parts too (MATERIALIZE TTL, mutations_sync = 2). The email of legal-hold (legal_hold = 1) rows must remain.

A column TTL changes the expired value to that type's default (an empty string for String). You make the exception by returning a far-future date for the hold rows. See in system.mutations whether changing the TTL creates a MATERIALIZE TTL mutation along with it by default.

Row TTL — only rows matching the condition are deleted

Run ALTER TABLE mut.events MODIFY TTL event_date + INTERVAL 180 DAY DELETE WHERE status = 'test' and apply it to the existing parts too. Not a single row that is not test may disappear.

The data is all from 2024, so 180 days have passed. Without the WHERE, every row is past its deadline and the table empties entirely. TTL is applied at merge time, so apply it now with MATERIALIZE TTL or OPTIMIZE FINAL.

GROUP BY TTL — old rows into a summary

Create the table mut.hits (ts DateTime, user_id UInt32, hits UInt32, max_hits UInt32 DEFAULT hits, sum_hits UInt64 DEFAULT hits) as MergeTree, PRIMARY KEY (user_id, toStartOfDay(ts), ts), TTL ts + INTERVAL 30 DAY GROUP BY user_id, toStartOfDay(ts) SET max_hits = max(max_hits), sum_hits = sum(sum_hits), insert /opt/lab/fixtures/mutation/hits.sql once, and then run OPTIMIZE TABLE mut.hits FINAL.

The columns used in the GROUP BY must be a prefix of the primary key, which is why toStartOfDay(ts) is in the PRIMARY KEY. You must give max_hits and sum_hits the default hits so that before summarization a row starts with its own value. Compare the row count right after the INSERT and after OPTIMIZE.

Find the stuck mutation and end it

Submit ALTER TABLE mut.events UPDATE amount = toUInt32(email) WHERE legal_hold = 1 (without mutations_sync). When a failure is recorded in system.mutations, write that mutation's mutation_id, is_done, parts_to_do and latest_fail_error_code_name (key name error_code_name) to /root/ch/mutation/stuck.json, and end it with KILL MUTATION.

An email string cannot be read as a number, so the mutation fails on every part and keeps retrying. There is no undo (rollback), and every mutation that comes after is blocked by this one. Wait a few seconds until latest_fail_reason is filled in before recording, and end it with KILL MUTATION WHERE database = 'mut' AND mutation_id = '…'.