Execute ClickHouse schema migrations — ALTER TABLE operations, data migration
between engines, versioned migration runners, and zero-downtime schema changes.
Use when modifying ClickHouse schemas, migrating data between tables,
or implementing versioned migration workflows.
Trigger: "clickhouse migration", "clickhouse ALTER TABLE", "clickhouse schema change",
"migrate clickhouse", "clickhouse add column", "clickhouse schema migration".
Execute ClickHouse schema migrations — ALTER TABLE operations, data migration
between engines, versioned migration runners, and zero-downtime schema changes.
Use when modifying ClickHouse schemas, migrating data between tables,
or implementing versioned migration workflows.
Trigger: "clickhouse migration", "clickhouse ALTER TABLE", "clickhouse schema change",
"migrate clickhouse", "clickhouse add column", "clickhouse schema migration".
Plan and execute ClickHouse schema migrations: column changes, engine migrations,
ORDER BY modifications, and versioned migration runners.
Prerequisites
ClickHouse admin access
Backup of production data (see clickhouse-prod-checklist)
Test environment for validation
Instructions
Step 1: Understanding ClickHouse DDL
ClickHouse ALTER operations are mutations — they run asynchronously and
rewrite data parts in the background. This is fundamentally different from
PostgreSQL/MySQL where ALTER is often instant or blocking.
-- Lightweight operations (instant, metadata only)ALTER TABLE events ADDCOLUMN country LowCardinality(String) DEFAULT'';
ALTER TABLE events RENAME COLUMN old_name TO new_name;
ALTER TABLE events COMMENT COLUMN user_id 'Unique user identifier';
-- Heavyweight operations (mutations — rewrite parts in background)ALTER TABLE events MODIFY COLUMN properties String CODEC(ZSTD(3));
ALTER TABLE events DROPCOLUMN deprecated_field;
ALTER TABLE events DELETEWHERE user_id =0;
ALTER TABLE events UPDATE email =''WHERE created_at <'2024-01-01';
-- Check mutation progressSELECT database, table, mutation_id, command, is_done,
parts_to_do, create_time
FROM system.mutations
is_done create_time;
WHERE
NOT
ORDER
BY
Step 2: Column Operations
-- Add a column (instant — no data rewrite)ALTER TABLE analytics.events
ADDCOLUMN IF NOTEXISTS country LowCardinality(String) DEFAULT''
AFTER user_id;
-- Add column with materialized default (fills new data, not old)ALTER TABLE analytics.events
ADDCOLUMN IF NOTEXISTS event_date Date
MATERIALIZED toDate(created_at);
-- Modify column type (mutation — rewrites all parts)ALTER TABLE analytics.events
MODIFY COLUMN user_id UInt64; -- Was UInt32, now UInt64-- Drop a columnALTER TABLE analytics.events
DROPCOLUMN IF EXISTS deprecated_field;
-- Change default valueALTER TABLE analytics.events
MODIFY COLUMN created_at DateTime DEFAULT now();
-- Add codec to existing column (mutation)ALTER TABLE analytics.events
MODIFY COLUMN properties String CODEC(ZSTD(3));
Step 3: Change ORDER BY (Requires Table Recreation)
ClickHouse does not support ALTER TABLE ... MODIFY ORDER BY. You must
create a new table and migrate data.
-- Step 1: Create new table with desired ORDER BYCREATE TABLE analytics.events_v2 AS analytics.events
ENGINE = MergeTree()
ORDERBY (tenant_id, event_type, toDate(created_at)) -- New keyPARTITIONBY toYYYYMM(created_at);
-- Step 2: Copy dataINSERT INTO analytics.events_v2 SELECT*FROM analytics.events;
-- Step 3: Atomic swap (zero-downtime if app handles reconnect)
RENAME TABLE
analytics.events TO analytics.events_old,
analytics.events_v2 TO analytics.events;
-- Step 4: Verify and drop old tableSELECTcount() FROM analytics.events;
SELECTcount() FROM analytics.events_old;
-- When satisfied:DROPTABLE analytics.events_old;
Step 4: Change Engine (MergeTree to ReplacingMergeTree)
-- Create new table with ReplacingMergeTreeCREATE TABLE analytics.users_v2 (
user_id UInt64,
email String,
plan LowCardinality(String),
updated_at DateTime DEFAULT now()
)
ENGINE = ReplacingMergeTree(updated_at)
ORDERBY user_id;
-- Migrate dataINSERT INTO analytics.users_v2 SELECT*FROM analytics.users;
-- Atomic swap
RENAME TABLE
analytics.users TO analytics.users_old,
analytics.users_v2 TO analytics.users;
DROPTABLE analytics.users_old;
-- migrations/sql/004-add-bloom-index.sqlALTER TABLE analytics.events
ADD INDEX IF NOTEXISTS idx_session session_id TYPE bloom_filter(0.01) GRANULARITY 4;
ALTER TABLE analytics.events MATERIALIZE INDEX idx_session;
Step 7: Migration Best Practices
Operation
Downtime?
Notes
ADD COLUMN
None
Instant metadata change
DROP COLUMN
None
Mutation runs in background
MODIFY COLUMN type
None*
Mutation rewrites — can be slow on large tables
Change ORDER BY
Brief
Requires table recreation + RENAME
Change ENGINE
Brief
Requires table recreation + RENAME
ADD INDEX
None
MATERIALIZE runs in background
ALTER TTL
None
Takes effect on next merge
*No application downtime, but queries on the affected column may be slower during mutation.
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)
