ClickHouse Migrations Without Transactions: What Changed in ch-migrate 0.6

ClickHouse doesn't roll back a failed migration, so every statement must be safe to run twice. ch-migrate 0.6 checks that first, and makes SET apply.

I rehearsed this change before it went anywhere near a client's production databases. On staging, I ran the ALTER TABLE logs MODIFY TTL statement on its own, with materialize_ttl_after_modify = 0 attached to the request so ClickHouse would apply the new TTL to new data only. It finished in half a second and scheduled no mutations. I ran it outside the migration because staging had an unrelated migration pending that ch-migrate up would have applied too. So the rehearsal proved the SQL was safe, but it never touched the tool that would run it.

Production ran it through the tool. In the migration file, the setting was a SET on the line above the ALTER, and ch-migrate sent the two as separate requests. The setting never reached the ALTER, so ClickHouse fell back to its default and started rewriting every existing part of the logs table, in four regions at once. In one region the rewrites took every mutation slot, small tables stopped merging, and inserts started failing with TOO_MANY_PARTS.

Killing the mutations stopped the rewrites in every region. Three days later, one of the client's engineers hit the same silent SET in another migration. When I audited every migration, 45 SET lines across 15 files had never applied, and not one of them had ever raised an error.

ch-migrate is my open-source ClickHouse migration tool, and the bug was mine: every statement went out as its own HTTP request, with no session for a SET to live in. 0.6 fixes that. Cleaning up exposed a bigger problem, too. ClickHouse runs DDL without transactions, so a migration that fails partway keeps everything it did before the failure, and every statement in it has to be safe to run twice. (New to the tool? Start with ClickHouse migrations with Alembic and what changed in 0.5.)

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, and 0.6 doesn't settle it. 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.

The SET statement that never ran

Up to 0.5, ch-migrate reached ClickHouse through clickhouse-sqlalchemy's HTTP transport. Each statement in a migration was its own HTTP request, and ClickHouse's HTTP interface treats each request as a new session unless it names one with session_id. A standalone SET changed a session that ended with its own request. The statement after it ran with the server defaults, and nothing reported an error.

I measured it before changing anything. A test migration ran SET max_threads = 1, then read the setting back in the same revision and in the next one. The server default was 16.

0.6 moves ch-migrate onto the Alembic dialect that ClickHouse maintains in clickhouse-connect. Each run opens one session, and every statement in every revision of that run uses it. ch-migrate also turns on session_check, so if the session expires mid-run, the next statement fails instead of quietly starting a new session with default settings. Sessions time out after 30 idle minutes; set session_timeout in config.yaml if a run needs longer.

Projects that haven't upgraded yet

Installing 0.6 isn't enough on its own. An existing project keeps its old migrations/env.py until you run ch-migrate upgrade-env, and until then up, down and history refuse to connect:

✗ This project's migrations/env.py is from ch-migrate 0.x.
Run ch-migrate upgrade-env.

✗ This project's migrations/env.py is from ch-migrate 0.x.
Run ch-migrate upgrade-env.

lint flags every standalone SET that the old connection would drop:

✗ migrations/sql/history/tables/logs/2026_10_19_1734_7c1acf765d55_set_ttl.up.sql:2  This SET is ignored on this project's connection. Run `ch-migrate upgrade-env`, or put the setting in a SETTINGS clause on the statement that needs it.  [standalone_set]
1 error

✗ migrations/sql/history/tables/logs/2026_10_19_1734_7c1acf765d55_set_ttl.up.sql:2  This SET is ignored on this project's connection. Run `ch-migrate upgrade-env`, or put the setting in a SETTINGS clause on the statement that needs it.  [standalone_set]
1 error

Here's that upgrade against a local ClickHouse server, slowed to half speed: lint catches the SET in a migration like the one at the top of this post, upgrade-env moves the project to 0.6, and up applies it. Without the setting, MODIFY TTL leaves a MATERIALIZE TTL mutation rewriting the table. Here there's none, because the SET reached the ALTER.

ClickHouse migrations without transactions

The SET bug was a statement that didn't run the way it was written. The bigger difference between ClickHouse and the databases most migration tools grew up on is what happens when a statement fails.

In Postgres, a migration runs inside a transaction. If its third statement fails, the first two roll back, and the database is where it started. ClickHouse has no transactions around DDL. Its experimental transactions need ClickHouse Keeper and work only on non-replicated MergeTree tables, so they can't help a migration on a replicated or Cloud database. The first two statements stay applied, and ch-migrate doesn't record the revision, because it didn't finish. You fix the file and run up again, and the whole revision runs again, including the two statements that already worked.

Without transactions, the retry is the recovery plan. A migration has to be correct the second time it runs, not just the first.

That's the property ClickHouse migrations need:

Idempotent: running a statement again leaves the database exactly as running it once did.

ClickHouse statements fall into three groups:

• Statements with an IF form. CREATE ... IF NOT EXISTS, ADD COLUMN IF NOT EXISTS, DROP ... IF EXISTS, RENAME TABLE IF EXISTS. Without the IF, the retry fails on a statement that already worked ("table already exists"), and you're editing a migration in the middle of a failed deploy.
• Statements that change the result when repeated. INSERT, EXCHANGE TABLES, ALTER ... UPDATE and DELETE, and partition copies and moves have no IF form. A second INSERT ... SELECT copies the rows again, and a second EXCHANGE swaps the tables back.
• Statements that converge. MODIFY, MATERIALIZE, TRUNCATE, OPTIMIZE and grants end in the same state however many times they run.

The idempotency gate

0.6 checks every pending upgrade statement against those groups before it runs any of them. If a statement needs an IF it doesn't have, or changes the result when repeated and the file says nothing about it, up stops before running any of them. It lists every finding with its file and line, and says how to make that statement safe to run twice.

Here's a migration that copies error rows into a new archive table:

-- Copy error rows into a new archive table.
CREATE TABLE {db}.logs_archive
(
    id UInt64,
    status String
)
ENGINE = MergeTree
ORDER BY id;

INSERT INTO {db}.logs_archive
SELECT id, status FROM {db}.logs WHERE status = 'error';

-- Copy error rows into a new archive table.
CREATE TABLE {db}.logs_archive
(
    id UInt64,
    status String
)
ENGINE = MergeTree
ORDER BY id;

INSERT INTO {db}.logs_archive
SELECT id, status FROM {db}.logs WHERE status = 'error';

Both statements fail the check. ch-migrate lint reports them without a database connection, and up refuses the run with the same findings:

✗ migrations/sql/history/tables/logs_archive/2026_10_19_1734_a99326ae097f_archive_errors.up.sql:2  Write CREATE TABLE IF NOT EXISTS {db}.logs_archive so a retry skips the existing table  [idempotency]
✗ migrations/sql/history/tables/logs_archive/2026_10_19_1734_a99326ae097f_archive_errors.up.sql:10  A retry inserts the rows again. If nothing else writes to {db}.logs_archive, put TRUNCATE TABLE {db}.logs_archive before it so a retry starts over, then waive it with -- ch-migrate: allow-non-idempotent <reason> on the line above  [idempotency]
2 errors

✗ migrations/sql/history/tables/logs_archive/2026_10_19_1734_a99326ae097f_archive_errors.up.sql:2  Write CREATE TABLE IF NOT EXISTS {db}.logs_archive so a retry skips the existing table  [idempotency]
✗ migrations/sql/history/tables/logs_archive/2026_10_19_1734_a99326ae097f_archive_errors.up.sql:10  A retry inserts the rows again. If nothing else writes to {db}.logs_archive, put TRUNCATE TABLE {db}.logs_archive before it so a retry starts over, then waive it with -- ch-migrate: allow-non-idempotent <reason> on the line above  [idempotency]
2 errors

Each finding names the table it read. The CREATE gets its IF NOT EXISTS. Nothing else writes to logs_archive, so a TRUNCATE before the copy makes a retry start over instead of copying the rows twice. The gate can't tell that the TRUNCATE protects the INSERT, so the INSERT also gets a waiver: a comment directly above it that says why a retry is safe.

-- Copy error rows into a new archive table.
CREATE TABLE IF NOT EXISTS {db}.logs_archive
(
    id UInt64,
    status String
)
ENGINE = MergeTree
ORDER BY id;

TRUNCATE TABLE {db}.logs_archive;

-- ch-migrate: allow-non-idempotent The TRUNCATE above makes a retry start over
INSERT INTO {db}.logs_archive
SELECT id, status FROM {db}.logs WHERE status = 'error';

-- Copy error rows into a new archive table.
CREATE TABLE IF NOT EXISTS {db}.logs_archive
(
    id UInt64,
    status String
)
ENGINE = MergeTree
ORDER BY id;

TRUNCATE TABLE {db}.logs_archive;

-- ch-migrate: allow-non-idempotent The TRUNCATE above makes a retry start over
INSERT INTO {db}.logs_archive
SELECT id, status FROM {db}.logs WHERE status = 'error';

✓ migrations/sql/history/tables/logs_archive/2026_10_19_1734_a99326ae097f_archive_errors.up.sql:13  Idempotency waiver: The TRUNCATE above makes a retry start over  [idempotency]
1 info line

✓ migrations/sql/history/tables/logs_archive/2026_10_19_1734_a99326ae097f_archive_errors.up.sql:13  Idempotency waiver: The TRUNCATE above makes a retry start over  [idempotency]
1 info line

• The reason is required. An empty waiver is still an error, and the comment has to sit directly above the statement, with no blank line between.
• It prints on every run. lint and up show the reason, so it reaches reviewers and deploy logs.
• It doesn't make the statement safe. It records that someone decided what a retry should do. Before retrying a waived statement, check what the first attempt changed.

There's no --skip flag. The only way past the gate is a written reason in the migration file, where it gets reviewed with the SQL it excuses.

Existing migrations aren't affected

ch-migrate upgrade-env records your current head in config.yaml as lint.gate_baseline. That revision and everything before it are exempt in every environment, including environments where they haven't run yet, so old history doesn't suddenly fail. Only migrations written after the upgrade are checked. New projects check every revision.

lint without an environment runs the same checks offline, so CI can run it on every pull request without database credentials.

The gate wouldn't have stopped the incident at the top of this post. MODIFY TTL converges, so the gate doesn't flag it. The session fix closes that one.

Here's the gate at half speed: up refuses the archive migration before running anything, then applies it once the CREATE has its IF NOT EXISTS and the INSERT has a TRUNCATE before it and a reason.

A migration can't run twice

The 0.5 post ended with a warning about the version table. 0.5 records a new version with an asynchronous ALTER TABLE ... UPDATE, so up can return before the update is visible. A second up in that window reads the old version and runs the migration again. I reproduced it: with the update held back, a second up ran both of the first run's migrations again.

0.6 uses the official dialect's approach. It inserts the new version row, then deletes the old one and waits for the delete to finish (mutations_sync = 2). If the process dies while it waits, both rows are still there, and Alembic refuses to continue from overlapping history instead of running anything again. Let the delete finish, then retry.

Replicated where your database is

On self-hosted clusters, the old version table was a local table on whichever node created it, so each node kept its own history, and up runs routed to different nodes could repeat migrations. 0.6 picks the version table's engine from the deployment:

Existing version tables are never converted automatically. ch-migrate status warns when a replicated deployment still has a local version table, and the README walks through converting one by hand.

Tested on ClickHouse Cloud

As with 0.5, I ran the integration suite against my ClickHouse Cloud test service as well as local containers. The service has two replicas and scales to zero when idle. Cloud found one real bug before release, and showed me how sessions and column changes behave there.

• Deduplication could empty the version table. Replicated and Shared MergeTree drop an insert that's identical to an earlier one, a safeguard for retried inserts. After a down put back a previous version row, or an up re-applied a revision that had been downgraded, that insert was dropped while the delete of the other row still ran. The version table ended up empty, and the next up would have run every migration from the start. Each version-row insert now carries its own insert_deduplication_token. The bug never reached a release.
• A session lives on one replica. Cloud routes each TCP connection, not each session ID, to a replica, so a session exists only on the replica its connection reached. ch-migrate keeps each run's session on one connection of its own. In my test, SET max_threads = 3 held across 50 statements spread over 68 seconds, all served by the same replica.
• A retry can wait on a column change. On Cloud, a dropped or renamed column stays locked until ClickHouse finishes the change in the background. A retry that comes before then fails with "affected by mutation ... which is not finished yet", even with IF EXISTS and even though the column is already gone from system.columns. On my test table the change finished within 10 seconds; on a large table it can take much longer. If a retry fails this way, wait for the mutation in system.mutations and run up again.

Upgrading from 0.5

Your migrations and version table don't change. What changes is the environment file and what up accepts next.

1. Check your Python. 0.6 needs Python 3.10 or later. On Python 3.9, pip keeps installing 0.5.x. A plain pip install -U on 3.10 or later skips past 0.5.x, so if you're not ready for the steps below, pin ch-migrate-cli==0.5.1 first.

2. Install 0.6.

uv tool install ch-migrate-cli==0.6.0
ch-migrate --version

uv tool install ch-migrate-cli==0.6.0
ch-migrate --version

With pip, use pip install ch-migrate-cli==0.6.0. If CI pins ch-migrate-cli==0.5.1, change the pin in the same pull request as the next step.

3. Run ch-migrate upgrade-env in each project. It replaces migrations/env.py with a short file that hands off to the package, saves the old one as env.py.bak, and records the gate baseline in config.yaml. Until you run it, up, down and history refuse to connect.

4. Bring back any customizations. upgrade-env doesn't merge. If you edited env.py, for example to add connection settings, reapply them from env.py.bak. If you added a session ID to work around the SET problem, you can drop it: 0.6 opens a session for every run.

5. Review and commit the diff. Everything up to the baseline is exempt from the gate, so check it before you commit. Don't re-run upgrade-env later to move the baseline past new findings; add a waiver instead.

6. Expect the gate on your next migration. The first new migration with a bare CREATE TABLE will be refused. Running ch-migrate lint in CI shows the findings before anyone runs up. The CHANGELOG lists every change.

Try it

Try 0.6 on a test server with uv tool install ch-migrate-cli==0.6.0, then follow the README quick start. And whatever version you run, search your migrations for a standalone SET: on 0.5 or earlier, over HTTP, it never applied. If you'd like help getting a migration workflow in place, get in touch.