Zero-Downtime Migrations, Releases, and Rollback

Safe pgvector releases separate compatible schema expansion, bounded data migration, new index construction, shadow verification, gradual read cutover, and later cleanup. Application, schema, model, chunker, index, and prompt versions may move independently. Feature flags and canaries limit blast radius; rollback remains possible until old readers, writers, and recovery paths are deliberately retired.

What will you be able to do?

• Apply expand–migrate–contract.
• Avoid long table locks and rewrite surprises.
• Coordinate dual-read and dual-write periods.
• Gate canaries on quality and operations.
• Roll back application and data-path changes.

What are the release phases?

1. Expand: add nullable columns/tables and compatible code paths.
2. Migrate: backfill in small resumable batches with progress and throttling.
3. Verify: constraints, counts, plans, exact recall, security, and recovery.
4. Canary: shadow or route limited approved traffic.
5. Cut over: switch reads, observe, then switch or retire old writes.
6. Contract: remove old schema only after rollback and retention windows.

Adding a vector column can be cheap when nullable, while populating it and building an ANN index is substantial work. Assess locks and table rewrite behavior for the exact PostgreSQL version.

How do you backfill safely?

Use ordered key ranges or jobs, bounded transactions, SKIP LOCKED if workers compete, pause on replica lag or SLO pressure, and record checkpoints. Do not use unbounded OFFSET on a changing large table.

What are release gates?

• migration progress and error budget;
• index valid and expected plan;
• exact/ANN recall by slice;
• cross-tenant and deletion tests;
• p95/p99 latency, load, WAL, replica lag;
• backup/restore and rollback rehearsal;
• no incompatible old application instances remain.

How do you verify it?

Rehearse on a production-shaped snapshot. Interrupt each phase and resume. Deploy old and new application versions together. Roll the feature flag forward and back. Cancel an index build safely. Confirm rollback does not mix embedding spaces.

What breaks in production?

• Destructive schema changes deploy before all readers update.
• Dual writes diverge silently.
• A flag switches queries but not query-embedding model.
• Backfill overloads WAL or replicas.
• Cleanup removes the only fast rollback.

AI pair-work prompt

Create an expand–migrate–contract runbook for this pgvector change [describe]. Include compatibility matrix, lock analysis, batches, index build, dual paths, shadow/canary gates, observability, pause conditions, rollback, and final cleanup evidence.

Verification contract: Demonstrate interruption and rollback at every phase in a production-shaped rehearsal before authorizing the change.

Check your understanding

1. Why is contract last?
2. What must a model-switch feature flag control together?
3. Name three automatic pause signals for a backfill.

Official references

• PostgreSQL ALTER TABLE
• PostgreSQL concurrent indexes