Pattern: Database migration strategy with zero downtime

Database schema changes can cause downtime if the new code expects a schema that hasn't been applied yet, or if migrations lock tables.

Use expand-contract pattern for zero-downtime migrations:

Phase 1: EXPAND (backward compatible)

• Add new columns (nullable or with defaults)
• Create new tables
• Add new indexes CONCURRENTLY
• Deploy code that writes to both old and new columns

Phase 2: MIGRATE DATA

• Backfill new columns from old data
• Verify data consistency

Phase 3: CONTRACT (remove old)

• Deploy code that only uses new columns
• Drop old columns/tables in a later migration

Rules:

• Never rename columns in one step (add new, migrate, drop old)
• Never add NOT NULL without a default value
• Use CREATE INDEX CONCURRENTLY (PostgreSQL)
• Keep migrations small and reversible
• Run migrations separately from code deploys

Example (rename column):
Step 1: ALTER TABLE users ADD COLUMN full_name TEXT;
Step 2: UPDATE users SET full_name = name;
Step 3: Deploy code using full_name
Step 4: ALTER TABLE users DROP COLUMN name;

The expand-contract pattern ensures the database schema is compatible with both old and new code at every point during deployment.