ORIGINAL SYSTEM DESIGN QUESTION
Online schema migration
Migrate a large user table to a new schema while the application keeps writing.
Original exercise · advanced. Editorial role relevance does not imply a company has asked this question.
Requirements and constraints
- 500 million rows; 5,000 writes/second; a six-hour migration window.
- Rollback and validation must cover writes made during backfill.
- State assumptions and justify security, cost, operational ownership and data retention.
Pressure round
A backfilled old value arrives after a newer live update.
Build and explain the design
Requirements & scope
Who uses it? What must it do? What is explicitly out of scope? State latency, durability and privacy goals.
Self-review guidance
Define the actors, required operations and out-of-scope behavior. Explicit requirement: Rollback and validation must cover writes made during backfill.
Capacity estimates
State assumptions, peak requests/second, data volume and retention. Show your arithmetic and units.
Self-review guidance
Use this workload: 500 million rows; 5,000 writes/second; a six-hour migration window. Convert rates and payload sizes into peak compute/storage assumptions; show units, headroom and what remains unmeasured.
APIs & data model
Describe endpoints/events, entity keys, indexes, authorization, and idempotency.
Self-review guidance
Define old/new schema mapping, write versions and migration checkpoints.
Architecture & data flow
Describe components and request/event paths. Use plain text arrows to sketch your design; explain each responsibility.
Self-review guidance
Use staged dual writes or change-data capture with reconciliation.
Failures & observability
What happens on overload, duplicate events, partial failures or lost dependencies? Discuss monitoring and recovery.
Self-review guidance
Pressure-test: A backfilled old value arrives after a newer live update. Describe bounded recovery, deduplication, observability and a user-visible failure state.
Tradeoffs & alternatives
Compare two plausible approaches. State the costs, consistency model and what would make you change the design.
Self-review guidance
Compare version-aware upserts, cutover validation and rollback cost.
Design guidance describes assumptions and tradeoffs; no architecture has been simulated or benchmarked.
Practice this question
Save your work privately, explain decisions and return to your revision queue.
Open practice workspaceBrowse the question library →