---
title: Migrations
---

This page holds the rules for writing a migration. How a deployment applies them, and the upgrade and rollback steps, are in [upgrades](/docs/zh-CN/operations/upgrades#how-migrations-run). [`schemas/README.md`](https://github.com/ClickHouse/loom/blob/main/schemas/README.md) maps the `schemas/` directory and lists the operator tools.

## The journal and the ledger

- The schema is a journal of numbered SQL files in `schemas/migrations/`, applied in
  numeric-prefix order by `server/core/schema/migrate.py`. `000_migrations.sql` creates the ledger;
  `016_baseline.sql` is the starting schema; every later file is a delta.
- Each applied file is recorded in `loom._migrations` with the SHA-256 of its contents.
  A re-run applies only new files. A changed file is **checksum drift** and a file the
  repository no longer has is a **ledger-only row**; `loom migrate --dry-run --strict`
  (a CI step and the deploy preflight) fails on either. A file renamed with its text
  unchanged is not ledger-only: its SHA-256 names it, and it is recorded under the new
  name without running again.
- Every file addresses `loom.<table>` and `loom_telemetry.<table>`. The runner maps them
  to `LOOM_CH_DATABASE` and `LOOM_CH_TELEMETRY_DATABASE` as it sends each statement
  (`server/core/ch_names.py`), so the checksum is over the file as written. A database
  is compared by value as `currentDatabase()` or a bound name, never a literal `'loom'`
  (`tests/unit/test_ch_names.py`). A file that changes the key or TTL of a table whose
  name starts with `_`, or renames or exchanges one, also updates its row in [storage
  layout](/docs/zh-CN/architecture/storage-layout) and the reshape guard of
  `tests/unit/test_storage_layout_rows.py`.

## Numbering

A file is named `NNN_description.sql`. The runner orders by the integer prefix, not the
name (`server/core/schema/journal.py`, `migration_order`), so `1030_x.sql` runs after `220_y.sql`. Take the next free number, checking `main`
and open pull requests, and keep it: two files with one number still apply, but in the
lexical order of their suffixes, which is not the order anyone intended, and a rebase
that renames one re-applies nothing. `tests/unit/test_migration_numeric_order.py` fails on a
shared number.

## Rules for writing a migration

- **Never edit or delete a merged migration.** Deployments' ledgers hold its hash. A
  correction is a new file.
- **Additive only.** Add tables, columns, indexes, projections and views; drop a skip
  index or a projection no code under `server/`, `scripts/`, `bench/` or `dev/` names (the old build loses an
  optimisation, never a column); change codecs, defaults or table settings; widen types;
  extend an enum. Never drop or rename a table or a column, and never exchange tables.
  `tests/unit/test_migrations_additive.py` enforces this. Of views and tables it allows
  only `DROP VIEW IF EXISTS` of an insert-time materialized view and
  `DROP TABLE IF EXISTS` of a superseded table, each when no file under `server/`,
  `scripts/`, `bench/` or `dev/` names it: neither a serving build nor an operator tool
  (they run against the production service) reads it. Narrowing a type is a review question, since a regex
  cannot tell it from a widening.
- **Retire a column in two releases.** First ship a release whose code no longer reads or
  writes it; drop it only once no serving build names it.
- **Every writer carries a new column.** Adding a column to `loom.memory` or
  `loom.shared` means updating the row builders in
  `server/memory/store/rows.py` (`build_memory_version_row`,
  `build_embedding_row`) and every projection that feeds them in the same change:
  these are `ReplacingMergeTree(version)` tables read with `FINAL`, and a version row
  that omits a column silently zeros it for every reader. A new `loom.memory` column must
  also be added to `loom.memory_versions` and to the `memory_versions_mv` view's `SELECT`
  in the same migration, or the history stops recording it
  (`tests/unit/test_memory_versions_schema.py` compares the column lists).
- **A table that can hold memory content** also needs an erasure saga step and a
  residue-census row (`server/erasure/store/census.py`, `_CENSUS`);
  `tests/unit/test_every_table_is_classified.py` fails on a new table that is in no list
  ([adding a table](/docs/zh-CN/development/adding-a-table)).
- **Engine-portable DDL.** Declare vector columns `Array(BFloat16) CODEC(NONE)`; where
  the `Quantized` codec can be on the runner creates them coded
  ([gated settings](/docs/zh-CN/architecture/clickhouse#gated-clickhouse-settings)). Use plain
  `MergeTree` engine names, which ClickHouse Cloud runs as `SharedMergeTree`.

**Why additive.** The build that was serving before a migration ran must still read and
write every table it knew afterwards. That makes a blue/green rollback a proxy swap
instead of a schema rollback, and lets the old colour keep serving while the new one
applies migrations at boot. The price is paid here: two releases to retire a column, and
rebuilds instead of `ALTER` for key changes.

## Runner directives

A migration file can carry comment directives the runner reads:

| Directive                           | Effect                                                                                    |
| ----------------------------------- | ----------------------------------------------------------------------------------------- |
| `-- loom: tolerate-error <code>`    | the file survives one named server error (for example a setting only some servers accept) |
| `-- loom: rebuild-if-empty <table>` | declares a target sort or partition key for `<table>` (below)                             |
| `-- loom: baseline covers N`        | the file replaces every file numbered N or lower that the repository no longer carries    |

**Baselines.** A baseline is numbered N, after the newest file it replaces. A fresh
database runs it. A database whose ledger holds file N records it without running and
keeps the replaced rows, which strict mode reports as superseded rather than as errors. So
a squash needs no hand edit of any ledger, and a build from before it still finds all of
its own files applied. A database whose ledger holds some replaced files but not file N
stopped partway: `loom migrate` refuses it (`stranded`) and changes nothing, since running
the baseline would skip the files it lacks and recording it would claim their schema.
Apply the missing files with a build from before the squash, then migrate again. Squash at
a release, not per change; the squash builds the baseline from `schemas/schema.sql`
(the dump body without the objects the files it keeps create) and proves it on a fresh
server, on a fully migrated ledger and on a partway one.

The runner also adapts statements at apply time without changing the files or their
checksums: it drops the experimental NLP flag where the server forbids it, creates vector
columns with the `Quantized` codec where it can be on, dropping a general codec chained
after it where `allow_suspicious_codecs` cannot be (`server/core/schema/vector_codec_ddl.py`),
creates every view under the user running the migration
(`DEFINER = CURRENT_USER`: the dump names its own user, which another server may not have), and waits for every mutation a file issues before
recording it (`LOOM_MIGRATE_MUTATION_WAIT_S`).

**Pre-checks.** A file that must not run while the data holds something the file alone
cannot see registers a check in `server/core/schema/prechecks.py`, keyed by its name
without the number, so a renumbered file keeps it. The runner runs the check before the
file, a dry run included; a check that refuses stops the run with its message and runs
nothing of the file. `097_mirror_names_reserved.sql` uses one: it refuses while a
namespace of the reserved mirror form is registered or holds live rows.

## Changing a sort key or a partition key

ClickHouse changes neither in place, so a migration declares the target and the table is
rebuilt, never altered:

```sql
-- loom: rebuild-if-empty code_test_facets
CREATE TABLE loom.code_test_facets ( ...every column, unchanged... ) ... ORDER BY (...);
```

That statement is the only copy of the target DDL. The runner (`server/core/schema/table_rebuild.py`)
compares the live table's `sorting_key` and `partition_key` with it: a match records the
file, an empty table (a fresh install) is exchanged for a twin with the target DDL on the
spot, and a table with rows fails the boot with a message naming the tool. A boot never
copies rows. On a deployment with data, the operator runs the tool first, one table at a
time:

```bash
python -P -m scripts.ops.table_rebuild --table code_test_facets --dry-run
python -P -m scripts.ops.table_rebuild --table code_test_facets --yes
```

Every changing action (`--yes` alone, `--rollback`, `--drop-pre-rebuild`) prints the
ClickHouse it talks to and its plan, and changes nothing without `--yes`. The tool copies
every row (not FINAL) into `loom.<t>_rebuild` in bounded batches, copies the parts written
meanwhile, checks the row count under FINAL and the newest version of a sample of keys,
swaps with `EXCHANGE TABLES`, and keeps the old table as `loom.<t>_pre_rebuild` for
`--rollback` until `--drop-pre-rebuild`. It refuses to swap, keeping the twin, when an
`ALTER` changed the live table during the copy: exit 6 for one ClickHouse runs as a
mutation (dropping, renaming or retyping a column, dropping an index, a TTL), exit 3 for
one that only moves its metadata time (an index, a setting, a column's default or codec).
An added column fails the copy (exit 1, no swap), or the run exits 3 after the last verify
pass. A rebuild does not carry an `ALTER` over: restate it in the target statement, then
rerun with `--restart`. It also refuses a table no migration declares.
`loom migrate --dry-run --strict` fails while a declared table still holds rows in the old
layout, so the deploy preflight catches a skipped step.

The target keeps the table's columns (so the swap is additive), and its sort key keeps
the old key's columns, reordered: that key is also what a `ReplacingMergeTree` merges on.
A key with other columns merges other rows and is listed with its reason in
`tests/unit/test_migrations_additive.py`. Name the rebuild in the release notes as an
operator step ([release process](/docs/zh-CN/development/release-process#release-notes)).

## Postgres migrations

`schemas/pg_migrations/NNN_*.sql` follow the same rules: numbered, additive, never
edited once merged, and every `CREATE`, `ALTER` or `DROP` of a table names
`loom.<table>`, except a rename's new name, where Postgres takes no schema
(`tests/unit/test_storage_layout_rows.py`). A boot applies them under a Postgres
advisory lock, each ordinary file in one transaction with its ledger row. A migration waits at
most 5 s for a table lock (`lock_timeout`, then retried), so an `ALTER` never queues
every query of the table behind a long transaction. A build serves a ledger that holds a
later build's files, with a warning naming them; a ledger file it does not carry
numbered at or below its own last file, or one whose checksum changed, refuses the boot.

Ordinary files run in one transaction, so they build no index on a table that
already ships: a transactional build blocks writes until it ends. An index on a
table introduced by the same release may be transactional because that table is
small wherever the file runs (`010_revision_delivered_at.sql`).

For an existing table, the shared Postgres runner accepts only this online file
form, in either the cell or router journal:

```sql
-- loom:online-index loom_router.placements_by_cell_org ON loom_router.placements (cell, org)
CREATE INDEX CONCURRENTLY placements_by_cell_org ON loom_router.placements
USING btree (cell COLLATE "C", org COLLATE "C");
```

The declaration and statement must name the same schema, table, index and ordered
columns. Only ordinary identifiers, a non-unique btree and C collations are
accepted. Expressions, predicates, included columns and additional statements
are refused before the ledger changes. This is not a general escape from
transactional migrations.

The runner takes session advisory locks by retrying `pg_try_advisory_lock`, with
no transaction or snapshot between attempts. It releases the ordinary migration
key before acquiring a separate schema-scoped online-index key and running
concurrent DDL. A shipped blocking migrator can therefore finish its unchanged
journal while the index waits for old snapshots. After the build finishes, the
runner reacquires the ordinary key, records the file's checksum in the existing
ledger and commits a WAL-message fence. Later files wait for this ledger entry.

Catalog validation checks the table, ordered columns and collations, btree
method, absence of constraints and expressions, and valid/ready state. A
matching incomplete index is dropped concurrently and rebuilt; a different
same-named object refuses without being dropped. A recorded but absent or
invalid index refuses as corruption. Even when every file is recorded, a build
carrying an online file validates its index and acknowledges a fresh WAL fence:
a primary-visible ledger row after a cancelled synchronous COMMIT is insufficient.
An interrupted build, drop or ledger write is retried from catalogs and the
existing ledger, without a separate job table. Cancellation terminates the
migration session before asyncpg can wait indefinitely on rollback.

The router's dedicated migration connection has no statement timeout; its
serving pool retains its statement limit. The runner preserves a borrowed
connection's statement budget, including a stopped restore command's deadline.
Concurrent DDL retains the five-second lock timeout and can be interrupted and
retried without changing the file checksum.

## Schema dump and check

`schemas/schema.sql` is the dumped result of applying every migration: read it to
understand the current shape, never hand-edit it. After a migration change, regenerate it
against a fresh ClickHouse of the release CI tests (`clickhouse/clickhouse-server:latest`):

```bash
bash dev/ci_start_clickhouse.sh     # that image with Loom's server config
make migrate
make schema-dump                    # rewrites schemas/schema.sql only when the dump differs
make schema-check                   # exit 1 with the head of the diff; writes nothing
```

A dump that fails leaves the file as it was. Another ClickHouse version, a server where the
`Quantized` codec or its `LZ4` chain cannot be on, or a database holding rebuild twins dumps
differently, which is why the pinned container matters. Internal tables (`_migrations`, names with a leading `_` or `.`)
are left out. CI's `schema-check` job replays every migration on a fresh server, runs the
strict dry run, then `make schema-check`.

CI follows `latest`, so a new ClickHouse release that prints DDL differently turns
`schema-check` red with no Loom change. Read the diff the job prints: when it is only
formatting, run the four commands above (they pull the new release) and commit
`schemas/schema.sql` on its own. A change in meaning (a type, a default, an engine
setting) is a ClickHouse behaviour change to look into before dumping over it;
`LOOM_CLICKHOUSE_IMAGE=clickhouse/clickhouse-server:A.B.C.D` runs the commands above on
the last release that dumped cleanly, to compare.

## Checklist

1. Take the next free number; write the file additively.
2. Update the row builders, `loom.memory_versions`, the erasure census and the analyzer
   cases if the change touches them.
3. `make migrate` against a local ClickHouse, then `make schema-dump` and commit
   `schemas/schema.sql` with the migration.
4. `make test-unit` (the additive, numbering and schema-classification tests) and the
   integration suite for the paths that read the new shape.
5. Note any operator step (a rebuild, a backfill) for the release notes.

## Related

- [Upgrades](/docs/zh-CN/operations/upgrades): how the runner applies files on a deployment
- [ClickHouse](/docs/zh-CN/architecture/clickhouse)
- [Schema reference](/docs/zh-CN/reference/schema)
