Live PostgreSQL Migrations: Expand, Backfill, Verify, Contract

A migration is a compatibility program

Consider replacing a free-text account status with a constrained lifecycle model while requests, background jobs, and exports continue running. Renaming the column and deploying new code together assumes every process changes at the same instant. A rolling deployment makes that assumption false.

Treat the change as a sequence of compatible states. At each state, specify which application versions can read and write, which data representations exist, and how to stop safely. The objective is bounded operational risk. No migration procedure can promise zero downtime independently of workload, lock contention, and operator readiness.

PostgreSQL documents the lock requirements of ALTER TABLE; many forms acquire an access-exclusive lock. A short operation can still wait behind a long transaction. Establish a lock-wait budget and cancel rather than allowing an unexpected blocking queue to grow. PostgreSQL ALTER TABLE

Expand without changing the meaning of existing writes

Add the new representation first. Deploy readers that tolerate its absence. Choose one authoritative write path and update both representations atomically while the compatibility window remains open. If multiple applications write directly, a temporary database trigger may be more reliable than hoping every client implements dual writes.

Define the conversion precisely. What does an unknown legacy status become? Can two old values map to one new value? Is reverse conversion possible? These answers determine whether rollback is a code change or a data-reconstruction project.

Backfill through small resumable transactions

Use indexed key ranges, a durable checkpoint, and bounded batches. Do not hold one transaction across millions of updates. Record the transform version so a resumed worker cannot silently mix conversion rules from two deployments.

The following illustrates a bounded batch for a hypothetical status migration. The actual mapping should be reviewed against production values before use.

WITH batch AS (
  SELECT id
  FROM accounts
  WHERE id > $1 AND lifecycle_state IS NULL
  ORDER BY id
  LIMIT 500
  FOR UPDATE SKIP LOCKED
)
UPDATE accounts AS a
SET lifecycle_state = CASE a.status
  WHEN 'enabled' THEN 'active'
  WHEN 'disabled' THEN 'suspended'
  ELSE 'needs_review'
END
FROM batch
WHERE a.id = batch.id
RETURNING a.id;

Skipping locked rows means a forward checkpoint can leave holes. Run a separate reconciliation pass over remaining nulls; never interpret reaching the largest ID as proof of completion. Also ensure concurrent writers cannot introduce new nulls after the sweep.

Throttle using measured replication lag, database latency, WAL growth, and lock contention. Batch size is a control variable, not a magic constant. A safe batch on a quiet staging database may be disruptive during a production import.

Build constraints without surprising the workload

For eligible constraints, separate adding the constraint from validating existing rows. Confirm the exact syntax and lock behavior for the deployed PostgreSQL version. This article's referenced current documentation is PostgreSQL 18; older releases may differ.

Concurrent index creation allows writes to continue but has restrictions, takes additional work, cannot run inside a transaction block, and can leave an invalid index after failure. Inspect the index state and follow an explicit cleanup or retry procedure. Do not treat a failed migration command as an automatic rollback of every artifact. PostgreSQL CREATE INDEX

Verify semantics before switching readers

Compare distributions by account type, customer cohort, and legacy status. Count unexpected mappings and inspect their identifiers. A total row count can match even when every suspended account was converted incorrectly.

Run shadow reads that compare old and new interpretations without changing user-visible results. Sample both common and rare transitions. Include jobs scheduled before the deployment, import tools, support scripts, and analytics exports in the compatibility audit.

Set a cutover gate that can be evaluated mechanically: no unexplained mismatches, no remaining unconverted rows, bounded replication lag, and a tested fallback. Keep the old representation until that gate remains satisfied through a representative workload period.

Separate code rollback from data rollback

Before cutover, code rollback may be straightforward because old writes still work. After accepting states the old schema cannot express, reverting the application can destroy information. Define a point of no return and require a recovery plan before crossing it.

Rehearse with production-shaped volume and deliberately hold a long transaction open. Interrupt a backfill between batches. Roll the application back while dual writes remain active. The useful result is a measured recovery procedure, not merely a migration that succeeds once on an empty database.