Skip to main content
PreparationZone Logo
PrepZone

Database Migration at Scale

Zero-downtime schema changes, dual writes and backfill strategies for live production data.

Pass checkpoint below

Read these first

Why this matters

  • StreamHub migrated 2.4B rows from MySQL to PostgreSQL over 6 weeks with zero downtime — the dual-write + backfill pattern made it possible.
  • ALTER TABLE ADD COLUMN on a 500M-row table locks the table for minutes in MySQL; in PostgreSQL it is fast but backfills are still slow.
  • Getting migration wrong means data loss, extended downtime, or weeks of rollback pain.

Zero-downtime migration toolkit

  • Expand-contract — add new column/table → dual-write → backfill → switch reads → drop old.
  • Online schema change — pt-online-schema-change, gh-ost, or native ALTER ... CONCURRENTLY in Postgres.
  • Dual writes — application writes to both old and new stores during transition.
  • Backfill workers — batch-process historical rows from old to new with rate limiting.
  • Verification — row-count checks, checksum comparisons, shadow reads before cutover.

Expand-contract phases

Zero-downtime DB migration

dual-writecutoverCOMPUTE
EKS APIfeature flag route
DATABASE
Legacy RDSsource
NETWORK
AWS DMSCDC replication
DATABASE
Auroratarget
DMS CDC → new Aurora → dual-write → cutover → decommission old RDS.

Phase 1 — Expand: Add new column/table without removing old.

Java
-- Postgres: instant metadata change, no table rewrite
ALTER TABLE users ADD COLUMN display_name_v2 VARCHAR(255);

-- Create new table for major restructure
CREATE TABLE users_v2 (
  id          UUID PRIMARY KEY,
  email       VARCHAR(255) UNIQUE NOT NULL,
  profile     JSONB,
  created_at  TIMESTAMPTZ DEFAULT now()
);

Phase 2 — Dual write: Application writes to both old and new.

Java
def update_user(user_id: str, display_name: str) -> None:
    mysql.execute(
        "UPDATE users SET name = %s WHERE id = %s",
        [display_name, user_id]
    )
    postgres.execute(
        "UPDATE users SET display_name_v2 = %s WHERE id = %s",
        [display_name, user_id]
    )

Phase 3 — Backfill: Batch-copy historical data.

Java
BATCH_SIZE = 1000
RATE_LIMIT_PER_SEC = 5000

def backfill_display_names():
    last_id = load_checkpoint()
    while True:
        rows = mysql.query(
            "SELECT id, name FROM users WHERE id > %s ORDER BY id LIMIT %s",
            [last_id, BATCH_SIZE]
        )
        if not rows:
            break
        postgres.executemany(
            "UPDATE users SET display_name_v2 = %s WHERE id = %s",
            [(r.name, r.id) for r in rows]
        )
        last_id = rows[-1].id
        save_checkpoint(last_id)
        time.sleep(BATCH_SIZE / RATE_LIMIT_PER_SEC)

The rest of this guide is part of Pro

Free covers every fresher guide in full. Pro unlocks Senior, Staff and Architect guides across every track.

Still to come · 4 sections

  • Verification before cutover
  • Online schema changes
  • Cross-database migration (MySQL → PostgreSQL)
  • Feature flags for safe cutover

Test yourself

Answer these before moving on — recall is what makes it stick.