ClickHouse Migrations That Wait for Mutations: What Changed in ch-migrate 0.7
2026-10-27 - 11 min read
In ClickHouse migrations, an ALTER ... UPDATE returns before the rewrite it starts has finished. ch-migrate 0.7 waits for that work, shows its progress, and records a migration only when ClickHouse is done.
ClickHouse migrations that change data don't finish when their statement does. An ALTER TABLE ... UPDATE creates a mutation, a background job that rewrites every affected part of the table, and ClickHouse acknowledges the ALTER as soon as the job is queued. The rewrite runs afterwards, for milliseconds on a test table or hours on a large one, and it can fail long after the client has moved on.
Here's a backfill that fills a new amount_cents column from a text amount column on a million orders. One order's amount is n/a.
-- ch-migrate: allow-non-idempotent Derived from amount, so a retry writes the same values
ALTER TABLE {db}.orders
UPDATE amount_cents = toUInt64(amount) * 100
WHERE 1;I ran it with ch-migrate 0.6.0 from PyPI against a local ClickHouse 26.3 server:
→ Applying a281be09 backfill_amount_cents
✓ Applied 1 migration; dev is at head.It reported success in under a second and recorded the migration, and ch-migrate status said the database was at head. ClickHouse disagreed:
mutation_id: mutation_2.txt
command: (UPDATE amount_cents = toUInt64(amount) * 100 WHERE 1)
is_done: 0
parts_to_do: 1
latest_fail_reason: Code: 6. DB::Exception: Cannot parse string 'n/a' as UInt64: syntax error at begin of string. …The mutation failed on the first part it tried and never finished. ClickHouse keeps retrying a failed mutation, and every later mutation on the same table waits behind it, so the next migration that touches orders would stall too. The version table, meanwhile, said the backfill was applied.
ch-migrate is my open-source ClickHouse migration tool, and 0.7 closes that gap: up doesn't record a migration until the work it started has finished. (New to the tool? Start with ClickHouse migrations with Alembic, then what changed in 0.6.)
What's new in 0.7
upwaits for mutations, and forON CLUSTERDDL on every host, before it records a migration.- A rerun waits for unfinished work, then runs the migration again, or stops first if a statement may already have applied.
--timeoutlimits the wait. - The idempotency gate now reads SQL sent through
text()andop.get_bind(), and asks for a waiver on SQL it can't read. - A failed mutation stops the migration with ClickHouse's reason and the exact
KILL MUTATIONstatement. - Progress while it waits: a live bar in a terminal, plain lines in CI.
- Coming from 0.6? See upgrading from 0.6.
Building it in public
ch-migrate is MIT-licensed and built in the open, one release at a time, each with a post and a short demo. I want it to be useful to any team running ClickHouse in more than one environment, on ClickHouse Cloud or self-hosted, not only the teams I work with.
I still haven't settled whether the CLI is for a person at a terminal or for a coding agent running the same migration. 0.7 is the first release where that question shaped the output: a terminal gets a live progress bar, and anything reading a pipe, whether a CI log or an agent, gets plain lines it can parse. If you're pointing an agent at your ClickHouse schema, I'd like to hear what it needs. Bug reports and ideas are welcome on GitHub.
0.7 also changed shape before release. An earlier build recorded which mutation each statement started, so that a rerun could wait on it without sending the statement again. It worked, but it was most of the release's code, and once a mutation had been killed, it couldn't recover without someone editing its records by hand. The release relies on 0.6's idempotency gate instead, plus a note of which migration was interrupted. That keeps recovery to fixing the cause and running up again, naming the migration if it has a waived statement.
ClickHouse migrations that wait for mutations
In 0.7, after each statement that can write, up polls system.mutations until ClickHouse reports no unfinished mutation in the project database, on any replica that has to apply it. Only then does it move to the next statement, and only after the last one does it record the migration. Zero parts left doesn't count as done on its own; the mutation has to be marked finished.
ClickHouse can do some of this waiting itself: mutations_sync = 2 makes the ALTER block until every replica is done. That holds one HTTP request open for the whole rewrite, though, and if the connection drops halfway through a two-hour backfill, the client loses the outcome while the server carries on. up sends the statement asynchronously and polls on the same session, so a SET earlier in the migration still applies and the session doesn't time out while it waits.
up doesn't try to work out which mutation came from which statement. Any unfinished mutation in the database holds the migration, including one another writer started, and before the first migration runs, up waits for any that were already queued. If one of those has failed, the run stops and names it. That's deliberately conservative. On the same table it costs nothing extra, because ClickHouse runs a table's mutations in order and yours would queue behind theirs anyway. On another table, it means a migration can wait for work it didn't start.
A statement that only changes metadata, such as adding a column, starts no mutation, so the check after it finds nothing and up moves on. UPDATE and DELETE rewrite parts in the background, and up waits for them. A MODIFY COLUMN that changes a column's type also rewrites parts, but ClickHouse waits for that one before it answers, so 0.6 already reported a failed conversion.
A rerun waits, then runs the migration again
Waiting creates a new question: what happens when up stops before ClickHouse does? A CI job hits its time limit, someone presses Ctrl-C, or the connection drops. In every case ClickHouse keeps doing the work, and up exits without recording the migration.
The next up first waits for that unfinished work, then runs the migration again from its first statement. Sending a statement twice is safe only if the second run gives the same result, and that's what 0.6's idempotency gate checks. up notes each migration when it starts and clears the note once the version is recorded, so it knows this one was interrupted:
- If every statement passes the gate as written,
upruns the migration again on its own. - If a statement carries a waiver, as every
UPDATE,DELETEandINSERT ... SELECTmust, it may already have applied.upstops before sending anything, names the statement, and waits for you to check what the first run changed and say so with--rerun-interrupted.
The backfills in this post derive each value from the row itself, so a second run writes the same values, but only you can confirm that. For the same reason, 0.7's gate now reads SQL sent through text(), op.get_bind() and exec_driver_sql, and SQL built at run time needs a waiver: a statement the gate can't read is one ch-migrate can't vouch for.
--timeout SECONDS gives the whole run a waiting budget. When it runs out, up exits 1 and names what's still pending:
✗ Timed out after 8.0s waiting on fd325ad6 backfill_digest (migrations/sql/history/tables/events/2026_10_27_1447_fd325ad69e49_backfill_digest.up.sql:3)
Pending: demo_dev.events mutation mutation_148.txt host e07f0be40810; parts_to_do=28
ClickHouse keeps doing the work, and the migration is not recorded as applied. Run ch-migrate up dev again: it waits for that work to finish, then runs the migration again, or stops first if a waived statement may already have applied.The backfill carries a waiver, so the next up waits out the first run's mutation, then stops:
✓ demo_dev.events mutation_148.txt · 7 parts · 185.0 MiB · 0:03
✗ fd325ad6 was interrupted on an earlier run, and ch-migrate can't vouch for running it again
migrations/sql/history/tables/events/2026_10_27_1447_fd325ad69e49_backfill_digest.up.sql:3 waiver: Each digest comes from the row itself, so a retry writes the same values: ALTER TABLE {db}.events
Some of its statements may already have applied. Check what the earlier run changed, then run ch-migrate up dev --rerun-interrupted fd325ad6.There's no default timeout. A wait ends when ClickHouse finishes the work, when the mutation fails, or when you stop it.
Here's a backfill over 40 partitions of a 120-million-row table, slowed to half speed. The demo server runs mutations on one background thread, so the parts finish one at a time and the bar has time to move. --timeout 8 stops the first wait partway, and system.mutations shows ClickHouse still working. The second up waits for that mutation, then stops, because the backfill is waived. Naming the revision sends the backfill again, and the second mutation only checks each part, finds nothing left to fill, and finishes sooner.
Running twice isn't free, though. In the demo, the repeated backfill changed nothing but still took 11 seconds, because ClickHouse reads the WHERE column of every part to find that out. On a table whose first pass takes hours, plan for the repeat to take a while too.
Where you can, write a backfill so that repeating it finds nothing to do. The demo's backfill ends in WHERE digest = '', so a second run skips the rows the first one filled. With WHERE 1, a second run computes every digest again.
When a mutation fails
A mutation that fails doesn't end by itself. ClickHouse records the error in latest_fail_reason and retries the mutation, and every later mutation on that table waits behind it. 0.7 reads latest_fail_reason, fails the migration and doesn't record it. Here's the orders backfill from the top of this post on 0.7:
→ Applying ec78e2cd backfill_amount_cents
waiting on mutation_2.txt (shop_dev.orders): 0/1 parts
✗ ec78e2cd backfill_amount_cents (migrations/sql/history/tables/orders/2026_10_27_1340_ec78e2cd4bd2_backfill_amount_cents.up.sql:2) failed: mutation mutation_2.txt on shop_dev.orders failed on 48e36828c2b8
Code: 6. DB::Exception: Cannot parse string 'n/a' as UInt64: syntax error at begin of string. Note: there are toUInt64OrZero and toUInt64OrNull functions, which returns zero/NULL instead of throwing exception: while executing 'FUNCTION toUInt64(amount :: 0) -> toUInt64(amount) UInt64 : 3'. (CANNOT_PARSE_TEXT)
It was not recorded as applied. ClickHouse keeps retrying a failed mutation, and later mutations on shop_dev.orders wait behind it.
If the cause is temporary, such as memory or disk, fix it and run ch-migrate up dev to wait for the retry.
If the statement itself is wrong, an operator stops it on 48e36828c2b8 with KILL MUTATION WHERE database = 'shop_dev' AND table = 'orders' AND mutation_id = 'mutation_2.txt', then fixes the SQL and runs ch-migrate up dev again.Side by side, the same backfill on each version:
| 0.6.0 | 0.7.0 | |
|---|---|---|
What up printed | ✓ Applied 1 migration; dev is at head. | ✗ … mutation mutation_2.txt on shop_dev.orders failed … |
| Exit code | 0 | 1 |
Recorded in alembic_version | Yes | No |
ch-migrate status | At head | Behind by 1 |
The mutation in system.mutations | Not done, failing on 'n/a' | The same |
ch-migrate never kills a mutation itself. It prints the statement, scoped to the one mutation, and names the host it failed on, for an operator to run.
What comes next depends on why it failed. If the cause was temporary, such as a full disk or a memory limit, fixing it lets ClickHouse's retry succeed, and running up again waits for that. If the statement itself is wrong, as here, no fix to the data can help, because any fix is another mutation and would queue behind the failed one. The mutation has to be killed.
Killing a mutation stops it partway: parts it already rewrote keep the new values.
So after a kill, fix the statement so it's right whatever the first attempt left behind, then run up again. The backfill carries a waiver, so up first stops and asks you to name it with --rerun-interrupted, which is the moment to check what the killed mutation changed. Here the fix is toUInt64OrZero(amount), which derives every row's value from amount again, so any rows the failed mutation rewrote end up the same as the rest. A statement that ClickHouse rejects outright, such as a MODIFY COLUMN on a sorting-key column, works the same way: fix the file and run up again.
Here's the failure, the kill and the fix, at half speed:
ON CLUSTER DDL waits for every host
On a self-hosted cluster, an ALTER ... ON CLUSTER goes into ClickHouse's distributed DDL queue, and each host runs it when it gets to it. The client waits up to distributed_ddl_task_timeout (180 seconds by default) for every host, then gives up with a timeout error, while hosts that haven't run the statement yet keep it queued.
0.6 left that wait to ClickHouse's defaults. 0.7 sets it for every statement it sends:
- A host that fails fails the migration, with the host, ClickHouse's error code and its message.
upsetsdistributed_ddl_output_mode = 'throw'itself, so a settings profile can't switch the check off. - A host that doesn't finish within 270 seconds, or what's left of
--timeout, fails it too. The nextupsends the statement again, so write it to be safe to repeat, withIF NOT EXISTSfor example. - An
ON CLUSTERstatement that also starts a mutation waits for both: every host runs the statement, then every replica finishes the mutation.
A timeout comes back as ClickHouse's own error, "not finished on 1 of 2 hosts", because up also sets wait_end_of_query. Without it, the same error reached clickhouse-connect as a broken connection in my tests.
I tested this on a local two-replica cluster with ClickHouse Keeper, stopping and restarting a replica during the wait. Configure cluster in config.yaml, or use a Replicated database, so that up knows which replicas to expect.
Progress in a terminal and in CI
0.6 didn't wait, so it had nothing to show. In a terminal, 0.7 shows a bar for each mutation it waits on, weighted by the bytes of the parts still to rewrite:
Parts vary in size, so a count of parts alone can sit at 95% while the largest one is rewritten. Sizes come from system.parts, read once per part, which is also what makes the time estimate possible. On a self-hosted replicated table, each replica gets its own row.
When output isn't a terminal, the bar would be a stream of control characters, so up prints plain lines instead: one when a wait starts, one at each tenth of the parts or every 30 seconds, and one when it finishes.
→ Applying 824cee6a backfill_digest
waiting on mutation_148.txt (demo_dev.events): 0/40 parts
waiting on mutation_148.txt (demo_dev.events): 4/40 parts · 0:02
waiting on mutation_148.txt (demo_dev.events): 8/40 parts · 0:04
…
waiting on mutation_148.txt (demo_dev.events): 36/40 parts · 0:23
✓ 824cee6a backfill_digest mutation_148.txt · 40 parts · 0:26
✓ Applied 1 migration; dev is at head.The lines name the real mutation ID, so whoever reads the log, a person or an agent, can look it up in system.mutations. If the migration user can't read system.parts, the bar counts parts and says so once. Progress never changes whether up succeeds.
Tested on ClickHouse Cloud
As with 0.5 and 0.6, I ran the integration suite against my ClickHouse Cloud test service as well as local containers. The service has two replicas and runs SharedMergeTree, where a mutation belongs to the service rather than to one replica.
- The version is recorded after the mutation, not before. In the Cloud check, ClickHouse marked a slow
UPDATEfinished, andupinserted the version row 0.20 seconds later. - A killed
uprecovers on Cloud too. I killedupwith SIGKILL while itsUPDATEwas still running. The nextupstopped, because theUPDATEis waived. With--rerun-interrupted, it waited for the firstUPDATE, then sent it again: two mutations, both done, and the right value. - Cloud lists metadata changes as mutations. On SharedMergeTree, even
ADD COLUMNleaves a row insystem.mutations, with no parts to rewrite.upwaits for those like any other until ClickHouse marks them done.
686 unit tests pass on each of Python 3.10 through 3.14. On ClickHouse Cloud, the integration suite passed 50 tests and skipped 12 that hold a mutation still with SYSTEM STOP MERGES, which Cloud doesn't grant, and the 4 Cloud-only checks passed. Against local ClickHouse 26.3 containers, it passed 58 on a single server (4 Cloud-only tests skipped) and 16 on a two-replica cluster with Keeper.
Upgrading from 0.6
Your migrations, environment file and version table don't change. Two things do: up takes as long as the work it starts, and an interrupted migration runs again from its first statement.
1. Install 0.7.
uv tool install ch-migrate-cli==0.7.0
ch-migrate --versionWith pip, use pip install ch-migrate-cli==0.7.0. A loose requirement such as ch-migrate-cli upgrades to 0.7 on its own; pin ch-migrate-cli==0.6.0 if you're not ready.
2. Skip upgrade-env. The environment that 0.6's upgrade-env wrote is current. up adds one small table to the project database, _ch_migrate_in_progress, where it notes each migration while it runs.
3. On ClickHouse Cloud, grant system.parts for the progress bar. bootstrap now grants the migration role SELECT on system.parts and system.replicas. On a self-hosted server it usually has them already, because bootstrap gives it SELECT ON system.* when the admin user can grant that. ClickHouse Cloud's admin can't, so for a role that 0.6 created there, run this as the admin user:
GRANT CURRENT GRANTS(SELECT ON system.parts) TO my_project_migration_role;Replace my_project with your project's name. Without it, the bar counts parts instead of bytes. system.replicas matters only for self-hosted replicated tables, so Cloud doesn't need it.
4. Check your CI time limits. A migration with a large backfill now keeps up running until ClickHouse finishes. If a job's time limit stops it, the work continues, and the next run waits for it. A migration with a waived statement then stops until it's named with --rerun-interrupted, so a CI job that simply retries up will fail there. Pass --timeout if you'd rather have up stop on its own and say what's pending.
5. Check the gate's new findings. The gate now reads SQL sent through text(), op.get_bind() and exec_driver_sql, and SQL built at run time needs a waiver, so ch-migrate lint can report migrations that passed on 0.6. Give each one a waiver with a reason, or leave old ones behind lint.gate_baseline.
6. Run one migration runner per database. Two runs at once would each wait for the other's mutations and could both run the same migration. Use a CI concurrency limit, as you would for 0.6.
The CHANGELOG lists every change.
Try it
Try 0.7 on a test server with uv tool install ch-migrate-cli==0.7.0, then follow the README quick start. And whatever tool you use, after your next data migration, look at system.mutations before you call it done. If you'd like help getting a migration workflow in place, get in touch.