Skip to content

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.

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.

The generated migration runs these steps for a table analytics.events with unique key id:

  1. Shadow and mirror. It creates the shadow table events__cutover_<stamp> with the new definition, and a materialized view events__cutover_<stamp>_mirror FROM events TO the shadow. Every INSERT that starts from now on writes its rows to both tables.
  2. Insert barrier. An INSERT builds its pipeline, including the materialized views it writes to, when it starts, so the INSERTs already running do not reach the mirror. chkit reads the running INSERT queries on every replica and waits until each of them has finished.
  3. Snapshot. It attaches every partition of events to 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 the INSERTs 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.
  4. Copy. It inserts the snapshot rows whose id the 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.
  5. Verify. For every id of 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.
  6. Swap. EXCHANGE TABLES events AND events__cutover_<stamp>: events now 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.
  7. Reconcile. An INSERT resolves the names in its pipeline one after another when it starts: the table, the materialized views that read it, their TO tables. 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 whose id the new table lacks are added to events: rows the old table holds once with an INSERT, which runs the materialized views that read events; rows it holds twice through a staging table and ATTACH PARTITION ALL FROM, which does not run them a second time. Such rows are missing from events for the few seconds between the swap and this step.
  8. 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.

  • A unique key. --cutover analytics.events=id names the columns that identify a row. The copy tells copied rows from mirrored rows by it, so no INSERT may reuse an existing key while the cutover runs, and the key columns cannot be Nullable. 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 run ALTER TABLE ... UPDATE/DELETE, lightweight DELETE, TRUNCATE or 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 includes INSERTs, their materialized views, inserts through Distributed tables and asynchronous insert flushes. A Kafka (or RabbitMQ, NATS, S3Queue, FileLog) consumer view, a refreshable materialized view or a Buffer table writes from a background thread; generate refuses 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_MISMATCH and the buffered rows are dropped. With wait_for_async_insert = 1 the writer receives that error and can retry; with wait_for_async_insert = 0 it already got its acknowledgement and the rows are lost. migrate reads system.query_log on every replica and stops the cutover, at its first step and again before the swap, while async inserts with wait_for_async_insert = 0 reached the table in the last 10 minutes. ObsessionDB defaults to wait_for_async_insert = 0: set it to 1 on those writers for the cutover, or pause them. A user who cannot read system.query_log gets a warning instead of the check.
  • Supported tables. MergeTree, ReplicatedMergeTree and SharedMergeTree, in an Atomic or Replicated database (EXCHANGE TABLES needs 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 a Replicated table needs clickhouse.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 CHECK constraint (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 INSERT of the cutover; generate refuses 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.

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:

Terminal window
chkit generate --name events_by_user --cutover analytics.events=id
cat chkit/migrations/*_events_by_user.sql
chkit migrate --apply

generate records the final definitions in the snapshot, including the retained analytics.events_previous. Afterwards everything is clean:

Terminal window
chkit status --json
chkit drift --json
chkit check --json
chkit generate --dryrun --json

The 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.

For a large table, let the backfill plugin copy the rows in chunks, locally with checkpoints or as a managed job with submit:

Terminal window
chkit generate --name events_by_user --cutover analytics.events=id --cutover-copy backfill
chkit migrate --apply # creates the shadow and the mirror, takes the snapshot, then stops at the copy check
chkit plugin backfill plan --cutover analytics.events
chkit plugin backfill run --plan-id <planId> # or: chkit plugin backfill submit --cutover analytics.events
chkit migrate --apply # verifies the copy and swaps

The 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.

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:

Terminal window
chkit generate --name drop_events_previous --drop-retained analytics.events_previous
chkit migrate --apply --allow-destructive

A 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.

  • 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, migrate waits until every replica lists the swapped tables before it continues.
  • clickhouse.cluster. Every DDL statement runs ON CLUSTER. The copy runs on every shard (INSERT INTO FUNCTION cluster(...) with parallel_distributed_insert_select = 2), so each shard copies its own rows, and SYSTEM SYNC REPLICA ... LIGHTWEIGHT runs before the copy and the verification. Use ReplicatedMergeTree: a plain MergeTree on a cluster is refused.

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 INSERT that started before the step is still running. Let it finish, then re-run migrate. Do not kill it to insert its rows again: a killed INSERT may 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 BY of every key, which spills to disk on a large table). Its ATTACH is guarded like the swap: retried after a lost acknowledgement, it stops with the ATTACH may already have run when the staging rows are already in the table; replace the statement with SELECT 1;, delete its -- before-retry: line, and resume with --retry.
  • The swap was interrupted. EXCHANGE TABLES is 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 with the EXCHANGE may already have run. Compare both tables in system.tables. If the table has its new layout, the swap is done: replace the EXCHANGE TABLES statement in the migration file with SELECT 1;, delete its -- before-retry: line, and resume with chkit 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.tables and resume the migration with --retry instead.

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.

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:

  1. 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 as SELECT throwIf(...).
  2. Change the schema to the final definitions, declaring the old layout as events_previous if you keep it, and run chkit generate. It records the final definitions in the snapshot. Replace the generated DROP/CREATE of that migration with a guard and the swap:
-- operation: cutover_validate key=table:analytics.events:cutover:before_exchange risk=caution
SELECT 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;
  1. Apply both migrations, check status, drift, check and generate --dryrun, and resume the writers.