Migration Patterns

• For large tables: use INSERT INTO ... SELECT with batching

Zero-Downtime Migrations

1. Create new table with desired schema
2. Create materialized view to capture new inserts: CREATE MATERIALIZED VIEW mv TO t_new AS SELECT ... FROM t_old
3. Backfill historical data: INSERT INTO t_new SELECT ... FROM t_old
4. Verify data consistency
5. Switch application to new table
6. Drop old table and materialized view

Data Backfill Patterns

• Batch by partition: INSERT INTO new SELECT * FROM old WHERE toYYYYMM(date) = 202301
• Use max_insert_block_size and max_threads for throughput control
• Monitor with system.processes and system.merges
• Verify row counts match after backfill

Partition Operations

• ALTER TABLE t ATTACH PARTITION id FROM other_table — zero-copy if same structure
• ALTER TABLE t REPLACE PARTITION id FROM other_table — atomic swap
• ALTER TABLE t MOVE PARTITION id TO TABLE other_table — move data

Common Pitfalls

• Nullable to non-Nullable requires default value for existing NULLs
• Changing ORDER BY requires table recreation
• Mutations (UPDATE/DELETE) rewrite all parts — schedule off-peak
• Test migrations on staging with production data volumes