Online Schema Changes

The Danger of ALTER TABLE

 "add a column" sounds trivial. on 500M rows:

 MySQL (older): full table COPY → hours + disk doubling +
                write-blocking for the duration
 Postgres:     most ADDs are metadata-only ✓ BUT:
   - adding COLUMN with VOLATILE DEFAULT = rewrite!
   - changing TYPE = rewrite + lock
   - CREATE INDEX without CONCURRENTLY = write-locking scan

 locks cascade: schema lock queues ALL following queries.
 one careless ALTER at peak = outage that looks like
 "database suddenly slow."

The Safe-Change Taxonomy

 INSTANT (metadata only):
   ADD COLUMN nullable, no default or constant default
   RENAME, DROP COLUMN (careful: app compatibility!)

 SAFE with tools/flags:
   CREATE INDEX CONCURRENTLY (no write-block; takes longer)
   ADD COLUMN with default (11+ postgres: instant)

 REWRITES (avoid live / plan carefully):
   type changes, column reorders, some constraint additions

 rule: KNOW your engine's behavior per statement type.
 never run untested DDL against prod-sized tables.

The Expand–Migrate–Contract Lifecycle

 any semantic change decomposes into safe phases:

 GOAL: rename column phone → phone_number

 1. EXPAND:    add phone_number nullable      (instant)
 2. DUAL-WRITE: app writes BOTH columns
 3. BACKFILL:  batched UPDATE ... WHERE phone_number IS NULL
               throttled; id-cursor pagination
 4. SWITCH:    reads move to new column behind flag
 5. CONTRACT:  stop writing old; drop LATER (observation window)

 each phase deploys independently; rollback = previous phase.
 NEVER combine expand+contract in one deploy —
 that's how midnight pages happen.

Online Migration Tools

 battle-tested machinery exists — use it:

 gh-ost (GitHub):    triggers-less MySQL migration via binlog;
                     throttleable, pauseable, auditable
 pt-online-schema-change: trigger-based predecessor
 pg_repack / logical approaches for postgres rewrites
 Liquibase/Flyway:   versioned migration scripts + state tracking

 what they provide beyond raw SQL:
 - change streaming to shadow table (no long locks)
 - cut-over coordination under brief lock (~seconds)
 - progress/throttle controls for production safety

Backfill Discipline

 backfills are queries TOO — they take down prod when rude:

 -- the polite pattern:
 UPDATE orders SET phone_number = phone
 WHERE id IN (
   SELECT id FROM orders
   WHERE phone_number IS NULL AND id > :cursor
   ORDER BY id LIMIT 10000
 );

 □ batch size bounded (10k rows typical)
 □ sleep between batches; watch replication lag!
   (backfills are the #1 cause of lag spikes)
 □ resumable cursor; progress visible
 □ off-peak scheduling; kill-switch ready

Interview Framing

“Add a required field to a billion-row table” scored lifecycle: instant-metadata-add first, dual-write, throttled backfill WITH lag awareness, read-switch behind flag, delayed contract phase. Naming CONCURRENTLY/gh-ost-class tooling shows tool literacy; the lag-during-backfill warning shows scar tissue. One-line summary worth memorizing: “expand, migrate, contract — never all at once.”