How Do You Design a Zero-Downtime Database Migration?
Learn how expand-and-contract migrations, dual writes, and safe backfills deliver zero-downtime database schema changes.
Expected Interview Answer
A zero-downtime database migration succeeds by decomposing a risky single cutover into small, reversible, backward-compatible steps — expand the schema, dual-write or backfill, verify, then contract the old shape — so the application keeps serving traffic against a schema that is valid before, during, and after each step.
The core technique is the expand-and-contract pattern: first add new columns, tables, or indexes without removing anything old (expand), deploy application code that can read and write both the old and new shapes, backfill historical data in small batches to avoid locking, then once the new path is verified in production, remove the old columns or code paths (contract). Each phase is deployed and observed independently, so a failure at any point can be rolled back without an outage. Long-running schema changes on large tables use online tools that avoid holding exclusive locks, and every step is tested against production-like data volumes beforehand.
- No user-facing downtime during schema changes
- Each step is independently reversible
- Reduces blast radius of a bad migration
- Keeps old and new code paths compatible during rollout
AI Mentor Explanation
A ground staff resurfacing a cricket pitch mid-season cannot close the stadium for a month, so they work one section at a time, keeping the rest playable while the new surface cures next to the old one. Only once the new strip has passed inspection do they retire the old strip from use. A zero-downtime migration does the same: new schema structures are built alongside the old ones and only swapped in after they are proven, so matches — application traffic — never stop.
Step-by-Step Explanation
Step 1
Expand the schema
Add new columns, tables, or indexes without removing or renaming anything the current application depends on.
Step 2
Deploy dual-compatible code
Ship application code that writes to both old and new structures and can read from either, verified with feature flags.
Step 3
Backfill and verify
Migrate historical data in small batches during low-traffic windows and compare old vs new values for correctness.
Step 4
Contract the old schema
Once reads fully use the new structures and verification passes, remove the deprecated columns, tables, or code paths.
What Interviewer Expects
- Explanation of the expand-and-contract pattern
- Awareness of backward compatibility during rollout
- Mention of backfilling in batches to avoid long locks
- A concrete rollback plan for each migration step
Common Mistakes
- Trying to change schema and application code in a single atomic deploy
- Forgetting to backfill or verify data before removing old columns
- Running large ALTER TABLE statements that hold long locks in production
- No rollback plan if the new schema misbehaves under real traffic
Best Answer (HR Friendly)
“I break a risky migration into small, reversible steps: first add the new schema alongside the old one, update the application to write to both, backfill historical data safely, verify everything matches, and only then remove the old structure. That way the site never goes down and I can roll back at any single step if something looks wrong.”
Code Example
-- Step 1: Expand — add the new column, nullable, no lock on existing reads
ALTER TABLE Users ADD COLUMN full_name VARCHAR(255);
-- Step 2: App writes to both old (name) and new (full_name) columns.
-- Step 3: Backfill in batches to avoid long-running locks
UPDATE Users SET full_name = name
WHERE full_name IS NULL
LIMIT 1000;
-- repeat until zero rows remain
-- Step 4: Once reads fully use full_name and verification passes,
-- contract — drop the old column in a later deploy
ALTER TABLE Users DROP COLUMN name;Follow-up Questions
- What is the expand-and-contract pattern and why does it prevent downtime?
- How would you backfill a 500-million-row table without locking it?
- How do you handle a migration that needs to roll back mid-way?
- What is the difference between an online schema change and a blocking ALTER TABLE?
MCQ Practice
1. What is the primary goal of the expand-and-contract migration pattern?
Expand-and-contract keeps every intermediate state valid for the currently running application, avoiding downtime.
2. Why should a large backfill be run in small batches rather than one UPDATE statement?
Small batches keep individual transactions short, avoiding long lock contention that would stall live queries.
3. When is it safe to drop the old column in an expand-and-contract migration?
The contract phase only happens once the new structure is proven correct and nothing depends on the old one.
Flash Cards
What is expand-and-contract? — A migration pattern that adds new schema alongside the old, verifies it, then removes the old schema in a later step.
Why batch a backfill? — To avoid holding long locks on large tables that would block live application traffic.
What makes a migration step safe? — It leaves the schema in a state the currently deployed application can still read and write correctly.
When to contract the old schema? — Only after the new schema is fully adopted, verified, and no code path still depends on the old one.