A migration can pass CI and still stall a production API. The risk is rarely SQL syntax. It is the interaction between table locks, rolling deployments, background workers and old application versions that have not received the memo.

For a busy PostgreSQL service, treat the deployment unit as schema plus every reader and writer still running. The reliable sequence is expand, backfill, validate and contract. Each phase needs an observable stop condition and a rollback plan.

1. Expand with backward compatibility

Imagine adding preferred_locale to an accounts table. Do not start with a mandatory field that old pods cannot populate. Add a nullable column first. This normally avoids a table rewrite, but ALTER TABLE can still require an ACCESS EXCLUSIVE lock. A short operation may wait behind a long transaction and cause other requests to queue.

BEGIN;
SET LOCAL lock_timeout = '2s';
ALTER TABLE accounts ADD COLUMN preferred_locale text;
COMMIT;

The two-second timeout is an example operational budget, not a universal recommendation. Failing quickly and investigating a blocker is safer than allowing a migration to wait indefinitely. Deploy code that handles null values and begins writing the new field. During a rolling deployment, both old and new binaries must work against the expanded schema.

2. Backfill in bounded, resumable batches

A single massive UPDATE can generate substantial WAL, dead tuples, replication delay and I/O pressure. Process a small batch, commit, observe and repeat. The example below assumes an indexed primary key and that 'en' is a product-approved fallback.

WITH batch AS (
  SELECT id FROM accounts
  WHERE preferred_locale IS NULL
  ORDER BY id
  LIMIT 500
  FOR UPDATE SKIP LOCKED
)
UPDATE accounts a
SET preferred_locale = 'en'
FROM batch
WHERE a.id = batch.id
RETURNING a.id;

SKIP LOCKED helps concurrent workers avoid waiting on row locks, but repeated scans can still be costly. For very large tables, a keyset or range-based cursor can be more predictable. Keep each transaction short, record progress, and make restarts idempotent. If users can change the locale while the backfill runs, protect those newer values with the IS NULL selection and consider concurrency semantics carefully.

Watch batch duration, user-facing p99 latency, connection pool waits, WAL volume, replication lag and dead tuples. Pause or throttle when those deteriorate. A backfill is not free just because it runs outside request handlers.

3. Validate the invariant before enforcing it

After all active writers populate the field and the backfill finishes, prove the invariant. PostgreSQL lets you add a CHECK constraint as NOT VALID, then validate it separately. New writes are checked once the constraint is installed; existing rows are scanned later. Do not install the constraint while old application versions still write nulls.

SELECT count(*) FROM accounts
WHERE preferred_locale IS NULL;

ALTER TABLE accounts
  ADD CONSTRAINT accounts_locale_present
  CHECK (preferred_locale IS NOT NULL) NOT VALID;

ALTER TABLE accounts
  VALIDATE CONSTRAINT accounts_locale_present;

Validation takes a SHARE UPDATE EXCLUSIVE lock rather than excluding ordinary updates. On supported PostgreSQL versions, a validated non-null check can also help a subsequent SET NOT NULL avoid an additional full table scan. The final DDL still has locking implications, so schedule it separately with a lock budget.

4. Build indexes concurrently, then inspect them

Before adding an index, use EXPLAIN (ANALYZE, BUFFERS) on representative queries. Ordinary index creation blocks writes. CREATE INDEX CONCURRENTLY permits normal writes but performs more work, may wait for old transactions and cannot run inside a transaction block.

-- Example: only add if the query plan justifies it.
CREATE INDEX CONCURRENTLY accounts_tenant_created_idx
  ON accounts (tenant_id, created_at DESC);

SELECT indexrelid::regclass, indisvalid
FROM pg_index
WHERE indrelid = 'accounts'::regclass;

A failed concurrent build may leave an invalid index that still adds write overhead. Inspect the result and remove or rebuild it appropriately before retrying. Concurrent does not mean free of locks, CPU or I/O cost.

5. Diagnose blockers, not just slow SQL

SELECT pid, wait_event_type, wait_event,
       pg_blocking_pids(pid) AS blocked_by,
       left(query, 120) AS query
FROM pg_stat_activity
WHERE datname = current_database()
  AND wait_event_type = 'Lock';

Correlate lock waits with application latency, error rates, long-running transactions, database CPU and replica health. A five-second migration is not successful if it blocks a critical endpoint for those five seconds. Keep an eye on migration frameworks that wrap every statement in a transaction: concurrent index creation needs its own nontransactional execution path.

6. Contract only after old code is gone

Remove compatibility reads and dual writes only after every web pod, background worker and message consumer uses the new field. Prefer waiting an additional release before dropping old columns. A rollback that restores yesterday's binary must not encounter a schema it no longer understands.

Release checklistTest old and new binaries against the expanded schema; set lock budgets; backfill in small resumable batches; validate data before constraints; verify index health; watch p99 latency and replication; postpone destructive DDL until rollback no longer depends on the old shape.

The goal is not literally zero locking. It is predictable compatibility, bounded disruption and a boring rollback.

Official references