PostgreSQL Zero-Downtime Schema Migrations: A Production Guide

Aug 20

By · Updated Sep 11

postgresqldatabase engineeringsystem designreliability

A database migration is not safe because the SQL finishes quickly on staging. It is safe when old application instances, new application instances, background jobs, replicas, and rollback procedures can coexist while the change is in flight.

That makes zero-downtime migration a compatibility protocol, not a DDL trick. PostgreSQL gives us useful primitives—transactional DDL, NOT VALID, VALIDATE CONSTRAINT, and CREATE INDEX CONCURRENTLY—but it cannot decide whether two application versions understand the same data.

The invariant

During every deployment phase, all live readers must understand the stored representation and every live writer must preserve it. A useful deployment model is:

expand schema -> deploy compatible code -> backfill -> enforce -> remove legacy path

Each arrow is a checkpoint. If rollback crosses more than one checkpoint, the plan is too coupled.

Consider renaming customers.name to display_name. A direct rename makes old binaries fail immediately. The compatible sequence is:

  1. Add nullable display_name.
  2. Deploy code that can read either column and writes both.
  3. Backfill old rows in bounded batches.
  4. Verify parity and switch reads to display_name.
  5. Stop writing name, wait for old binaries and jobs to disappear, then drop it in a later release.

The apparent duplication is not waste. It buys a rollback window.

Lock time is the first budget

“Without downtime” means preserving the application’s availability target throughout the rollout. It does not mean every DDL operation is lock-free. Confirm the behavior against the documentation for your deployed PostgreSQL version, and test with representative concurrent traffic.

Many ALTER TABLE forms acquire ACCESS EXCLUSIVE, PostgreSQL’s strongest table lock. Even a metadata-only operation can sit behind a long transaction and then block new traffic once it reaches the front of the lock queue. The dangerous variable is often wait time, not execution time.

Set a short lock timeout for online migrations and fail visibly instead of waiting indefinitely:

BEGIN;
SET LOCAL lock_timeout = '2s';
SET LOCAL statement_timeout = '15s';

ALTER TABLE customers
  ADD COLUMN display_name text;
COMMIT;

Retry the migration under controlled automation. Do not raise the timeout until it eventually succeeds; inspect blockers in pg_stat_activity and pg_locks, then remove the operational cause.

Backfill without becoming the incident

A single UPDATE over a large table creates a large transaction, increases WAL, retains dead tuples until commit, delays replicas, and competes with customer traffic. Backfill by a stable key, commit each batch, and make the operation restartable:

UPDATE customers
SET display_name = name
WHERE id > $1
  AND id <= $2
  AND display_name IS NULL;

The worker should persist its cursor, cap rows or wall-clock time per batch, sleep under load, and expose rows processed, WAL volume, replica lag, lock wait, and error rate. The correct batch size is an operating measurement—not a constant copied from another company.

Avoid offset pagination. Concurrent inserts and updates make offsets expensive and ambiguous; a monotonic primary-key cursor gives deterministic progress. If the transform is not naturally idempotent, record a migration version so retries cannot apply it twice.

Add constraints in two phases

Validating a new foreign key or check constraint against every historical row can hold disruptive locks longer than expected. PostgreSQL supports a safer sequence:

ALTER TABLE orders
  ADD CONSTRAINT orders_total_nonnegative
  CHECK (total_cents >= 0) NOT VALID;

ALTER TABLE orders
  VALIDATE CONSTRAINT orders_total_nonnegative;

NOT VALID avoids the initial table scan while still enforcing the constraint for new or changed rows. VALIDATE CONSTRAINT later checks historical rows with a less disruptive lock. PostgreSQL documents that validation uses SHARE UPDATE EXCLUSIVE, allowing ordinary reads and writes to continue.

For a uniqueness constraint on a large table, build the index first:

CREATE UNIQUE INDEX CONCURRENTLY customers_tenant_email_uq
  ON customers (tenant_id, lower(email));

Concurrent index construction permits writes but performs more work, takes longer, and cannot run inside a transaction block. If it fails, it can leave an INVALID index that must be inspected and removed or rebuilt. “Concurrent” means lower write blocking, not zero operational cost.

The hidden contracts

Schema dependencies extend beyond the request-serving application:

  • CDC connectors may identify columns by name and ordinal position.
  • BI queries and exports may bypass the service abstraction.
  • old queue messages may be replayed by new consumers.
  • read replicas may lag behind the migration checkpoint.
  • caches may hold objects encoded with the old shape.
  • rollback may reintroduce a writer that no longer maintains the new field.

Inventory these consumers before DDL. A schema registry helps only if the unregistered paths are found.

A migration control record

Every material migration should have a small control document:

owner: customer-platform
invariant: name and display_name remain equal during dual-write
abort_if:
  lock_wait_seconds: 2
  replica_lag_seconds: 10
  api_error_rate: 1%
rollback: deploy previous reader; retain both columns
destructive_after: 2026-09-20
verification: parity query plus sampled application reads

This is more valuable than a hundred-line migration script with no operating boundary.

Are PostgreSQL migrations reversible?

Application rollback and data rollback are different operations. While both name columns are maintained, an older reader can be redeployed. After the old column is dropped or information is irreversibly transformed, a reverse SQL script cannot reconstruct missing values.

Record the last compatible application version at each phase. Before a destructive step, prove that old writers, scheduled jobs, and replay consumers have retired. If recovery requires a backup, test the restore and reconciliation process first; see why backups do not prove recoverability.

Common migration mistakes

Deploying DDL and dependent code together. Rolling deployments guarantee a period with mixed versions. Separate expansion from adoption.

Treating a backfill as maintenance work. It is a production workload with its own rate limit and SLO impact.

Dropping compatibility immediately. The absence of errors for ten minutes does not prove that cron jobs, replay workers, or rollback images are gone.

Assuming an ORM migration is online. Inspect the exact SQL and the lock mode for the deployed PostgreSQL version.

Calling the plan zero-downtime without an abort threshold. Safety requires a measurable point at which automation stops.

CTO review checklist

Before approving the change, ask:

  1. Which old and new binaries coexist at every phase?
  2. What is the maximum lock-wait budget, and does the migration fail closed?
  3. Is the backfill bounded, observable, throttled, and restartable?
  4. Can we roll back code without reversing a destructive schema operation?
  5. Which replicas, connectors, jobs, exports, and caches consume the field?
  6. What evidence permits the final destructive step?

The mature posture is simple: schema changes are distributed-system changes. Expand first, preserve compatibility, measure the backfill, enforce separately, and delete only after the evidence says the old world is gone.

References

>