Zero-Downtime Migrations and Backfills

Zero downtime is a compatibility and operations discipline, not a magical migration flag. Expand the schema so old and new code coexist, move data in bounded resumable batches, validate invariants, switch reads and writes gradually, observe, and contract only after rollback expires. Every DDL statement needs lock, rewrite, duration, replica, and failure analysis.

What will you be able to do?

• Classify changes by lock, rewrite, scan, and compatibility risk.
• Add constraints and indexes through safer staged patterns.
• Build idempotent, throttled, restartable backfills.
• Coordinate application deploys, validation, rollback, and cleanup.

Prerequisites and mental model

A migration has two timelines: schema compatibility and data movement. Keep them separate. The dangerous part may be waiting for a lock, not executing DDL; use lock_timeout and monitor blockers.

How do you migrate safely?

Example: replace free-text priority with a governed value.

1. Add new nullable priority_v2.
2. Deploy code that understands both and writes both.
3. Backfill rows by stable ID ranges with commits/checkpoints.
4. Reconcile values and invalid/quarantined rows.
5. Add a check constraint as NOT VALID where appropriate, then VALIDATE CONSTRAINT under current-version semantics.
6. Switch reads to v2 and observe.
7. Stop old writes.
8. Add not-null using a version-appropriate low-risk technique after proof.
9. Drop old column only when rollback no longer needs it.

Create large indexes with the current documented concurrent process when blocking matters. It cannot run inside a normal transaction block and failed builds may leave invalid indexes requiring cleanup.

Backfills set application_name, use bounded timeouts, avoid long snapshots, record last processed key and counts, monitor WAL/replica lag/autovacuum, and pause automatically on pressure.

Why does this matter in AI-native systems?

Changing embedding model/dimensions is a dual-version migration. Write new embeddings alongside old, build index, evaluate, switch reads by configuration, retain rollback, then retire old data. Do not mutate incompatible vectors in place or make partial rebuilds visible.

Prove it worked

Rehearse on a production-like restored dataset. Record lock wait, duration, WAL, replica lag, table/index growth, batch rate, application compatibility, constraint reconciliation, switch, and rollback. Inject failure at every stage and resume.

Production judgment

• DDL behavior changes across PostgreSQL versions; read current docs.
• ORM-generated migration plans require the same review.
• Schema rollbacks can be more dangerous than roll-forward fixes after data writes diverge.
• Migration jobs need separate connection and resource budgets.

Common mistakes

• Deploy blocks unexpectedly: lock queue not tested → timeout, inspect blockers, reschedule.
• Backfill bloats/lag spikes: batch too large → throttle and monitor.
• Rollback broken: old column removed immediately → preserve compatibility window.

AI Pair-Programmer Prompt

Hands-on exercise

Plan migration from one embedding model/dimension to another with no retrieval outage.

Acceptance criterion: both versions coexist, new index is built/evaluated before switch, partial data is invisible, and rollback remains possible.

Knowledge check

1. Why separate schema expansion from backfill?
2. What can remain after failed concurrent index creation?
3. When should contraction happen?

---

Answers

1. It keeps transactions short and preserves application compatibility.
2. An invalid index requiring inspection/cleanup.
3. After new behavior is stable and the rollback window no longer needs old structure.

Completion checklist

• Lock/rewrite/scan risks are known.
• Old and new app versions coexist.
• Backfill is bounded, resumable, and reconciled.
• Switch, rollback cutoff, and contract are explicit.

Primary references

• ALTER TABLE
• CREATE INDEX
• Lock monitoring