The Database Migration Interview: A Senior and Staff Engineer Playbook

Your interviewer draws an old database and a new database, then asks: “How would you move production traffic without losing writes?” You propose replication, a backfill, and a feature flag. The follow-up arrives immediately: “The backfill crashes after a customer updates a record. Which value wins?”

This is a useful practice problem for senior and staff engineers because the difficult part is the transition. You must explain which system owns each write, how mixed application versions behave, and what operators can safely undo. This guide offers a rehearsal framework, not a claim that every employer asks migration questions or scores them identically.

The evidence comes from published engineering experience and database documentation. Stripe described migrating hundreds of millions of subscription objects. Discord reported copying messages at up to 3.2 million per second. Those are historical case studies, not throughput promises for your architecture. Their value is showing the decisions behind the numbers.

1. Frame the Migration Before Choosing a Tool

Start by identifying what changes. Adding a column, splitting a table, moving tenants between shards, and replacing a database engine have different failure surfaces. Ask about the database version, data volume, mutation rate, largest tenant, acceptable latency, and whether applications can tolerate a brief write pause. Clarify whether “zero downtime” means no failed requests, no maintenance window, or no blocked writes whatsoever.

The distinction is concrete: Shopify’s shard-balancing account includes stopping source writes during cutover. A design can hide a short pause through request handling without making the database continuously writable. State the product requirement before adopting the marketing phrase.

For practice, use this fictional prompt: move an orders service containing 500 million rows to a new schema while processing 2,000 mutations per second. Customers must retain read-after-write behavior, and acknowledged orders must survive. These numbers are exercise assumptions. Ask whether a single transaction can cover both representations; the answer determines whether ordinary transactional writes or a separate replication mechanism is appropriate.

Close your opening with a definition of success: preserve acknowledged writes, maintain agreed request latency, and demonstrate equivalent business behavior before retiring the old representation. Then name the largest unknown. A candidate who asks whether mobile clients write directly to the old API exposes a dependency that a tidy database diagram can hide.

2. Put Invariants and Ownership on the Whiteboard

Write three invariants in plain language: an acknowledged order is never lost; an older update never overwrites a newer update; and each tenant has one authoritative write destination. Add a fourth if deletion matters: a copied record cannot resurrect an order already deleted. These statements give you a way to evaluate every subsequent implementation choice.

Draw a phase table with columns for authoritative writes, serving reads, synchronization, and recovery. During preparation, the old store owns both reads and writes. During copying, it still owns writes while the target receives changes. During read rollout, selected reads use the target. Only after a controlled ownership transfer should the target accept authoritative writes.

Amazon’s rollback-safety guidance explains why old and new software must understand intermediate data formats. Apply that principle to your phase table: a rollback deployment must still read records produced by the newer deployment. Merely keeping yesterday’s binary available does not establish recoverability.

For staff-level rehearsal, add the teams that own each transition. Who deploys readers? Who owns background jobs? Who approves the final cutover? Your proposed distinction is scope, not a universal hiring rubric: demonstrate that you can make a technically sound change executable across services with different release schedules.

3. Separate Schema Expansion From Destructive Cleanup

Introduce the new representation before requiring every reader to use it. For a renamed field, that might mean temporarily supporting both names. For a table split, create the destination and deploy compatible access code first. Keep destructive cleanup in a later release, after observing that old readers and writers have disappeared.

Stripe’s published sequence separates dual writing, changing reads, changing writes, and removing old data. Use the sequence as a starting point, then explain the transaction boundary in your own scenario. It is a migration pattern, not proof that any two independent writes are safe.

Show engine-specific judgment without turning the interview into a syntax recital. In PostgreSQL 16, a supported foreign-key or CHECK constraint can be added as NOT VALID, deferring the scan of existing rows while enforcing the constraint for subsequent inserts and updates. Validation can follow separately. This does not mean the initial operation takes no locks.

Similarly, PostgreSQL’s CREATE INDEX CONCURRENTLY allows writes during an index build, but carries operational caveats. Ask about the exact engine and version before proposing production SQL. Your interview answer should identify the risk category: blocking lock acquisition, expensive scanning, incompatible readers, or irreversible transformation.

A useful response to “Why not one deployment?” is: “I want independently observable transitions so a compatibility defect does not require us to reverse schema changes and application behavior simultaneously.” You are making an argument about failure isolation, not advocating ceremony for every small table.

4. Explain How Writes Survive Partial Failure

“Dual write” is an incomplete answer until you explain what happens when only one destination succeeds. If both representations live inside the same transactional database, a shared transaction may provide the needed atomicity. Across independent stores, explain how a committed source mutation becomes a durable, replayable change before calling the operation safe.

One possible design is an outbox written in the same source transaction as the business record, followed by an asynchronous consumer. Another uses the database change log. These are alternatives to evaluate against the prompt, not interchangeable guarantees. Specify replay behavior, record identity, ordering scope, and how failed events reach an operator.

GitHub’s gh-ost uses the MySQL binary log to track changes during online schema migration. That is a concrete example of log-based synchronization; it is not a general cross-engine migration solution. Its documented requirements include a shared primary or suitable unique key, so verify fit before recommending it.

Prepare for duplicate delivery. PostgreSQL documents that a logical slot can resend recent changes after a crash. Propose idempotent application or a durable deduplication scheme rather than assuming every event arrives once. A version check also needs defined ordering semantics; timestamps alone may be ambiguous when writers have different clocks.

Now answer the opening question: if the backfill carries version 12 and the live stream has already applied version 13, the destination must reject the stale overwrite under the chosen version scheme. Explain deletes with the same care: a tombstone or equivalent ordering mechanism must prevent an older copied row from reappearing.

5. Make the Backfill Resumable and Calculate Its Budget

Treat a backfill as a production workload with a budget. Describe bounded batches, stable iteration keys, committed checkpoints, retry policy, and a throttle tied to user-facing health. A checkpoint should mean the corresponding target work is durably complete, not merely that a worker has read the source rows.

For the fictional 500-million-row exercise, an effective copy rate of 20,000 rows per second gives 25,000 seconds, or about 6.9 hours. That is an arithmetic lower bound assuming constant throughput and no pauses. It excludes verification, retries, indexes, and catch-up. Say so before turning the calculation into a schedule.

Use a second calculation for the change stream. If it receives 2,000 events per second and applies 5,000, a backlog of nine million events takes roughly 3,000 seconds, or 50 minutes, to drain at a constant net rate of 3,000. If apply capacity falls below arrival rate, waiting longer cannot complete catch-up. Keep events and rows distinct; a mutation can generate multiple events.

Discord’s migration used checkpoints and encountered a final tail of troublesome token ranges. The interview lesson is to report the slowest partition and remaining work, not only an aggregate percentage. A completion bar can conceal the hardest remaining segment.

PostgreSQL also warns that replication slots retain required WAL and catalog resources. Include storage headroom and consumer lag in your plan. An intentionally paused consumer must not silently exhaust the source database. Explain which threshold pauses copying, which pages an operator, and who can resume.

6. Prove Correctness Beyond Matching Row Counts

Equal counts can hide a missing row and an extra row. Propose validation in layers: key coverage, normalized record comparisons, business invariants, and production read behavior. For orders, compare identities, currency, totals, status transitions, and deletion semantics. Choose normalization rules explicitly so a timestamp representation difference does not conceal a meaningful monetary mismatch.

AWS DMS describes source-to-target row validation and notes the extra resources it consumes. Treat verification as work that needs capacity and scheduling. If the data keeps changing, comparisons also need a consistent boundary or an understood delay; otherwise a legitimate in-flight update looks like corruption.

GitHub’s Scientist library compares a candidate code path with a control path. That illustrates shadow-read validation: serve the trusted result while comparing the candidate result separately. Stripe used Scientist to compare reads before switching to its new table. Explain how you avoid duplicate side effects; shadowing a read is different from replaying a purchase.

Sampling is useful but cannot prove universal equivalence. Include rare tenants, old records, unusually large objects, and recently deleted records. Keep a list of mismatch categories and investigate unexplained differences before expanding traffic. Do not declare success by averaging a small corrupt cohort into a large healthy population.

Also validate things outside the obvious rows. PostgreSQL 16 logical replication does not replicate schema changes or sequence state. A destination can pass record comparisons and still fail its first independent insert. Ask about sequences, constraints, jobs, permissions, and all consumers required to make the target operational.

7. Describe the Exact Cutover Boundary

The interviewer may now say: “Everything looks healthy. Flip the flag.” Resist compressing ownership transfer into one unexplained verb. State what stops new source writes, what happens to in-flight requests, how the final committed change is identified, and when the destination becomes authoritative. Specify how stale routers or queued jobs are prevented from writing to the old store.

Shopify records a final source binlog coordinate and drains changes to that coordinate during cutover. This is stronger evidence than “replication lag looks near zero.” Your proposed migration should similarly distinguish a monitored lag estimate from proof that all changes before a known boundary have arrived.

A tenant-based rollout can limit the blast radius if tenants are genuinely isolated. Choose a small, representative cohort, establish a watch period, and expand after checking errors, latency, mismatches, and operational load. If transactions cross tenant boundaries, explain why that rollout unit may be invalid. A convenient flag does not create an isolation boundary.

GitHub describes gh-ost’s controllable migration behavior, including postponing cutover. Borrow the operational principle: finishing the copy should not force an immediate traffic switch. Operators need an explicit readiness gate and a way to hold safely while dependent teams complete their checks.

Finish this section with a short decision statement: “We cut over only after the final change boundary is applied, validation passes, and the destination can sustain the selected cohort. Otherwise we remain in the current phase.” The answer makes a decision rule inspectable.

8. Separate Read Rollback From Write Recovery

A read-only traffic experiment can often return to the old store while synchronization continues. After the target owns writes, the old store may be stale. Sending traffic backward can lose acknowledged updates unless reverse synchronization or another tested recovery mechanism preserves them. State this boundary before promising a one-click rollback.

Amazon’s rollback guidance emphasizes preparing compatible readers before introducing a new data format. Apply the idea to feature launches too. If a new schema supports multiple values where the old schema supports only one, postponing that feature can preserve a recovery option during the migration window.

Rehearse three responses: pause a backfill when user latency rises; revert a read cohort when comparisons fail; and execute the defined write-recovery procedure after ownership transfer. These are different actions with different preconditions. A staff candidate can additionally explain who declares the incident and who owns cross-service recovery.

Delay removal of the old representation until its recovery purpose has expired and remaining consumers are accounted for. Set a named owner and completion criteria for cleanup. “Keep both forever” avoids making the retirement decision but creates ongoing cost and ambiguity. Make that cost visible alongside the reliability benefit.

Consider a concrete recovery drill for the fictional orders service. The new writer accepts an order, then its region becomes unavailable. Ask the practice interviewer whether the recovery objective permits waiting for that region or requires serving elsewhere immediately. If the old database has not received the order, switching back violates your acknowledged-write invariant. Explain what additional replication, fencing, and failover evidence would be necessary before promising immediate recovery.

Write down the human procedure as well: the incident lead freezes further rollout, the database owner confirms the last durable position, and the application owner checks routing and retries. A second engineer verifies the chosen recovery destination before traffic moves. These are proposed exercise responsibilities, not claims about a particular company's process. Their purpose is to make your recovery story testable: a teammate should be able to describe what they would observe and which action they would take without inventing missing steps.

9. Rehearse a 40-Minute Migration Interview

Use this suggested practice schedule: five minutes for requirements and invariants, eight for phases and compatibility, ten for copying and write ordering, seven for validation and cutover, and ten for adversarial follow-ups. It is a training exercise, not an employer’s published interview format. Ask a partner to interrupt whenever you use “safe,” “atomic,” or “rollback” without explaining the mechanism.

Try these follow-ups aloud: the target rejects one record; a worker retries a completed batch; a delete races with copying; a long transaction blocks an index operation; a sequence starts behind existing IDs; the new database fails after receiving authoritative writes. For each, name the invariant at risk, the detection signal, and the recovery action.

Two documented details make useful flashcards. A failed concurrent PostgreSQL index build can leave an invalid index, and concurrent builds cannot run inside a transaction block. Sequence state requires separate attention when a subscriber will become writable. Explain why each changes the plan instead of memorizing the sentence.

Finally, prepare one truthful experience story. Describe your actual scope, the unsafe alternative you rejected, the evidence you collected, and the outcome you measured. If you have not led a large migration, say so and use a clearly labeled practice design. You can demonstrate reasoning without borrowing another company’s scale as your own accomplishment.

For adjacent preparation, use our system-design tradeoff guide and architecture-review interview guide. Here, your strongest closing sentence answers a narrower question: which conditions must hold before the next transition, and what happens if they do not?