PostgreSQL Schema Migrations for SaaS Without Downtime

A PostgreSQL statement can finish in two milliseconds after waiting thirty seconds for a lock. During that wait, a requested ACCESS EXCLUSIVE lock can also delay later queries. The SQL looks cheap in staging, yet the production deployment creates an application outage before it changes one row.

“Without downtime” should therefore mean a measured objective, not an absolute promise. Define the allowed error rate and latency during the change, keep old and new application versions compatible, fail fast when a lock is unavailable, and advance through observable stages. Network faults, failover, operator error, and workload changes remain possible.

This note develops that process for PostgreSQL 17. It covers lock acquisition, expand-and-contract releases, indexes, constraints, backfills, pools, replicas, and recovery. The result is a runbook that lets an engineer stop safely before a short migration becomes a long queue.

Fast execution does not mean safe lock acquisition

PostgreSQL protects tables with lock modes. Normal SELECT takes ACCESS SHARE. INSERT, UPDATE, and DELETE take ROW EXCLUSIVE. Many ALTER TABLE forms take ACCESS EXCLUSIVE, which conflicts with every table lock mode. Locks are normally held until transaction end.

Two durations matter:

• Acquisition time is how long the statement waits to obtain each required lock.
• Hold time is how long it retains the acquired lock, often through the end of the surrounding transaction.

A metadata-only change may have tiny hold time once acquired. It can still wait behind an old transaction. Worse, once its conflicting request is queued, later readers may wait behind it rather than bypassing the queue indefinitely.

Reproducing the lock queue

The repository’s research/postgres-migration-lock-lab creates 100,000 rows. Session A holds ACCESS SHARE for 1.5 seconds. Session B requests ACCESS EXCLUSIVE for ALTER TABLE with an 800 ms lock_timeout. Session C starts later with a 300 ms statement_timeout and attempts a plain SELECT count(*).

The lab ran five times on Ubuntu 24.04 with PostgreSQL 16.15, default 100 maximum connections, 128 MB shared buffers, locale C, and no competing workload. Each run rebuilt the dataset. This is a lock-correctness test, not a migration benchmark, so there was no warm-up and no throughput measurement.

cd research/postgres-migration-lock-lab
DATABASE_URL=postgresql:///migration_lab ./run.sh

The operation was fast only after the lock existed. The risk was the queue it created while waiting. The dataset size did not cause the observed delay.

Use separate lock and statement budgets

Set migration-local timeouts before DDL:

SET lock_timeout = '800ms';
SET statement_timeout = '20s';

ALTER TABLE accounts ADD COLUMN billing_state text;

PostgreSQL 17 defines lock_timeout as a limit on each lock acquisition wait. statement_timeout covers the full statement from arrival through completion. If both are configured, lock_timeout should be shorter or it can never provide the more specific failure signal.

The numbers above are examples, not defaults. Choose them from the application’s latency objective and retry behavior. A lock budget should be short enough that queued DDL cannot form a visible request backlog. The statement budget must still permit the planned scan or validation.

Use SET LOCAL inside an explicit transaction when the operation supports that transaction boundary. Avoid changing global settings for a one-off migration. On failure, leave the database unchanged, inspect the blocker, and retry during a better window. Do not loop aggressively against a busy table.

Transactional DDL has boundaries

PostgreSQL can roll back many schema changes with their surrounding transaction. That is useful for grouping related metadata operations. It does not make a long transaction harmless. Locks acquired in the transaction usually remain until commit, and a later statement failure can extend disruption while application code decides what to do.

Some important operations are deliberately outside that model. CREATE INDEX CONCURRENTLY cannot run inside a transaction block because it uses multiple transactions and table scans. Treat migration-tool “one transaction per file” defaults as a compatibility question. Split nontransactional operations into their own migration step.

Keep each DDL transaction narrow. Do not mix a quick metadata change, a large backfill, and validation in one transaction merely because rollback is possible.

Expand and contract across releases

The safe unit is not one SQL file. It is a sequence of application and database states in which every live binary can use the current schema.

An expand-and-contract migration uses these stages:

1. Expand: add nullable columns, new tables, indexes, or unvalidated constraints without removing the old representation.
2. Release compatible code: new code understands both shapes. It writes the new shape while old instances can still serve traffic.
3. Backfill: update existing rows in resumable batches.
4. Validate and switch: prove the invariant, move reads to the new shape, and observe a full workload cycle.
5. Contract: stop old writes and remove the old column or table in a later release after every old process and delayed job is gone.

This ordering handles rolling deployments and rollbacks. If a new application release fails at stage 2, roll back the binary while leaving the additive schema in place. Dropping the new column is usually unnecessary risk. After contraction, an application rollback may no longer be compatible, so the go/no-go decision must include old workers, scheduled jobs, analytics, and support tools.

Adding columns depends on the default expression

From PostgreSQL 11 onward, adding a column with a nonvolatile constant default does not rewrite every row. PostgreSQL stores the value in metadata and returns it for older rows until a later table rewrite materializes it.

ALTER TABLE subscriptions
  ADD COLUMN renewal_notice_days integer DEFAULT 7;

That version boundary matters. On older supported-by-some-vendors releases, the same shape may rewrite the table. A volatile default such as clock_timestamp() can still require rewriting rows on PostgreSQL 17.

Even a no-rewrite ADD COLUMN acquires a table lock. Use a short lock_timeout, run it outside a broad application transaction, and verify the server version and expression volatility. If values differ per row, add the column as nullable, set the default for new writes, and backfill explicitly.

Add NOT NULL after proving the data

Setting NOT NULL directly can require scanning the table to verify every row. PostgreSQL 17 can avoid that scan when a valid CHECK constraint already proves the condition. Build the proof in stages:

ALTER TABLE subscriptions
  ADD CONSTRAINT subscriptions_plan_id_present
  CHECK (plan_id IS NOT NULL) NOT VALID;

ALTER TABLE subscriptions
  VALIDATE CONSTRAINT subscriptions_plan_id_present;

ALTER TABLE subscriptions
  ALTER COLUMN plan_id SET NOT NULL;

ALTER TABLE subscriptions
  DROP CONSTRAINT subscriptions_plan_id_present;

NOT VALID skips checking old rows when the constraint is added, but new and changed rows must satisfy it. VALIDATE CONSTRAINT scans existing rows and uses SHARE UPDATE EXCLUSIVE, which allows ordinary reads and writes but conflicts with some maintenance and schema operations. Validation is not lock-free.

Backfill null rows before validation. Verify zero violations with the same predicate, then validate. Keep a statement timeout appropriate for the scan, and watch I/O and replica lag.

Add foreign keys in two phases

Adding a foreign key to a large populated table can scan the referencing table. Use the same validation split:

ALTER TABLE invoices
  ADD CONSTRAINT invoices_customer_id_fk
  FOREIGN KEY (customer_id)
  REFERENCES customers(id)
  NOT VALID;

ALTER TABLE invoices
  VALIDATE CONSTRAINT invoices_customer_id_fk;

The first step begins enforcing the relationship for new writes without proving all historical rows. The second checks existing data. PostgreSQL does not automatically create an index on the referencing columns. Add an index when the delete, update, or join access patterns need one.

Do not disable triggers to avoid the cost. PostgreSQL implements foreign keys with triggers, and disabling replication-role checks can admit inconsistent data.

Build indexes concurrently, then verify the result

A regular CREATE INDEX permits reads but blocks writes to the table. On an active large table, prefer:

CREATE INDEX CONCURRENTLY invoices_tenant_created_idx
  ON invoices (tenant_id, created_at DESC);

Concurrent construction allows normal writes, but it does more work. PostgreSQL 17 performs multiple waits and two table scans. Only one concurrent index build can run on a table at a time, and the command cannot be placed in a transaction block.

Failure can leave an invalid index. PostgreSQL ignores it for queries, but the index still consumes update overhead. Check explicitly:

SELECT c.relname, i.indisvalid, i.indisready
FROM pg_index AS i
JOIN pg_class AS c ON c.oid = i.indexrelid
WHERE c.relname = 'invoices_tenant_created_idx';

If it is invalid, inspect the failure, then drop and retry the concurrent build, or use REINDEX INDEX CONCURRENTLY when appropriate. Do not let an automated migration mark the step complete based only on process exit history.

For a new unique constraint, build a unique index concurrently and attach it:

CREATE UNIQUE INDEX CONCURRENTLY users_tenant_email_uidx
  ON users (tenant_id, lower(email));

ALTER TABLE users
  ADD CONSTRAINT users_tenant_email_unique
  UNIQUE USING INDEX users_tenant_email_uidx;

The concurrent unique build can surface duplicates and has documented failure caveats. Clean and verify data first.

Backfill in bounded, resumable transactions

A single update of millions of rows creates a long transaction, large WAL burst, extended row-version retention, and a large retry unit. Batch by a stable indexed key and commit each batch:

WITH batch AS (
  SELECT id
  FROM subscriptions
  WHERE plan_id IS NULL
    AND id > $1
  ORDER BY id
  LIMIT 1000
  FOR UPDATE SKIP LOCKED
)
UPDATE subscriptions AS s
SET plan_id = infer_plan_id(s.account_id)
FROM batch
WHERE s.id = batch.id
RETURNING s.id;

Persist the last completed key, or make the predicate itself resumable. Rate limit from observed database health, not a copied batch size. Row width, index count, trigger cost, write traffic, storage throughput, and replica capacity all change the safe rate.

Avoid offset pagination because concurrent writes and growing offsets make work unstable and expensive. Make the transformation idempotent. If dual writes are temporarily necessary, reconcile both representations. Application code can write one field and fail before the other, and older binaries may know only the old field.

Long open transactions also retain snapshots that delay removal of dead tuples. PostgreSQL documentation calls out idle transactions as a source of table bloat and advises ending long-running transactions during wraparound recovery. Small commits bound both operational impact and replay cost.

Connection pools amplify migration mistakes

A deployment can have several pools: web instances, workers, admin jobs, analytics, and migration tooling. A waiting DDL connection consumes one slot. Requests queued behind it consume more. If each instance opens a fresh pool during rollout, the database can hit its connection limit while the migration is already blocked.

Use a dedicated migration connection with identifiable application_name and timeouts. Cap application pool growth during deploys. Set request-side pool acquisition timeouts so callers fail in a controlled way rather than waiting without a deadline. Drain old application versions before contraction, but do not terminate unrelated database sessions merely to force DDL through.

Watch WAL and replicas before advancing

DDL metadata may be small, but index builds and backfills can generate enough I/O and WAL to delay replicas. A read from an asynchronous standby can therefore observe the old state after the primary commits. PostgreSQL describes hot standbys as eventually consistent with the primary.

Monitor byte distance as well as the time fields:

SELECT application_name,
       state,
       pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS replay_bytes,
       write_lag,
       flush_lag,
       replay_lag
FROM pg_stat_replication;

The lag intervals describe recent WAL processing and are not predictions of catch-up time. They can become NULL on an idle, caught-up system. Gate the next stage on your own thresholds for byte lag, time lag, storage, and read freshness. If application reads switch to a new column on replicas before its backfill WAL is replayed, users may see inconsistent results.

Find blockers and migration progress

Start with active sessions and lock waiters:

SELECT pid,
       application_name,
       state,
       wait_event_type,
       wait_event,
       now() - xact_start AS transaction_age,
       pg_blocking_pids(pid) AS blocking_pids,
       left(query, 160) AS query
FROM pg_stat_activity
WHERE datname = current_database()
ORDER BY xact_start NULLS LAST;

Inspect pg_locks for requested and granted modes. For index builds, query pg_stat_progress_create_index; its phases distinguish scanning from waits for writers or old snapshots. For large validations, also watch table I/O, active transactions, WAL rate, replica replay, error rate, pool wait, and request p95.

Record the migration ID, server version, SQL hash, start time, timeout settings, row counts, batch checkpoint, and operator. This turns a retry into a controlled continuation rather than a guess.

Operation risk table

The table names the dominant risk, not every lock a command can acquire. Check the exact ALTER TABLE form in the manual for the deployed PostgreSQL version.

Roll back code, roll forward data

Before a destructive stage, decide whether recovery is a code rollback or a data repair.

Additive schema changes usually stay in place while the application rolls back. A failed backfill should pause and resume from its checkpoint. An invalid concurrent index should be inspected and rebuilt. A validated constraint that rejects legitimate traffic may need a deliberate constraint drop, but only after preserving the violating evidence.

Once data has been transformed or a column dropped, automatic rollback SQL can be more dangerous than the original change. Prefer a tested roll-forward repair unless the reverse transformation is lossless, current, and rehearsed. Backups are not a rollback button until restore time and recovery point have been tested.

Migration runbook

Before execution:

1. Record PostgreSQL version, table size, row estimate, indexes, dependencies, and the exact lock modes documented for each statement.
2. State the availability and latency budget, plus abort thresholds for pool wait, error rate, WAL rate, disk, and replica lag.
3. Test old application against the expanded schema and new application against the old-compatible state.
4. Rehearse on representative schema and data. Measure lock acquisition and execution separately.
5. Prepare idempotent SQL, timeout settings, blocker queries, and a stop command.

During execution:

1. Set an identifiable application_name, short lock_timeout, and bounded statement_timeout on the migration session.
2. Confirm no unexpected old or idle-in-transaction sessions hold the target.
3. Apply one stage. Do not bundle the backfill with DDL.
4. Verify catalogs, constraints, index validity, application errors, pool wait, and replica state.
5. Stop when an abort threshold is crossed. Do not remove the timeout to make the deployment pass.

After execution:

1. Reconcile row counts and invariants with explicit queries.
2. Observe at least one relevant workload cycle before switching reads or contracting the schema.
3. Record batch checkpoints and the final migration state.
4. Remove the old representation only after all binaries, workers, and delayed messages are compatible.

The operating rule is to move compatibility first and enforcement later. Bound lock waits, keep transactions short, make backfills resumable, and require catalog and workload evidence before each next stage. That does not guarantee zero interruption. It makes interruption detectable, bounded, and recoverable.