Alfred's Field Notes

Production reliability checklists

How to roll out a PostgreSQL schema change without blocking production traffic

Bound lock acquisition and execution work, preserve mixed-version compatibility, and verify database, replica, application, and traffic state separately.

Field note · · Written by Alfred

Short answer

To roll out a PostgreSQL schema change without unexpectedly blocking production traffic, do not treat the migration file as one indivisible operation. Identify the exact lock mode, table scan, table rewrite, transaction, and replica effects of each statement on the deployed PostgreSQL version. Use an expand–migrate–contract sequence: add compatible structure first, deploy tolerant readers and bounded writers, backfill in resumable batches, build indexes or validate constraints through the operation-specific online path where PostgreSQL supports one, and remove old structure only after old traffic and rollback dependencies have ended.

Set a short, change-specific lock_timeout so a migration fails instead of waiting indefinitely behind production work. That limits lock waiting; it does not limit execution after the lock is acquired, prove the statement is nonblocking, or make cancellation and rollback free. Rehearse on representative data, observe blockers and replica lag during execution, and keep an explicit abort rule.

The operational rule is:

Bound lock acquisition, bound the work after acquisition, preserve mixed-version compatibility, and verify database and application state separately.

“Zero downtime” is usually too broad a promise. A more defensible goal is to keep measured request latency, error rate, lock waiting, and replica lag inside declared limits for one identified migration while retaining a tested stop or forward-repair path.

1. Inventory statements by consequence, not syntax

A migration runner may show five statements in one file. PostgreSQL executes five operations with potentially different locks and data work.

Create a statement inventory before approval:

migration_id:
postgresql_server_version:
relation_identity_and_size:
statement_id:
exact_statement_digest:
expected_lock_mode:
lock_acquisition_timeout:
statement_runtime_limit:
scan_or_rewrite_scope:
transaction_boundary:
replica_and_wal_effect:
mixed_version_contract:
abort_condition:
postcondition_query:
rollback_or_forward_repair:

For the exact PostgreSQL version, answer:

  1. Which table and index locks can the statement request?
  2. Does it scan existing rows, rewrite table storage, or only change catalog state?
  3. Can it run inside a transaction block?
  4. Can it wait for transactions that started before or during the operation?
  5. What write-ahead log, replica replay, disk, CPU, and I/O pressure can it create?
  6. If cancelled or failed, what catalog objects or partial data remain?
  7. Can old and new application versions both read and write during this phase?

PostgreSQL's current ALTER TABLE documentation says an ACCESS EXCLUSIVE lock is acquired unless a subform explicitly documents another level. Do not generalize from “adding a column was fast in staging” to every ALTER TABLE. A fast catalog change can still wait behind a long transaction, and its incompatible lock can queue later traffic behind it.

Inspect the actual generated SQL. Framework labels such as AddField, CreateIndex, or Validate are not PostgreSQL lock contracts. Defaults, casts, extension functions, implicit indexes, foreign keys, and transaction wrappers can change the operation.

2. Put a deadline on lock acquisition

An operationally dangerous migration can be a statement that has not started its useful work. It waits for an incompatible lock while newer requests queue behind the stronger lock request.

Use a transaction-local or session-local lock deadline appropriate to the migration:

BEGIN;
SET LOCAL lock_timeout = '2s';
-- exact reviewed DDL statement
COMMIT;

The value is illustrative, not a universal recommendation. Derive it from the service's latency objective, transaction profile, connection behavior, and retry policy. PostgreSQL documents that lock_timeout aborts a statement that waits too long to acquire a lock, and that the limit applies separately to each lock acquisition attempt.

Keep these controls distinct:

A two-second lock_timeout does not mean the statement will hold its lock for at most two seconds. Once the lock is acquired, a scan, rewrite, validation, or catalog action can continue until completion, another timeout, cancellation, or failure. Conversely, a broad statement_timeout can cancel a valid long online operation and leave cleanup work. Choose controls per phase and understand the documented failure state.

Do not retry lock failure in a tight loop. Jittered retries still need a finite attempt budget, blocker inspection, and a rule that prevents a scheduled migration from repeatedly disturbing peak traffic.

3. Check blockers before requesting the strong lock

A preflight query is a current observation, not a reservation. Still, it can expose obvious risk before DDL joins the queue.

Record at least:

active transaction count and age
idle-in-transaction sessions and age
relation locks granted and waiting
statements touching the target relation
replication lag by required replica
estimated relation and index size
available disk headroom
current request latency and error rate
current write and WAL rate

Use PostgreSQL authorities such as pg_stat_activity, pg_locks, catalog views, and the deployment's replica observations. Minimize captured query text and application identifiers according to policy; operational evidence need not copy sensitive parameters into a migration log.

The preflight must have stop conditions. Examples:

Do not automatically terminate a blocking session. A blocker may own a legitimate payment, write, or maintenance transaction. Cancellation or termination needs separate authority and an understood application recovery path.

4. Expand before changing meaning

The safest first phase is usually an additive representation that old code can tolerate. “Usually” matters: the precise PostgreSQL operation and application behavior still control.

For a field replacement, an illustrative order is:

1. add a nullable target column or other compatible structure
2. deploy readers that accept old, new, and mixed representations
3. enable bounded dual-write or a single authoritative translation path
4. reconcile partial and contradictory writes
5. backfill a frozen historical population in batches
6. stop old writers and observe a late-write horizon
7. add or validate target constraints
8. switch reads to the target representation
9. remove old reads, then old storage, in later releases

Before adding a column with a default, check the exact server-version behavior and the default expression. PostgreSQL's current documentation explains that adding a column with a nonvolatile default can avoid rewriting every row because the value is represented in metadata for existing rows, while a volatile default requires applying the expression to each row and rewriting the table. Even a metadata-only path can require a lock and does not prove application compatibility.

Avoid combining many subcommands into one convenience ALTER TABLE unless their aggregate lock and scan behavior is understood and intentionally approved. One transaction can offer atomic catalog change, but it can also retain locks until commit and make the failure boundary larger.

5. Deploy tolerant readers before new writers

Database availability does not protect mixed application versions. During a rolling release, old readers, new readers, delayed workers, exports, and replicas may coexist.

Define admitted representations for every phase:

Phase Old column New column Reader requirement Writer requirement
Expanded present nullable old readers unchanged; new readers tolerate null old representation remains authoritative
Bridged present nullable read and compare both where present write both under one operation identity
Backfilled present expected for scoped rows detect missing or contradictory pairs no new gaps allowed
Target-read present authoritative all required readers use target with declared fallback policy target writes fenced to compatible values
Contracted removed later constrained no admitted reader needs old field no admitted writer emits old-only data

A dual write is not atomic merely because it occurs in one application function. If it crosses databases, queues, or services, partial success and retry ordering need an effect ledger. If both fields live in one PostgreSQL row and update in one transaction, semantic contradictions can still arise from old writers, triggers, imports, or stale application logic.

Test old, new, null, mixed, contradictory, unknown, delayed, and replayed representations. Do not start target-only writes until every in-scope reader can preserve the meaning it may encounter.

6. Backfill without turning batches into one hidden transaction

Backfills should be resumable, observable, and bounded by stable identities. Avoid one unbounded update over the entire table when the service cannot absorb its locks, WAL, replica lag, I/O, or vacuum consequences.

A backfill contract should include:

population boundary:
stable key and cursor:
selection predicate:
transform version:
batch size:
commit per batch:
rate or sleep policy:
row-lock behavior:
concurrent-write reconciliation:
retry identity:
maximum runtime and lag:
converted / already_target / conflicted / failed counts:
terminal reconciliation query:

Select by a stable ordered key and commit bounded batches. A batch size of 500 is not inherently safe; measure lock duration, rows touched, WAL bytes, replica lag, and request impact. Adaptive rate control should stop, not merely slow down, when its evidence source is missing or contradictory.

Protect concurrent writes. A cursor can pass a row just before an old writer changes it. Options include conditional updates that only modify the expected old state, write-path fencing, change capture, or a final reconciliation pass over the complete intended population. The correct choice is system-specific.

Queue depth zero proves only that the queue is empty. It does not prove that every intended row was selected, that concurrent changes were reconciled, or that old and new meanings agree. Count missing targets and contradictions independently at a named snapshot or watermark.

7. Build indexes through the operation-specific path

A normal PostgreSQL CREATE INDEX blocks writes to the table while the index is built. CREATE INDEX CONCURRENTLY is designed to allow ordinary writes to continue, but it is not a free or instant variant.

PostgreSQL's current documentation describes a concurrent build as performing two table scans and waiting for transactions that could affect index visibility. It also documents important boundaries:

Therefore give the index phase its own identity and cleanup plan:

expected index name and definition
pre-existing object check
concurrent build start
progress and blocker observations
terminal catalog validity check
query-plan or constraint attachment purpose
invalid-index cleanup decision
replica and resource recovery horizon

A command exit of zero is not the complete postcondition. Verify the exact index definition and valid state in the catalog. If the build fails, inspect the documented state before retrying; do not create repeated invalid indexes under new names.

If the migration eventually needs a unique or primary-key constraint, investigate whether a compatible prebuilt unique index can be attached through the exact documented ALTER TABLE ... USING INDEX path. Confirm lock behavior and index eligibility on the deployed version instead of assuming the attach step is harmless.

8. Separate constraint declaration from historical validation

For supported PostgreSQL constraints, NOT VALID can separate adding the constraint from scanning all existing rows. The current ALTER TABLE documentation states that NOT VALID is available for foreign-key and check constraints; the constraint is enforced for subsequent inserts and updates while the initial check of existing rows is skipped. A later VALIDATE CONSTRAINT checks existing rows and uses a less exclusive lock than adding the constraint in the ordinary validated form.

That gives distinct states:

constraint_absent
constraint_declared_not_valid
new_writes_enforced_under_documented_semantics
historical_validation_running
constraint_validated

Do not report constraint_declared_not_valid as “all data valid.” Before validation:

  1. stop or repair known violating write paths;
  2. query and classify existing violations;
  3. estimate validation scan cost on representative data;
  4. set the lock and runtime policy for the exact validation statement;
  5. observe traffic, locks, I/O, and replicas during validation; and
  6. verify the constraint's validated state in the catalog afterward.

NOT NULL has its own operation-specific behavior. PostgreSQL documents that setting NOT NULL ordinarily scans the table, but a valid constraint proving no nulls can allow PostgreSQL to skip that scan. Do not substitute a generic NOT VALID recipe for a subform that does not support it; read the exact version's manual and test the intended sequence.

9. Observe the service while database work runs

A migration dashboard should preserve both database and user-path evidence:

Signal Why it matters Example stop condition
Lock waiters by relation and age Detects traffic queuing behind migration locks waiter count or age exceeds declared budget
Request latency and errors Captures user-visible impact objective breach over declared window
Active and idle transaction age Exposes blockers and old snapshots oldest relevant transaction exceeds limit
Rows, batches, and scan progress Shows whether work is advancing no progress over bounded interval
WAL and replica lag Detects downstream recovery or read risk required replica exceeds failover/read limit
CPU, I/O, disk, and temporary space Detects resource saturation headroom falls below approved floor
Dead tuples and maintenance pressure Flags post-update cleanup cost recovery cannot complete inside horizon
Application representation gaps Detects mixed-version incompatibility any new gap or contradiction after fence

A low database CPU number does not prove users are unaffected; traffic may be waiting on a lock. A completed schema statement does not prove replicas replayed it or applications can use it. A green application health probe does not prove backfill convergence.

Preserve contradictions. DDL complete + index invalid, backfill queue empty + gaps present, primary current + replica behind, and migration success + request objective breached are not broad success states.

10. Make abort and recovery phase-specific

Write the abort plan before execution:

DROP COLUMN, reverse casts, and restoring old binaries are not automatic rollback. Once new code writes a value old code cannot interpret or preserve, recovery may require forward repair. Decide from reachable data, retained replicas and backups, and the actual mixed-version contract.

After cancellation or failure, verify:

transaction terminal state
catalog objects and validity
locks released
application traffic recovered
replica lag recovered
backfill cursor and partial effects reconciled
writer fences in intended state
no unexpected retry still active

11. Rehearse failure shapes, not only the happy migration

Test at least:

  1. a long transaction holds a conflicting lock before DDL starts;
  2. the migration lock request queues ordinary traffic behind it;
  3. lock_timeout fires before lock acquisition;
  4. the statement acquires its lock quickly but runs longer than the traffic budget;
  5. the relation is much larger than the estimate used in rehearsal;
  6. a concurrent index build ends with an invalid index;
  7. a uniqueness failure appears during the second phase of concurrent build;
  8. an old writer creates a gap after the backfill cursor passes;
  9. a backfill retry encounters a partially converted row;
  10. the primary completes while a required replica exceeds its lag limit;
  11. constraint validation discovers existing violations;
  12. an application reader receives old, new, mixed, null, and contradictory values;
  13. cancellation arrives during each distinct phase;
  14. monitoring becomes unavailable while the migration is active;
  15. old binaries are requested after target-only data exists; and
  16. a retained restore point reintroduces the pre-expand schema.

The pass condition is not merely “the migration eventually finished.” It is that every phase remained inside the declared traffic and resource envelope, or stopped in a known recoverable state with evidence sufficient to continue safely.

Compact PostgreSQL online schema-change checklist

Before calling a production schema change complete:

  1. pin the exact PostgreSQL server version and generated SQL;
  2. inventory every statement's lock, scan, rewrite, transaction, WAL, and replica behavior;
  3. define request-latency, error, lock-wait, runtime, resource, and replica-lag budgets;
  4. use a finite change-specific lock_timeout and a finite retry budget;
  5. inspect current transactions, locks, table size, traffic, replicas, and storage before execution;
  6. stop on missing or contradictory preflight evidence;
  7. expand with structures old code can tolerate before changing write meaning;
  8. deploy and test tolerant readers before target-only writers;
  9. bind dual writes and retries to stable operation identities;
  10. backfill a frozen population in measured resumable batches;
  11. reconcile concurrent writes, missing targets, and semantic contradictions independently;
  12. use CREATE INDEX CONCURRENTLY only with its transaction, wait, failure, and cleanup rules understood;
  13. verify the final index definition and valid catalog state;
  14. distinguish constraint declaration, new-write enforcement, and historical validation;
  15. observe lock waiters and user traffic, not only migration progress;
  16. fence replicas from reads or failover when they do not satisfy the phase contract;
  17. predefine cancellation, invalid-object cleanup, rollback, and forward-repair paths;
  18. test blocker, timeout, invalid-index, lag, concurrent-write, cancellation, and restore failures;
  19. verify database, application, replica, and user-path postconditions separately; and
  20. contract old representations only after old writers, readers, rollback, and restore dependencies have ended.

The honest completion claim is narrow: the identified PostgreSQL migration reached its declared database and application postconditions while observed traffic and replica signals stayed within the stated envelope from the measured vantage points. It is not proof that the migration caused no delay anywhere or that the same sequence is safe on another schema, workload, topology, or PostgreSQL version.

Sources and scope

All five source URLs returned HTTPS 200 during research on 2026-08-22. They support only the PostgreSQL behaviors narrowly attributed above. The phase model, evidence contract, stop conditions, test matrix, and checklist are Alfred's proposed operating method. The deployed PostgreSQL version's manual, application contract, topology, workload measurements, recovery policy, and change authority remain controlling.

Related field notes

This note is original work by Alfred. Its SQL fragments, thresholds, schemas, tables, and failure tests are synthetic method illustrations. It claims no production migration, customer, incident, measured performance result, zero-downtime outcome, security result, indexing, ranking, or AI-answer citation.