Design zero-downtime database migrations
PremiumCloud infrastructureMedium
- Expand and contract
- Online schema change
- Backfill
Quick answer
This cloud infrastructure interview question asks you to design zero-downtime database migrations. Aim for a design that runs reliably and securely in the cloud at a sensible cost, then explain the services, regions, deployment and trade-offs. Interviewers want to see how you balance reliability, security and cost.
The problem
Design a zero-downtime database migration system: the workflow and tooling that change schemas on live production tables while the app keeps serving. The hard parts are locks that stall traffic, old and new app versions running at once, and backfilling hundreds of millions of rows without hurting latency.
An example change: split users.name into first_name and last_name, and add an index on orders(customer_id, created_at), on a sharded database taking writes all day.
What must the system do?
- Migrations as code: Migrations live in the repository, are reviewed, are checked for dangerous operations and are run by the pipeline, never by hand.
- Expand and contract: Every breaking change becomes compatible steps: add the new schema, write to both, backfill, switch reads, stop old writes, drop the old schema.
How well must it do it?
- No downtime: p99 query latency rises by no more than 20% during a migration, and no statement waits more than 2 s for a lock.
- Compatibility: At every moment the schema works with both the current and the previous app version.
What scale should you design for?
Use these numbers in your estimates.
The scale numbers unlock with this questionUnlock
Key concepts
- Expand and contract
- Online schema change
- Backfill
- Lock timeout
- Dual writes
- PostgreSQL
How you’ll practise
- Requirements
- Environments and topology
- Deployment pipeline
- Reliability and disaster recovery
- Observability and cost
- Wrap-up
Lala, personalized to you, gives a hint when you ask and reviews your design when you are ready.
What do interviewers look for?
In any cloud infrastructure design round.
- Clarifies availability, security and cost targets
- Chooses managed services with reasons
- Plans regions, zones and failover
- Covers identity, secrets and network boundaries
- Explains deployment, rollback and observability
- Keeps an eye on cost as usage grows
Questions people ask about this interview question
- Which design type is this question?
- It is a cloud infrastructure question. You design zero-downtime database migrations and explain your choices the way you would in an interview.
- Is this question asked at entry level?
- It is marked Medium. Entry-level engineers can practise it, but it expects you to reason about requirements and trade-offs, not only list components.
- What should I cover first?
- Start with the workloads, the availability target and the security and cost limits. Agree on scope with the interviewer before you design anything.
- Can I practise it for free?
- It is a premium question. Sign up and unlock it once for 10 credits; it then stays unlocked. When you finish, Lala, your personalized AI mentor, can review your design for 30 credits.
Related questions
- Design an application health check systemCloud infrastructure · EasyPremium
- Design a distributed software release orchestration platformCloud infrastructure · HardPremium
- Design a multi-cloud disaster recovery orchestration platformCloud infrastructure · HardPremium
- Design a global infrastructure change approval & risk assessment platformCloud infrastructure · HardPremium
Updated · Written by the SevyDevy team