Migrate a 10TB database with zero downtime: what is the plan?

Question

IT Interview Practice · Accepted Answer

**Dual-write to old and new, backfill history in the background, use CDC to catch up the tail, verify with shadow reads, then flip reads atomically — with a rollback path at every step.** You never take the app down; you run both databases in parallel until the new one is provably identical, then cut over.

## Architecture

```text
        ┌── write ──▶ OLD DB (source of truth) ──CDC (Debezium)──┐
 App ───┤                                                        ▼
        └── write ──▶ NEW DB ◀──── backfill job (historical rows)
                        │
   shadow reads (compare OLD vs NEW) ─▶ diff metrics ─▶ atomic cutover ─▶ NEW is truth
                                                          (flip flag; rollback = flip back)
```

## How it works

1. **Dual-write.** The app writes every change to both OLD and NEW. New rows stay in sync from now on; only history is missing.
2. **Backfill.** A background job copies historical rows in batches (throttled to protect prod). Use `INSERT ... ON CONFLICT DO NOTHING` so it's idempotent and safe to re-run.
3. **CDC catch-up.** Dual-write can miss rows written during the switch-on. **Debezium** streaming the OLD DB's WAL/binlog replays every change, closing the gap the backfill can't.
4. **Shadow reads.** Read from NEW in parallel with OLD, **compare results**, and emit a diff metric. Zero diffs over time = NEW is trustworthy.
5. **Cutover.** Flip a feature flag so reads (then writes) go to NEW. It's a config change, not a deploy — instant, and **reversible by flipping back**.

## Interview perspective

The interviewer wants **how you guarantee no data loss and no downtime**, and whether you have a **rollback**. Good answers mention dual-write; great answers add **CDC to catch the race window**, **shadow reads to verify before trusting**, and an **atomic, reversible flag flip**. The classic trap is "backfill then switch" with no verification — you discover the mismatch after users hit it, with no way back.

## Trade-offs & failure modes

- **Dual-write consistency.** If the NEW write fails but OLD succeeds, they diverge. Make OLD authoritative and let CDC repair NEW, rather than failing the user's request.
- **Cost & complexity.** Running two databases + CDC + backfill is expensive and operationally heavy — justified only for large, critical stores.
- **Schema changes** during migration multiply risk; freeze schema until cutover.
- **Backfill load.** An unthrottled backfill can saturate prod I/O and cause an outage — the opposite of the goal; batch and rate-limit.
- **Rollback window.** Keep dual-writing (now NEW→OLD) for days after cutover so you can flip back if a latent bug surfaces. Only then decommission OLD.
- **When not to:** a small DB with a maintenance window doesn't need this — a 30-minute `pg_dump`/restore is simpler and safer than a CDC pipeline.

Architecture

How it works

Interview perspective

Trade-offs & failure modes

Related questions