Database and service migration strategies

The Problem Statement

Interviewer: "Your team needs to migrate a 500GB relational database from self-hosted MySQL to managed Postgres, and a monolith service to a new microservice, both without downtime. Walk me through how you would approach this."

This is a classic systems design question that appears at senior and staff engineer interviews. It tests whether you think in terms of reversible steps, risk management, and incremental delivery rather than heroic big-bang cutovers. The interviewer is not just looking for the technical patterns (dual write, expand-contract) but also whether you understand why they are necessary: every migration in production is a live surgery on a system that cannot be stopped.

The phrase "without downtime" is the constraint that forces rigor. Without it, you could briefly stop writes, copy data, and restart. With it, you need a different set of patterns entirely.

What the interviewer is really evaluating

The migration question is a proxy for how you will behave when a real high-stakes migration lands on your team. The interviewer wants to see:

1. Do you identify rollback first or design forward first? (Rollback first is senior behavior.)
2. Do you understand why dual write must be asynchronous-failure-tolerant?
3. Do you name specific metrics that define "the migration is ready" vs "the migration is guesswork"?
4. Do you know when NOT to use big bang? (The answer is: always, except for brand new systems with no data.)

A candidate who says "I would do dual write" without explaining the failure mode (what if the new write fails?), the health signal (shadow read discrepancy rate), or the off-ramp (feature flag rollback in milliseconds) is giving a mid-level answer to a staff-level question.

---

Clarifying the Scenario

Before diving into techniques, I clarify the constraints.

You: "A few questions before I outline the approach. How closely coupled are the MySQL schema and the Postgres migration? Can we do them sequentially or are they tied together?"

Interviewer: "The service migration from monolith to microservice is independent of the database migration. Let's focus on the database migration first."

You: "And for the database migration, is the Postgres schema identical to MySQL, or are we also changing the schema as part of this work?"

Interviewer: "We need to rename a few columns and denormalize one table with lots of joins. So the schema is different."

You: "Got it. And what's the RPO? If something goes wrong, can we tolerate losing five minutes of writes, or do we need zero data loss?"

Interviewer: "Zero data loss. We cannot lose any transactions."

You: "Perfect. Then I will structure my answer around four phases: first, the strangler fig setup to route traffic; second, dual-write to keep both databases current; third, expand-contract to handle the schema differences; and fourth, phased traffic shifting to move reads and writes gradually with canary verification."

That framing signals that I know the standard migration playbook and I am going to explain not just what the patterns are, but why each one exists and what risks it mitigates.

---

My Approach

I think about production migrations as a progression of reversible phases. The key invariant I maintain throughout is: at every point, I can roll back to the previous state by changing a feature flag. This is the constraint that forces all the patterns.

The five patterns I use together:

1. Strangler fig: Route incoming traffic to the new system gradually. The old system still handles what has not been migrated. Useful for service migrations.
2. Dual write: Write to both old and new systems on every incoming write. Keeps both systems in sync during the transition window. The old system is authoritative for reads.
3. Shadow reads: Read from both systems in parallel, compare the results, and serve the old system's result. Catches discrepancies before they affect production.
4. Expand-contract: Handle schema differences by adding the new structure first, backfilling, switching reads, then removing the old structure. Never removes anything in use.
5. Phased traffic splitting: Gradually increase the percentage of traffic going to the new system (1%, 5%, 10%, 50%, 100%), monitoring error rates and latency at each step.

These patterns are not independent: a real migration uses several at once. Understanding how they compose is the answer the interviewer is looking for.

The migration phases in practice

A complete migration using all five patterns looks like this in calendar time:

The eight weeks can compress to four for simpler migrations (no schema change, no denormalization) or extend to twelve for complex ones (multiple join tables, high write volume requiring slow backfills).

---

The Architecture

Here is the full migration pipeline from start to final cutover:

Walking through the diagram: during the early phases, 100% of traffic goes to the old system. The dual writer sends every incoming write to both MySQL and Postgres, with MySQL as the authoritative write. The shadow reader fetches results from both systems and compares them in the background, without affecting the response served to the client. A separate backfill job copies historical data in key-range batches.

Once the shadow read discrepancy rate drops to zero (or below an acceptable threshold), the feature flag starts routing a canary percentage to the new system for reads. This is when the risk exposure begins: real users reading from the new system. Monitoring error rates and latency during each canary step catches problems before they affect the majority of users.

---

Dual-Write and Data Consistency Challenges

Dual write looks simple: write to both systems. The complexity is in what "both" means when writes fail, when writes arrive during migration, and when the two systems disagree.

The sequence diagram shows the three decisions you must make in dual write:

1. Which write is authoritative? Always the old system during the migration window. The old system's write must succeed for the operation to succeed from the client's perspective. The new system's write is best-effort: if it fails, the client gets a 200, but the discrepancy goes into a log for async remediation.

2. What happens when the new write fails? You cannot fail the entire request because the old write succeeded. Log the failure, serve the response, and have a reconciliation job catch up. The shadow read will also catch it on the next read.

3. How do you discover divergence? Shadow reads. On every read, fetch from both systems in the background, compare, and log any difference. The discrepancy rate (diverged reads / total reads) is your primary signal that the new system is not ready for cutover.

Shadow read sampling strategy

Running shadow reads on 100% of traffic doubles your read load during the migration window. For high-throughput systems, I use a sample-based shadow read: 10% of reads trigger a background shadow comparison. This reduces the overhead by 10x while still providing enough signal. With 10,000 reads per second, a 10% sample gives 1,000 comparisons per second, which is enough to detect discrepancies in seconds, not minutes.

The shadow read result should be published to a metrics stream. I track:

• Total shadow reads: volume of comparisons running
• Discrepant reads: shadow reads where old and new results differ
• Discrepancy rate: discrepant / total (target: below 0.1% before any read cutover)
• Field-level discrepancy breakdown: which specific fields diverge most often (reveals data model bugs precisely)

Field-level tracking is critical. A 2% overall discrepancy rate looks alarming until you discover that 1.9% of it is from a timezone normalization difference in a single timestamp field that is easy to fix, and only 0.1% is genuine write-miss divergence.

Reconciliation approaches

When new system writes fail, you have three options for reconciliation:

I use shadow read repair for simple divergences and a periodic diff job for complete validation before any read cutover.