pg-flux and the Discipline of Safe PostgreSQL Migrations

A migration is a production event, not just a SQL file

The thinking behind pg-flux starts with a simple observation: PostgreSQL can accept a migration statement that is unsafe for a busy application. A table rewrite, long validation, blocked lock queue, or unbounded update may behave well on a small staging copy and interrupt production under concurrency. Correct SQL is necessary; operational compatibility is a separate property.

Safe change depends on table size and shape, traffic, transaction duration, indexes, replicas, database version, deployment order, and application behavior. That makes universal “zero-downtime SQL” lists unreliable. The goal is a disciplined workflow that exposes assumptions, separates phases, constrains blast radius, and gives operators evidence to continue or stop.

Use expand, migrate, and contract

Application and schema deployments rarely switch at the same instant. Expand-contract design keeps old and new versions compatible during the transition. First add the new structure without removing what existing code needs. Then deploy code that can work across the transition, move or derive data, validate the new path, switch reads and writes, and only later remove the old structure.

• Add nullable columns or new tables before code requires them, then enforce constraints after data is ready.
• Use concurrent index operations where supported and appropriate, while planning for their failure and cleanup states.
• Avoid renaming or dropping a field in the same release that removes all compatibility logic.
• If dual writes are unavoidable, define authority, idempotency, monitoring, and reconciliation before enabling them.
• Give every compatibility shim and temporary object an owner and removal condition.

Control locks, queues, and transaction scope

A brief strong lock can be acceptable if acquired immediately. The dangerous case is often a migration waiting behind a long transaction while new application requests queue behind the migration. Set a short lock timeout appropriate to the operation so the change fails instead of becoming an unbounded outage. Keep migration transactions focused, and observe blockers and application latency during execution.

Statement timeouts, maintenance windows, and throttling are controls, not guarantees. Review whether an operation rewrites a table, scans existing rows, validates a constraint, or generates heavy replica lag. Test on representative data volume and distribution, but remember that staging rarely reproduces production concurrency. A runbook should name the expected lock, approximate work, dashboards, stop conditions, and responsible operator.

Treat backfills as resumable application jobs

1. Define an idempotent transformation

   A row can be retried without compounding values or overwriting a newer application change.

2. Select bounded batches

   Process stable key ranges or another deterministic cursor, commit frequently, and avoid offset scans that grow or skip under change.

3. Throttle from database health

   Pause or reduce concurrency when locks, latency, write-ahead log volume, replica lag, or application errors cross agreed thresholds.

4. Checkpoint progress

   Persist enough state to resume after deployment, timeout, failover, or operator stop without restarting the full job.

5. Reconcile continuously

   Count remaining rows, sample transformed values, compare invariants, and record errors for explicit review rather than silent skips.

Large data movement does not belong hidden inside a schema transaction. A job can expose rate, remaining work, lag, failures, and pause controls. It can also coexist with application writes if precedence rules are explicit. For example, the backfill may update only rows where the new value remains absent, while new application versions populate it on every fresh write.

Validate the new invariant, then remove the old path

Validation should express the business invariant, not merely that a command returned success. Compare counts and distributions, check referential relationships, sample edge cases, and observe new read and write paths. Where PostgreSQL supports separating constraint creation from validation, that can help stage work, but the exact locking and version behavior still needs review.

Rollback is often a compatibility strategy rather than reversing SQL. Restoring a dropped column or reversing a destructive transformation may be slow or impossible, while deploying the previous application version can also fail if the schema moved too far. Preserve old structures until the rollback window closes, rehearse a roll-forward fix, and back up or archive destructive data when required. Then finish: remove old reads, writes, triggers, indexes, columns, flags, and monitoring. Temporary migration machinery becomes permanent risk when cleanup has no owner.