---
title: Zero-downtime structural migrations
description: Change a populated table's sorting key, primary key, partition key or engine while writers keep inserting, with chkit generate --cutover.
---

`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

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

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 `INSERT`s 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 `INSERT`s 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.

## Preconditions

- **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 `INSERT`s. 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 `INSERT`s, 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' `INSERT`s 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.

## 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)`:

```ts
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:

```sh
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:

```sh
chkit status --json
chkit drift --json
chkit check --json
chkit generate --dryrun --json
```

The copy runs as an [async statement](/cli/migrate/#async-operations-modeasync): 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

For a large table, let the [backfill plugin](/plugins/backfill/#cutover-copy) copy the rows in chunks, locally with checkpoints or as a managed job with `submit`:

```sh
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](/cli/migrate/#backfill-assertions); 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

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:

```sh
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`.

## 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, `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.

## 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 `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](/cli/migrate/#abandoning-a-failed-migration) 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.

## 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](#when-a-cutover-does-not-fit) with the same layout guard.

## 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:

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:

```sql
-- 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;
```

3. Apply both migrations, check `status`, `drift`, `check` and `generate --dryrun`, and resume the writers.

## Related pages

- [`chkit generate`](/cli/generate/#zero-downtime-cutover) — the `--cutover` flags
- [`chkit migrate`](/cli/migrate/) — retries, `-- before-retry:` and insert barriers
- [Backfill plugin](/plugins/backfill/#cutover-copy) — copying a cutover's rows in chunks
- [Migration workflow](/guides/migration-workflow/)
