ALTER TABLE against a table with millions of rows freezes production: locks, queued queries, cascading timeouts. The expand-contract pattern splits every schema change into phases compatible with old and new code coexisting. One rule precedes the phases: schema changes and dependent code never ship in the same release. Old code must survive the new schema, and new code must survive the old schema through the whole transition.

Phase 1: expand

Additive changes belong in this phase. Add nullable columns, create new tables, build indexes with CREATE INDEX CONCURRENTLY so Postgres keeps serving writes. Backfill existing data in batches paginated by primary key, 5 thousand rows per batch; a long transaction causes replication lag and bloat, so small batches with pauses between them keep the cluster healthy. A failed index build leaves an invalid index behind; cleanup requires DROP INDEX CONCURRENTLY before the next attempt.

Phase 2: transition with dual-write

New code writes both shapes during the overlap: every operation hits the old structure and the new one. The read path migrates behind a feature flag, table by table. A nightly consistency checker compares both representations and reports divergence while there is still time to fix it. The phase lasts days or weeks, enough to cover rollbacks, lagging workers, and stale caches.

Phase 3: contract

Telemetry proved zero reads on the old structure? Remove deprecated columns and legacy write paths. Dropping a constraint takes staged steps across deploys: first out goes the code reading it, then the constraint itself, each step in its own deploy with a trivial rollback.

Which operations demand their own choreography?

  • Column type changes: new column, dual-write, backfill, read switch, drop the original
  • Column renames: the same sequence, with a view alias covering the transition window
  • New unique constraints: deduplicate data first, build the unique index CONCURRENTLY after

Tooling that holds the line

Squawk lints SQL migrations in CI and flags operations that lock tables. Rails apps install strong_migrations, which blocks dangerous changes at runtime and suggests the safe alternative. Heavy MySQL fleets run gh-ost or pt-online-schema-change: both copy the table with triggers and perform an atomic swap at the end. Close the loop by running migrations in CI against production-sized snapshots; an ALTER that passes locally with 100 rows explodes in staging with 50 million.