Decide how much waiting the service can accept
An additive schema change can preserve application compatibility while delaying requests that use the table. Before approving it, decide how much disruption the service can accept and how the migration will stop when it exceeds that budget. A successful run against an otherwise idle database leaves the contention question open.
Consider adding a nullable text column with no default or constraints to an ordinary PostgreSQL 18 table. PostgreSQL documents that this addition needs no table rewrite. The ALTER TABLE command still requires ACCESS EXCLUSIVE. That lock conflicts with every table lock mode, including the ACCESS SHARE lock taken by an ordinary SELECT.
The example below is a design exercise based on PostgreSQL 18 documentation. It contains no measured wait times or production result. Its release decision is whether the proposed statement and migration runner can keep blocking within an agreed limit while existing traffic continues.
Trace the queue through three sessions
A reporting session reads the table inside a transaction that remains open. Its table lock normally lasts until that transaction ends, even if the SELECT has finished. The migration arrives and waits for its incompatible lock.
A later application query can then wait behind the migration. PostgreSQL calls this a soft block: a conflicting lock request already ahead in the queue can block another requester. The pending schema change can therefore affect traffic before it acquires its own lock.
The decision depends on the entire chain. Finishing the reporting transaction may let the migration proceed. Ending the migration attempt removes its pending request. Assign responsibility for that choice before the release, including who owns the reporting job and who can postpone the schema change.
- Session ATransaction remains open
A report has read the table and still holds ACCESS SHARE.
- Session BMigration waits
The column addition requests ACCESS EXCLUSIVE and cannot acquire it yet.
- Session CLater reader queues
Its request can wait behind the migration's conflicting request.
- Release decisionProceed or end the attempt
An approved operating limit determines when the migration must stop and be rescheduled.
Budget acquisition and transaction duration separately
PostgreSQL's lock_timeout limits time spent waiting for each lock acquisition. It does not limit work after acquisition or supply one total budget across several acquisitions. statement_timeout covers a statement's elapsed time, including its lock wait. Choose the two values from the service's tolerance and the expected migration work; a generic timeout value cannot establish those limits.
Apply the settings through the actual migration connection. SET LOCAL lasts for the current transaction, so it fits a runner that explicitly starts a transaction before setting the limits. Outside a transaction block it has no effect. Verify how the framework opens transactions and handles pooled connections before relying on this arrangement.
The runner also needs a deadline and cleanup path for its whole transaction. Require a failed statement to stop the migration and roll back. Bound any retry policy across the release attempt, and record why another attempt is justified. Repeated short attempts can create repeated periods of contention.
Review everything inside the transaction
A fast schema statement can still leave its lock held while the same transaction performs later work. Inspect the runner's full transaction boundary, including hooks and data updates. Put a substantial backfill in a separately reviewed step when the compatibility contract permits that separation.
For a CHECK constraint, PostgreSQL 18 permits NOT VALID to skip the initial scan while enforcing the constraint on later inserts and updates. A later VALIDATE CONSTRAINT scans existing rows using SHARE UPDATE EXCLUSIVE. That mode allows ordinary reads and writes, but conflicts with some maintenance and schema operations.
This option changes the work and lock requirements; it does not remove the need to inspect them. When one ALTER TABLE contains several subcommands, PostgreSQL takes the strongest lock any subcommand requires. Record the exact statements, table structure, and server version that were reviewed.
Capture the wait before it disappears
Use pg_locks to distinguish a granted lock from an awaited one and inspect waitstart. PostgreSQL recommends pg_blocking_pids to identify blocking processes because a simple join of lock rows cannot reconstruct queue order correctly. These observations can explain why an attempt waited.
Connect the process identity to pg_stat_activity. Its application_name, xact_start, state, and wait fields help distinguish a long transaction from a currently executing query. An idle-in-transaction session still has an open transaction. Record observations through an authorized monitoring role with the visibility the investigation needs.
Capture the migration outcome and application request delays alongside those database observations. A single clear snapshot only describes that instant. Choose a bounded observation interval and retain timestamps so the reviewer can relate a queue to affected traffic. Keep monitoring frequency proportionate; PostgreSQL documents overhead from frequent lock-manager inspection.
Approve against a representative rehearsal
Rehearse on an isolated environment your team controls, using the proposed runner and representative transaction durations. Include the normal case, a long transaction already using the table, and an attempt that reaches its stop condition. Observe whether ordinary requests recover when the attempt ends and whether the runner leaves any transaction open.
Retain the exact migration, database version, table characteristics, timeout settings, and observed wait and request-latency results. Choose acceptance limits before interpreting those results. A small rehearsal can establish the mechanism and cleanup behavior; its traffic and data must justify any claim about the intended deployment.
The release record should say which blocking condition was exercised, what stopped the attempt, and what remains unknown. If the representative case exceeds the disruption budget, the next decision is a different transaction boundary, a coordinated operating window, or further investigation. An assessment can own that bounded decision before the migration is scheduled.