Zero-Downtime Schema Migrations: The Expand-and-Contract Answer

A senior backend interview question I like: "You need to rename the users.name column to full_name on a table with 400 million rows, and the app can't go down. Walk me through it." Most candidates say "run ALTER TABLE at night". The interviewer wants the expand-and-contract pattern.

Why a single ALTER is dangerous

The five steps

  1. Expand: add full_name as a nullable column. Adding a nullable column without a default is a metadata-only change in modern Postgres and MySQL.
  2. Dual write: deploy code that writes both name and full_name but still reads name.
  3. Backfill: copy old rows in small batches (say 5,000 rows per transaction, keyed by primary key ranges), throttled by replication lag so replicas don't fall behind. Make the job idempotent and resumable.
  4. Switch reads: deploy code that reads full_name. Verify with a checksum or sampled comparison that both columns match. Stop writing name in a later deploy.
  5. Contract: once nothing references name (check query logs, not just the codebase), drop it.

Every step is independently deployable and reversible until the final drop. That is the property to emphasise.

Details that signal experience

How to wrap up the answer

Summarise the trade-off: expand-and-contract costs several deploys and temporary storage, but each step is safe and observable. Then mention how you would monitor it: replication lag, error rates, and a mismatch counter between the two columns during the dual-write phase.

Disclosure: I help build real-time interview help from Linkjob AI; this is from our backend system-design practice notes.