Clickhouse Migration Deep Dive

ClickHouse Migration Deep Dive

Overview

Plan and execute ClickHouse schema migrations: column changes, engine migrations, ORDER BY modifications, and versioned migration runners. ClickHouse ALTER operations behave unlike PostgreSQL/MySQL — most are asynchronous mutations that rewrite data parts in the background, and some changes (ORDER BY, engine) require full table recreation. This skill walks the safe path for each.

Prerequisites

• ClickHouse admin access
• Backup of production data (see clickhouse-prod-checklist)
• Test environment for validation

Instructions

Follow these steps in order. SQL and runner code for each step live in the linked reference files — keep them open while you work.

Step 1: Classify the operation

Decide whether your change is lightweight (instant, metadata only) or a heavyweight mutation (rewrites parts in the background):

-- Lightweight (instant): ADD COLUMN, RENAME COLUMN, COMMENT COLUMN
ALTER TABLE events ADD COLUMN country LowCardinality(String) DEFAULT '';

-- Heavyweight (mutation): MODIFY COLUMN, DROP COLUMN, DELETE, UPDATE
ALTER TABLE events MODIFY COLUMN properties String CODEC(ZSTD(3));

-- Always monitor mutation progress
SELECT database, table, mutation_id, is_done, parts_to_do
FROM system.mutations WHERE NOT is_done ORDER BY create_time;

Step 2: Run column operations

Use Edit/Write to author the ALTER TABLE statements, then apply them. Add/modify/drop columns, set materialized defaults, and attach codecs. Full DDL semantics and every column-operation variant: DDL & column operations.

Step 3: Recreate the table for ORDER BY / engine changes

ClickHouse has no MODIFY ORDER BY and no in-place engine change. Create a new table, INSERT ... SELECT the data, then atomically RENAME TABLE to swap. Full create → copy → swap → verify → drop recipe for both cases: table recreation.

Step 4: Wire a versioned migration runner

For repeatable, tracked migrations, drive numbered .sql files through a runner that records applied versions in a _migrations table and stops on first failure. Use Write to scaffold runner.ts and the sql/NNN-*.sql files, then run it with npm/node. Full runner, example migration files, and the operation downtime matrix: migration runner.

Output

Applying this skill produces:

• Executed ALTER statements or recreated-and-swapped tables, with mutations confirmed complete via system.mutations WHERE NOT is_done (empty result).
• Migration .sql files under migrations/sql/ (e.g. 001-create-events.sql) and a runner.ts that records each applied version in the _migrations table.
• Verification counts — row counts on the new/old tables matching before the old table is dropped.

Pre-Migration Checklist

• Backup production data (BACKUP TABLE ... TO S3(...))
• Test migration on staging with production-like data
• Check disk space (mutations create temporary extra parts)
• Schedule during low-traffic window (for heavy mutations)
• Prepare rollback procedure
• Verify mutation completes (system.mutations WHERE NOT is_done)

Examples

Add a column (instant, no data rewrite):

ALTER TABLE analytics.events
    ADD COLUMN IF NOT EXISTS country LowCardinality(String) DEFAULT ''
    AFTER user_id;

Change ORDER BY via table recreation and atomic swap:

CREATE TABLE analytics.events_v2 AS analytics.events
ENGINE = MergeTree()
ORDER BY (tenant_id, event_type, toDate(created_at))
PARTITION BY toYYYYMM(created_at);

INSERT INTO analytics.events_v2 SELECT * FROM analytics.events;

RENAME TABLE
    analytics.events TO analytics.events_old,
    analytics.events_v2 TO analytics.events;

More worked examples: column codecs and materialized defaults in column operations, the MergeTree → ReplacingMergeTree engine swap in table recreation, and end-to-end versioned files in migration runner.

Error Handling

Resources

• ALTER TABLE Reference
• Column Manipulations
• Schema Migration Tools
• Mutations
• For architecture patterns, see clickhouse-reference-architecture.