Zero-Downtime Database Migrations: The Expand and Contract Pattern

In early-stage prototypes, running database migrations is trivial: you run `ALTER TABLE` in a migration script, push your code, and let the database lock for three seconds while the app updates. But on a production system serving live paying customers, a locking migration will stall transactions, backup connection pools, and trigger cascading 504 Gateway Timeouts.

Achieving true zero-downtime continuous deployment requires writing backward-compatible database schema changes using the **Expand and Contract** (or Parallel Change) pattern.

The three phases of Expand and Contract

Instead of altering a column or table structure in a single destructive step, split the transition across three independent deployment phases:

1. 01Phase 1 (Expand): Add the new column or table as nullable or with a safe default. Deploy backend code that writes to BOTH the old and new columns simultaneously while still reading from the old column.
2. 02Phase 2 (Backfill): Run an asynchronous, throttled background script to copy historical data from the old column to the new column in small batches without locking the table.
3. 03Phase 3 (Contract): Deploy updated application code that reads and writes exclusively to the new column. Once verified, run a final migration to safely drop the old column and legacy triggers.

Common migration mistakes and their safe alternatives

Renaming a column

Unsafe: `ALTER TABLE users RENAME COLUMN name TO full_name;` (crashes old app instances during deploy). Safe: Add `full_name`, dual-write, backfill, cut over.

Adding NOT NULL without default

Unsafe: Locks table while rewriting every row. Safe: Add nullable column, backfill data, add a `CHECK` constraint with `NOT VALID`, then validate asynchronously.

Creating an unindexed foreign key

Unsafe: Can cause table-level locks during cascade checks. Safe: Create foreign key with `CONCURRENTLY` or separate index creation step.

If your deployment requires taking your application offline for maintenance mode, your database schema changes are tightly coupled to your application code.

Checklist for safe production migrations

• Always create new indexes using `CREATE INDEX CONCURRENTLY` in PostgreSQL to prevent table read/write locks.
• Set aggressive statement timeouts (`SET statement_timeout = '2s';`) on migration transactions so slow migrations fail fast rather than blocking connections.
• Throttled backfills: sleep 50ms between updating batches of 1,000 rows to prevent IOPS saturation on production databases.
• Never execute long-running data transformations inside the same transactional block that alters DDL schemas.