migration-safety

# Skill: Migration Safety > **Expertise:** Expand-and-contract, `CREATE INDEX CONCURRENTLY`, migration timing estimation, rollback planning. ## When to load When planning or executing a production database migration, estimating migration duration, or writing rollback SQL. ## Expand-and-Contract Pattern ```sql -- ❌ DANGEROUS: direct rename locks table and breaks old app version ALTER TABLE orders RENAME COLUMN user_id TO customer_id; -- ✅ SAFE: expand-and-contract over multiple deploys -- Phase 1: EXPAND (add new column, keep old) ALTER TABLE orders ADD COLUMN customer_id BIGINT; -- Phase 2: DUAL-WRITE (app v2 writes to both; reads from customer_id) -- (code change, no migration needed) -- Phase 3: BACKFILL (run in small batches to avoid lock) UPDATE orders SET customer_id = user_id WHERE customer_id IS NULL AND id BETWEEN <batch_start> AND <batch_end>; -- Phase 4: CONTRACT (app v3 no longer uses user_id) ALTER TABLE orders DROP COLUMN user_id; ``` ## Lock-Safe DDL ```sql -- ✅ Safe: CREATE INDEX CONCURRENTLY (no table lock) CREATE INDEX CONCURRENTLY idx_orders_customer_id ON orders(customer_id); -- If concurrent creation fails: DROP INDEX CONCURRENTLY idx_orders_customer_id_invalid; -- Then retry -- ❌ Dangerous on large tables: full table lock CREATE INDEX idx_orders_customer_id ON orders(customer_id); -- ✅ Safe: ADD COLUMN with no default (instant in PostgreSQL 11+) ALTER TABLE orders ADD COLUMN processed_at TIMESTAMPTZ; -- ❌ Dangerous: ADD COLUMN with DEFAUL...