Database Migration Strategy for Zero-Downtime Deployments

Running database migrations during deployment causes downtime. ALTER TABLE locks tables, new code expects new columns that don't exist yet, and rollbacks are dangerous.

Use expand-and-contract migration pattern:

Phase 1: Expand (backward-compatible)

• Add new columns as NULLABLE (no lock on reads)
• Create new tables
• Add new indexes CONCURRENTLY
• Deploy code that writes to BOTH old and new locations

Phase 2: Migrate data

• Backfill new columns from old data
• Run in batches to avoid locking

Phase 3: Contract (cleanup)

• Deploy code that reads from new locations only
• Remove old columns/tables in a separate migration
• This step can be done days/weeks later

Dangerous operations and safe alternatives:

• ALTER TABLE ADD COLUMN NOT NULL -> Add nullable, backfill, then add constraint
• ALTER TABLE ADD COLUMN DEFAULT x -> PG 11+ does this without rewriting table
• CREATE INDEX -> CREATE INDEX CONCURRENTLY
• ALTER TABLE RENAME COLUMN -> Add new column, dual-write, drop old
• DROP COLUMN -> Remove from code first, drop column later

Tools: Flyway, Alembic, golang-migrate, prisma migrate, Rails migrations

Old code and new code run simultaneously during rolling deployments. Migrations must be compatible with both versions, or you get errors during the transition window.

Managing database schema changes in production