How do I coordinate a zero-loss schema migration with dual-writing on a live table?

Short answer: a blocking ALTER TABLE on 20M rows locks the table and stalls the system. The fix is a dual-write-based expand/contract migration — split the change into phases, and let no step be one big transaction.

The logic: instead of changing the schema in a single move, let the old and new schema live side by side for a while, migrate the data without dropping any, then shut the old one down. Each step must be coordinated with deploys and reversible.

1. EXPAND — add the new structure nullable. Add the new columns/structure to accept NULL; the table isn’t rewritten (no rewrite), and the lock is instantaneous. This step alone is safe and reversible.
2. DUAL-WRITE — have the code write both schemas. Deploy code that writes to both the old and new schema on every change. Keep it backward-compatible: the old read paths keep working while the new fields are filled in parallel.
3. BACKFILL — migrate history in throttled batches. Copy old data in small, rate-limited (throttled) chunks, preferably off-peak; keep going until the old and new fields agree. At the end, verify (reconcile) row counts and sample records — that’s the only way you catch silent data loss.
4. SWITCH READS — point reads at the new schema. Move the app to read from the new schema, but keep writing to both for a while. This is a safety net so you can fall back to the old one if something goes wrong.
5. CONTRACT — drop the old. Once you’re sure the new structure works solidly, stop writing the old, then DROP the old columns. This final step is also deployed independently; use pg_repack / an online-schema-change tool where possible, and never write one giant transaction.

Bottom line: expand/contract + dual-write + batched backfill + verify; move in step with deploys so old code never sees a data shape it can’t handle. With this discipline you can transform even a 20M-row financial schema in PostgreSQL without taking the live system down.