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".
Instalar com Codex ou Claude Copie este prompt, cole no Codex, Claude ou outro assistente e deixe que ele revise a página da skill e instale para você.
Um comando direto ignora o prompt de revisão. Verifique a origem antes de executá-lo.
Instruções da origem · Visualização somente leitura
name
clickhouse-migration-deep-dive
description
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)
