Zero-Downtime Database Migrations at Scale

Why Migrations Cause Downtime

Most migration-related downtime comes from three sources:

1. Table locks - ALTER TABLE acquires an exclusive lock that blocks reads and writes
2. Long-running transactions - migrations that touch every row hold locks for minutes
3. Application/schema mismatch - deploying code that expects the new schema before the migration runs (or vice versa)

Each has a solution. None requires a maintenance window.

The Expand/Contract Pattern

The safest approach to any breaking schema change is to split it into three phases:

Phase 1 - Expand: Add the new column/table alongside the old one. Both old and new code work.

-- Safe: adding a nullable column never locks
ALTER TABLE orders ADD COLUMN customer_ref_id UUID;

Phase 2 - Migrate: Backfill data in batches. Never update all rows in a single transaction.

-- Backfill in batches of 1000 to avoid long-running locks
UPDATE orders SET customer_ref_id = customer_id::UUID
WHERE id BETWEEN $1 AND $2 AND customer_ref_id IS NULL;

Phase 3 - Contract: Once all code uses the new column and the old one is empty, drop it.

ALTER TABLE orders DROP COLUMN customer_id;

Online Index Builds

PostgreSQL 12+ supports CREATE INDEX CONCURRENTLY - it builds the index without blocking writes. The trade-off: it takes longer and can fail if a concurrent write violates the index constraint.

CREATE INDEX CONCURRENTLY idx_orders_customer_ref
ON orders (customer_ref_id)
WHERE customer_ref_id IS NOT NULL;

Always use CONCURRENTLY on production tables. Always.

Deployment Order Matters

The correct deployment sequence for a column rename:

1. Deploy code that writes to both old and new columns
2. Run migration to add new column and backfill
3. Deploy code that reads from new column only
4. Run migration to drop old column

Reversing steps 2 and 3 is the most common cause of migration-related incidents.