Part 193: Zero-Downtime Schema Migrations: The Expand & Contract Pattern

Altering tables with hundreds of millions of rows while serving live production traffic.

1. The Problem: Breaking Schema Changes

Renaming a column (ALTER TABLE users RENAME COLUMN phone TO phone_number;) instantly breaks all active web instances running older application code.

2. The 5-Phase Expand and Contract Pattern

Phase 1: Expand (Add new column without removing old)
Phase 2: Dual-Write (Application writes to both columns)
Phase 3: Backfill (Copy historical data in background chunks)
Phase 4: Switch Reads (Application reads from new column)
Phase 5: Contract (Drop old column)

3. Safe Schema Modification Checklist

-- ❌ DANGEROUS: Blocks entire table with AccessExclusiveLock
ALTER TABLE large_table ADD COLUMN status VARCHAR(20) DEFAULT 'active';

-- ✅ SAFE IN POSTGRESQL (Instant metadata change, no rewrite):
ALTER TABLE large_table ADD COLUMN status VARCHAR(20) DEFAULT 'active' NOT NULL;
-- (In Postgres 11+, non-null defaults are instant!)

-- ❌ DANGEROUS: CREATE INDEX locks writes
CREATE INDEX idx_user_email ON users(email);

-- ✅ SAFE: Builds index in background without blocking writes
CREATE INDEX CONCURRENTLY idx_user_email ON users(email);

Key Takeaways

• Never rename a column directly; expand with a new column, backfill, and contract the legacy column.

In Part 194, we explore Point-in-Time Recovery (PITR) & Disaster Recovery Runbooks!