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
UPDATEacross 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
| Phase | What changes | Reversible? |
|---|---|---|
| Expand | New nullable column; app writes to both | Yes, ignore the new column |
| Backfill | Old rows copied in batches | Yes, stop the job |
| Switch reads | App reads the new column | Yes, flip the flag back |
| Contract | Old column no longer written, then dropped | Only after this point is it final |
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.
Top comments (0)