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. ## 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. ```sql -- Lightweight operations (instant, metadata only) ALTER TABLE events ADD COLUMN 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 DROP COLUMN deprecated_field; ALTER TABLE events DELETE WHERE user_id = 0; ALTER TABLE events UPDATE email = '' WHERE created_at < '2024-01-01'; -- Check mutation progress SELECT database, table, mutation_id, command, is_done, parts_to_do, create_time FROM system.mutations WHERE NOT is_done ORDER BY create_time; ``` ### Step 2: Column Operations ```sql -- Add a column (instant — no data rewrite) ALTER TABLE analytics.events ADD COLUMN IF NOT EXISTS country LowCardinality(Str...