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

> Instead of a blocking `ALTER TABLE` on 20M rows run expand/contract: add nullable columns, dual-write, backfill in throttled batches, then drop the old.

- Asked: 2026-06-08
- Answered: 2026-06-11
- Asked by: Taylan
- Tags: veritabani, postgresql, ci-cd
- Source: https://www.muhammetsafak.com.tr/en/just-ask/zero-loss-schema-migration-with-dual-writing-on-a-live-table/
- Language: en-US
- Author: Muhammet Şafak

---
**Question:** On a live system with active trading running, we need to completely change the financial schema on the `users` table; it has 20M rows. A direct `ALTER TABLE` would lock the table, so the system stalls.

With a dual-writing strategy, how do I coordinate the phased migration where the code writes to both the old and new schema at once, the old data is migrated in the background, and the old structure is then shut down?


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.

## Related Reading

- [Is PostgreSQL Enough for Everything?](https://sade.dev/en/journal/is-postgresql-enough-for-everything) — sade.dev
- [Should I use a partial index on a queue table where I only ever scan 'pending' rows?](https://www.muhammetsafak.com.tr/en/just-ask/should-i-use-a-partial-index-on-a-queue-table-where/) — Just Ask
- [How do I rewind to seconds before a disaster with WAL archiving and PITR?](https://www.muhammetsafak.com.tr/en/just-ask/point-in-time-recovery-with-wal-archiving/) — Just Ask
- [How do I run database migrations safely in CI/CD?](https://www.muhammetsafak.com.tr/en/just-ask/running-database-migrations-safely-in-ci-cd/) — Just Ask