Zero-downtime database migrations: the expand and contract answer for interviews

A zero-downtime database migration changes the schema of a live database without blocking users. For large, busy tables, the standard approach is expand and contract: add the new structure next to the old one, move data and traffic across in safe steps, and remove the old structure last.

Interviewers ask it like this: "We need to change a column on a table with hundreds of millions of rows, without any downtime. How would you do it?"

Step 1: Explain why the naive approach is risky

On a small table, ALTER TABLE ... followed by UPDATE ... is fine. On a huge, busy one:

• Some ALTER operations take strong locks or rewrite the entire table, blocking reads and writes while they run (PostgreSQL ALTER TABLE).
• A single UPDATE across hundreds of millions of rows runs as one huge transaction: it holds locks, bloats logs and makes replicas fall behind.
• If something goes wrong halfway, rolling back is slow and painful.

Starting with this shows you understand the problem, not just the recipe.

Step 2: Expand

Add the new structure alongside the old one, without breaking anything:

• Add the new column as nullable, without a default that forces a table rewrite.
• Deploy application code that writes to both the old and new columns on every insert and update.
• Keep reading from the old column. Nothing user-facing changes yet.

Step 3: Backfill in batches

Now copy existing data into the new column:

• Process small batches, for example a few thousand rows, ordered by primary key.
• Only update rows where the new column is still empty, so the job can be stopped and re-run safely, and doesn't overwrite fresh values written by the app.
• Pause between batches and watch replication lag and database load. Slow down if they climb.
• Make the job resumable, so a crash at row 80 million doesn't mean starting over.

Step 4: Switch reads and verify

• Compare old and new values with a verification query or sampling job until they match.
• Switch reads to the new column, ideally behind a feature flag so you can switch back instantly.
• Keep dual writes running. The old column is your safety net.

Step 5: Contract

Only when you're confident:

• Stop writing to the old column.
• Drop it in a later release, after checking nothing still reads it.
• Throughout every step, set a lock timeout on schema changes so they fail fast instead of queueing behind long transactions and blocking everything.

Tools like pgroll for PostgreSQL or pt-online-schema-change for MySQL automate parts of this, and are worth mentioning as options.

The steps at a glance

The one-line answer

"I'd use expand and contract: add the new column, dual-write, backfill in batches while watching replication lag, switch reads behind a flag after verifying, and drop the old column in a later release."

Follow-up questions to expect

• "What about renaming a column?" Same pattern: a rename is "add new, copy, switch, remove old", never an in-place rename on a live system.
• "How do you handle writes during the backfill?" Dual writes keep new data in sync; the backfill only fills rows still empty, so it never overwrites them.
• "How would you add an index without locking?" In PostgreSQL, CREATE INDEX CONCURRENTLY, which avoids blocking writes at the cost of a slower build.

For more backend interview practice, see how to scale a database and idempotency keys explained.

Frequently asked questions