Zero-downtime structural migrations
chkit generate --cutover changes a populated table’s ORDER BY, PRIMARY KEY, PARTITION BY or engine without losing a row and without pausing the writers that insert into it.
Why a structural change needs a cutover
Section titled “Why a structural change needs a cutover”ClickHouse cannot ALTER a table’s sorting key, partition key or engine. For such a change generate plans a DROP TABLE followed by a CREATE TABLE, which deletes every row; that is why it needs --allow-destructive. A cutover builds the new table next to the old one instead, fills it while the old one keeps serving reads and writes, and swaps the two with EXCHANGE TABLES, which renames both atomically.
How a cutover works
Section titled “How a cutover works”The generated migration runs these steps for a table analytics.events with unique key id:
- Shadow and mirror. It creates the shadow table
events__cutover_<stamp>with the new definition, and a materialized viewevents__cutover_<stamp>_mirrorFROM events TOthe shadow. EveryINSERTthat starts from now on writes its rows to both tables. - Insert barrier. An
INSERTbuilds its pipeline, including the materialized views it writes to, when it starts, so theINSERTs already running do not reach the mirror. chkit reads the runningINSERTqueries on every replica and waits until each of them has finished. - Snapshot. It attaches every partition of
eventsto a frozen copy,events__cutover_<stamp>_snapshot(ATTACH PARTITION ALL FROM: hard links on open-source ClickHouse; on ObsessionDB a server-side copy of the parts in object storage, which takes longer on a large table), and waits for theINSERTs that were running at that moment. Every row of the snapshot was either inserted before the mirror existed, or has reached the shadow through the mirror. - Copy. It inserts the snapshot rows whose
idthe shadow does not hold yet. Rows inserted before the mirror are copied; mirrored rows are skipped. A retried copy skips the rows an earlier attempt wrote. - Verify. For every
idof the snapshot it compares the number of rows and a hash of their values with the shadow. Any difference stops the migration before the swap. - Swap.
EXCHANGE TABLES events AND events__cutover_<stamp>:eventsnow has the new definition and every row. Inserts that started before the swap finish into the old table and still reach the new one through the mirror, which is dropped next. - Reconcile. An
INSERTresolves the names in its pipeline one after another when it starts: the table, the materialized views that read it, theirTOtables. One that starts at the instant of the swap can resolve the table before the swap and the rest after it, and then writes its rows only to the old table: once without running any materialized view, or twice (directly and through the mirror) after running them. After another insert barrier, the rows of the old table whoseidthe new table lacks are added toevents: rows the old table holds once with anINSERT, which runs the materialized views that readevents; rows it holds twice through a staging table andATTACH PARTITION ALL FROM, which does not run them a second time. Such rows are missing fromeventsfor the few seconds between the swap and this step. - Old table. It renames the old table to
events_previous(--cutover-old keep, the default) or drops it (--cutover-old drop), and drops the snapshot.
Writers insert into events the whole time. A plain INSERT INTO events that starts at the instant of the swap can be rejected with TABLE_UUID_MISMATCH (code 741); it wrote no rows, so retry it. Readers read events: the old table until the swap, the complete new table after it. Materialized views that read from events or write into it keep working, because ClickHouse binds them to the table name, which the swap keeps.
Preconditions
Section titled “Preconditions”- A unique key.
--cutover analytics.events=idnames the columns that identify a row. The copy tells copied rows from mirrored rows by it, so noINSERTmay reuse an existing key while the cutover runs, and the key columns cannot beNullable. The verification fails, before the swap, when a key was inserted again before the snapshot; an exact repeat of an existing row inserted after the snapshot cannot be detected. - No mutations. A materialized view only sees
INSERTs. Do not runALTER TABLE ... UPDATE/DELETE, lightweightDELETE,TRUNCATEor partition operations on the table until the migration completes. The migration refuses to start while a mutation is unfinished, and the verification fails when an update changed rows between the mirror and the snapshot. A change made after the snapshot does not reach the new table, and chkit cannot detect it. - Writers chkit can see. The barrier waits for queries in
system.processes, which includesINSERTs, their materialized views, inserts throughDistributedtables and asynchronous insert flushes. A Kafka (or RabbitMQ, NATS, S3Queue, FileLog) consumer view, a refreshable materialized view or aBuffertable writes from a background thread;generaterefuses a cutover while the schema has one writing into the table, directly or through intermediate tables. Detach it for the cutover. - No fire-and-forget async inserts. ClickHouse binds the buffer of an asynchronous insert to the table it was sent to. When that table is swapped before the buffer is flushed, the flush fails with
TABLE_UUID_MISMATCHand the buffered rows are dropped. Withwait_for_async_insert = 1the writer receives that error and can retry; withwait_for_async_insert = 0it already got its acknowledgement and the rows are lost.migratereadssystem.query_logon every replica and stops the cutover, at its first step and again before the swap, while async inserts withwait_for_async_insert = 0reached the table in the last 10 minutes. ObsessionDB defaults towait_for_async_insert = 0: set it to1on those writers for the cutover, or pause them. A user who cannot readsystem.query_loggets a warning instead of the check. - Supported tables.
MergeTree,ReplicatedMergeTreeandSharedMergeTree, in anAtomicorReplicateddatabase (EXCHANGE TABLESneeds one). Engines that merge rows away (ReplacingMergeTree,SummingMergeTree,AggregatingMergeTree,CollapsingMergeTree) are refused: their copy cannot be verified row by row. A replication path must contain{uuid}or{table}, so the shadow registers at its own path, and aReplicatedtable needsclickhouse.cluster, so the shadow is created on every replica. - Columns. Columns can be added or removed in the same change; the copy and the mirror write the columns both tables store. A column whose type changes, a column or table rename, a TTL (it deletes rows at different times in the two tables) and a new
CHECKconstraint (rows the old table accepts would fail the mirror, and the writers’INSERTs with it) are refused: change them in a separate migration. - Dependent views. Views and materialized views that read from or write into the table keep working, but one that the same migration drops and recreates would miss every
INSERTof the cutover;generaterefuses it. Change such a view in a separate migration. - Disk. The shadow holds a second copy of the table until the old one is dropped. On ObsessionDB the snapshot holds a third copy until the migration drops it at its end.
Run a cutover
Section titled “Run a cutover”Change the table’s definition in your schema, here the sorting key of analytics.events from id to (user_id, occurred_at, id):
const events = table({ database: 'analytics', name: 'events', engine: 'MergeTree()', columns, partitionBy: 'toYYYYMM(occurred_at)', primaryKey: ['user_id', 'occurred_at', 'id'], orderBy: ['user_id', 'occurred_at', 'id'],})Generate the cutover instead of the drop and recreate, review the migration, and apply it while your writers keep running:
chkit generate --name events_by_user --cutover analytics.events=idcat chkit/migrations/*_events_by_user.sqlchkit migrate --applygenerate records the final definitions in the snapshot, including the retained analytics.events_previous. Afterwards everything is clean:
chkit status --jsonchkit drift --jsonchkit check --jsonchkit generate --dryrun --jsonThe copy runs as an async statement: an interrupted migrate re-attaches to it, and a copy that failed runs again and skips the rows it already wrote.
Copy with the backfill plugin
Section titled “Copy with the backfill plugin”For a large table, let the backfill plugin copy the rows in chunks, locally with checkpoints or as a managed job with submit:
chkit generate --name events_by_user --cutover analytics.events=id --cutover-copy backfillchkit migrate --apply # creates the shadow and the mirror, takes the snapshot, then stops at the copy checkchkit plugin backfill plan --cutover analytics.eventschkit plugin backfill run --plan-id <planId> # or: chkit plugin backfill submit --cutover analytics.eventschkit migrate --apply # verifies the copy and swapsThe first migrate exits with code 1 at the verification, like a backfill assertion; writers keep inserting into the table and the mirror keeps the shadow current while the backfill runs. Each chunk skips the rows whose key the shadow holds, so a chunk can run again. --cutover-copy backfill is not available with clickhouse.cluster.
Keep or drop the old table
Section titled “Keep or drop the old table”With --cutover-old keep (the default) the old table stays as <table>_previous, with its old definition and the rows it received until the mirror was dropped. The rows of an INSERT that started at the instant of the swap can appear in it twice. The snapshot records it as retained, so drift and check compare it and the next generate does not plan to drop it. Drop it when you no longer need it; this is a normal destructive change:
chkit generate --name drop_events_previous --drop-retained analytics.events_previouschkit migrate --apply --allow-destructiveA second cutover of the same table needs the name free: drop the retained table first, or use --cutover-old drop. With a replication path that uses {table}, ClickHouse expanded the macro when the table was created, so the retained table keeps the path of the original name: drop it before you recreate the table without a cutover, or use {uuid}.
With --cutover-old drop the migration drops the old table after the swap. The drop is a danger operation and needs --allow-destructive.
Clusters and ObsessionDB
Section titled “Clusters and ObsessionDB”- ObsessionDB. No configuration is needed. The copy and the verification read with
select_sequential_consistency = 1, so each sees the parts every replica committed. After the swap,migratewaits until every replica lists the swapped tables before it continues. clickhouse.cluster. Every DDL statement runsON CLUSTER. The copy runs on every shard (INSERT INTO FUNCTION cluster(...)withparallel_distributed_insert_select = 2), so each shard copies its own rows, andSYSTEM SYNC REPLICA ... LIGHTWEIGHTruns before the copy and the verification. UseReplicatedMergeTree: a plainMergeTreeon a cluster is refused.
Failures and retries
Section titled “Failures and retries”Each step is recorded in the migration journal. When a step fails, fix the cause and run chkit migrate --apply again: the completed steps are skipped and the failed one runs again.
- The barrier times out after 10 minutes when an
INSERTthat started before the step is still running. Let it finish, then re-runmigrate. Do not kill it to insert its rows again: a killedINSERTmay have committed part of its rows, and inserting them again reuses their keys. - The verification fails when the shadow does not match the snapshot. The table was not swapped. Find the cause (a reused key, a mutation), then drop the cutover’s objects (
<table>__cutover_<stamp>,_mirror,_snapshot), abandon the migration and run it again. - The reconcile reads both tables in full after the swap (a
GROUP BYof every key, which spills to disk on a large table). ItsATTACHis guarded like the swap: retried after a lost acknowledgement, it stops withthe ATTACH may already have runwhen the staging rows are already in the table; replace the statement withSELECT 1;, delete its-- before-retry:line, and resume with--retry. - The swap was interrupted.
EXCHANGE TABLESis not idempotent: running it again swaps the tables back. Before chkit retries it, it waits until the replicas agree on both tables and runs a guard that checks that the table still has its old layout. When the swap had already run, the guard stops the migration withthe EXCHANGE may already have run. Compare both tables insystem.tables. If the table has its new layout, the swap is done: replace theEXCHANGE TABLESstatement in the migration file withSELECT 1;, delete its-- before-retry:line, and resume withchkit migrate --apply --retry <migration>. - Leftovers. A cutover started again from its first step, after an abandon, refuses to run while its shadow, mirror or snapshot exist. Before the swap they hold nothing the table lacks, so drop them. After the swap the shadow’s name belongs to the pre-cutover table, which may hold rows not reconciled yet: do not drop it; compare both tables in
system.tablesand resume the migration with--retryinstead.
Roll back
Section titled “Roll back”After a keep cutover the old table is still there as <table>_previous, but it stopped receiving rows when the mirror was dropped. To go back without losing the rows written since, run another cutover: drop the retained table, change the schema back, and generate --cutover again. A plain reverse EXCHANGE TABLES is only safe while writers are paused and before any row was written to the new table; write it as a manual migration with the same layout guard.
When a cutover does not fit
Section titled “When a cutover does not fit”A table without a unique key, a change that transforms rows, or an engine that merges rows needs a reviewed manual migration, with writers paused from the copy until the swap:
- Keep the schema unchanged and scaffold a migration with
chkit generate --empty --name copy_events. Create the new table under a temporary name, copy the rows with the transformation you need, and validate the copy with a guard such asSELECT throwIf(...). - Change the schema to the final definitions, declaring the old layout as
events_previousif you keep it, and runchkit generate. It records the final definitions in the snapshot. Replace the generatedDROP/CREATEof that migration with a guard and the swap:
-- operation: cutover_validate key=table:analytics.events:cutover:before_exchange risk=cautionSELECT throwIf((SELECT sorting_key FROM system.tables WHERE database = 'analytics' AND name = 'events') != 'id', 'events is no longer on its old layout; inspect before you retry');
-- operation: exchange_tables key=table:analytics.events risk=caution-- before-retry: SELECT throwIf((SELECT sorting_key FROM system.tables WHERE database = 'analytics' AND name = 'events') != 'id', 'the EXCHANGE may already have run')EXCHANGE TABLES analytics.events AND analytics.events_previous;- Apply both migrations, check
status,drift,checkandgenerate --dryrun, and resume the writers.
Related pages
Section titled “Related pages”chkit generate— the--cutoverflagschkit migrate— retries,-- before-retry:and insert barriers- Backfill plugin — copying a cutover’s rows in chunks
- Migration workflow