Back to roadmap
Module 6 · Relational Data at ScaleDay 05825 min

Online Schema Migrations

Add columns, change types, split tables — without downtime.

← PreviousNext →
Day 058

Online Schema Migrations

Add new col
service
Dual-write
service
Backfill
service
Switch reads
service
Drop old
service
Signal path
Expand / migrate / contract
Add new col
service
flow
Dual-write
service
Dual-write
service
flow
Backfill
service
Backfill
service
flow
Switch reads
service
Memory hook

Online Schema Migrations: add columns, change types, split tables

Mental model

shape data so reads and writes stay honest

Design lens

Dual-write requires careful invariants.

Recall anchors
Expand-contractBackfillTools

Why it matters

Online migrations work in stages: write code that handles old + new schema, change schema, backfill, switch reads, drop old. Done right, no downtime.

Deep dive

Renames are particularly risky; prefer add+backfill+switch.

Big-table migrations need shadow tables and chunked backfills (gh-ost, pt-osc).

ALTER TABLE on Postgres mostly online for adding nullable columns; type changes are not.

Demo / scenario

Rename column with billions of rows.

  1. Step 1: add new column nullable.
  2. Step 2: dual-write old + new.
  3. Step 3: backfill in chunks.
  4. Step 4: switch reads to new.
  5. Step 5: drop old after soak.

Tradeoffs

  • Dual-write requires careful invariants.
  • Backfill cost spread over hours/days.
  • Long staging vs quick rollout — pick by risk.

Diagram

Add new col
Dual-write
Backfill
Switch reads
Drop old
Expand / migrate / contract.

Mind map

Check yourself

Loading quiz…

Sources & further reading

← PreviousNext →