How it works
Define the availability and correctness requirements first: can requests briefly queue, how much replication lag is acceptable, and which invariants must always hold? Zero downtime means no planned service outage, not zero locks, extra latency, or migration risk.
Expand before you contract. For a column rename, add the new column rather than immediately renaming or dropping the old one. Deploy code that works with both schemas, and wait until old application instances, workers, and scheduled jobs no longer depend on the previous write behavior.
Keep new writes compatible while backfilling existing rows. Within one database, update both representations in the same transaction when possible; across databases, use a durable change stream rather than assuming two independent writes will both succeed.
Backfill in small, resumable batches with checkpoints and rate limits. Make retries safe, and prevent an older copied value from overwriting a newer application update through conditional updates, source versions, or correctly ordered replay. For a database move, establish a consistent snapshot and a matching change-log position so no updates fall between copying and capture.
Validate before switching reads: compare key coverage, transformed values, business invariants, and change-stream lag. Roll out new reads gradually with a feature flag and compare old and new results where practical; row counts alone do not prove correctness.
Cut over with explicit ownership. For a database move, fence old writers, drain changes through a known position, and enable the new write authority; requests may need brief queuing within the latency budget. Keep a rollback path, then remove the old schema or store only after the rollback window and compatibility checks pass.
A worked example
Renaming a column on a busy table
Suppose a table has 100 million rows and must replace display_name with public_name. Add public_name as nullable, then deploy code that reads display_name and updates both columns in one transaction. Wait until all old writers have drained. Backfill with an atomic statement that sets public_name from the current display_name only where public_name is null, so it cannot overwrite a live dual-write update. At an illustrative 5,000 rows per second, the scan takes at least about 5.6 hours; throttle it when production latency rises. Validate missing values and mismatches, then gradually switch reads. Continue writing both columns during the rollback window. Stop using and drop display_name only in later deployments.
Trade-offs to say out loud
- Extra capacity during migration
- Copies, additional indexes, change logs, and duplicate representations consume storage, I/O, and write capacity until cleanup finishes.
- Migration speed versus production latency
- Faster backfills shorten the transition but can increase lock contention, replication lag, and foreground request latency.
- Compatibility versus implementation complexity
- Supporting two representations lets application versions coexist but adds temporary code paths and requires a clear source of truth.
- Online DDL still has operational risks
- An online schema operation can still require metadata locks or expensive validation, so its behavior depends on the database version and exact change.
- Rollback becomes harder after new writes
- Once only the destination accepts writes, switching back safely requires reverse synchronization or reconciliation rather than just changing routing.
- Strict cutover can affect latency
- A single-writer handoff may require briefly fencing or queuing writes, so a no-outage claim must be checked against request deadlines and queue capacity.
When to reach for it
- The interviewer asks how to rename a column, change a type, add a constraint, or build an index on a busy production table.
- A large dataset must move between databases or storage systems while writes continue.
- Shards must split or move between nodes without losing updates or creating two authoritative owners.
- A rolling deployment must support old and new application versions against the same database.
- The design needs an explicit migration, validation, cutover, and rollback plan rather than a one-time bulk copy.
How to bring it up in the interview
- Start with the invariant: every acknowledged write must survive, and there must be one authoritative write path at cutover; interviewers often probe the exact handoff.
- Describe expand, migrate, and contract as separate phases, with the deployment and validation gates between them.
- Explain one concurrent-update race concretely: a backfill reads an old value, a live write changes it, and the backfill must not overwrite the newer value.
- State measurable pause conditions such as elevated request latency, growing replication lag, or validation mismatches, and explain how the migration resumes from checkpoints.
- Define rollback before and after write cutover separately; expect questions about destination-only writes, destructive transformations, and when old data can be deleted.
Common mistakes
- Renaming or dropping a column before all application versions and background jobs stop using it.
- Running a single massive update or assuming an operation labeled online cannot block production traffic.
- Copying a changing dataset without a consistent snapshot-to-change-stream boundary or protection against stale overwrites.
- Treating independent dual writes as atomic, or ignoring deletes and duplicate events during change replay.
- Declaring success from matching row counts or assuming rollback is just a routing change after the destination has accepted new writes.
Where it comes up
Interview questions where zero-downtime migrations is part of a strong answer, and why.
- Design a large data migration to the cloudThe core challenge is copying a changing dataset, validating it independently, and completing a fenced cutover without losing acknowledged writes.
- Design a distributed key-value storeMoving partition ownership requires transferring existing data and concurrent writes before safely handing authority to the new owner.
- Design a control plane for a distributed databaseScaling and upgrade workflows need resumable migration steps, compatibility checks, and safe ownership transitions while databases remain available.
Questions people ask
How do you perform a zero-downtime database schema migration?
Use expand, migrate, and contract: add a compatible schema, deploy compatible application code, backfill and validate data, then switch reads and remove the old schema later. Check the exact DDL operation for locks, table rewrites, and database-specific restrictions before running it.
What is the difference between online schema migration and zero-downtime migration?
Online schema migration changes a schema while reads and writes continue, using native database capabilities or a shadow-copy approach. Zero-downtime migration is the broader application-level process, including compatible deployments, data correctness, traffic switching, and rollback. Online DDL alone does not guarantee uninterrupted service.
How do you keep data consistent during a live database migration?
Start from a consistent snapshot tied to a change-log position, then replay subsequent changes with retry-safe handling of updates and deletes. Prevent stale snapshot data from overwriting newer changes, and validate destination contents independently. At cutover, fence the old write authority and drain through a known position before accepting writes on the destination.