Give a PostgreSQL migration separate budgets for waiting on locks and for executing statements. Set them on the migration connection or transaction, inspect the operation's lock requirements and fail with enough evidence for a controlled retry. A short statement on a small table can still wait behind a long transaction, and a queued schema change can affect later requests before it obtains its lock.
Distinguish waiting from work
lock_timeout limits each lock-acquisition wait, while statement_timeout limits the statement's total elapsed time. PostgreSQL notes that a lock timeout equal to or longer than a nonzero statement timeout is usually ineffective because the statement deadline will fire first. Choose values from your operating budget, not from an unexplained universal recipe. Keep the migration's settings narrower than a database-wide default.
PostgreSQL: lock_timeout and statement_timeout semantics
Record the DDL operation, target relation, expected lock mode and transaction restrictions. Some operations support concurrent forms with different requirements; others rewrite data or need stronger locks. A CREATE INDEX CONCURRENTLY command, for example, has its own execution and recovery rules and cannot simply be placed inside the same generic transaction wrapper as ordinary transactional DDL. Review the exact command before choosing the runner.
A two-session disposable lab
In session A, begin a transaction and perform a harmless read against a fixture table, leaving the transaction open. In session B, attempt a reviewed schema change with a shorter lock budget than statement budget. Observe the blocking relationship, timeout error and transaction state. Do not run this experiment against customer traffic or terminate production sessions to make an editorial example succeed.
-- Session B: illustrative transactional DDL fixture, not run here.
BEGIN;
SET LOCAL lock_timeout = '2s';
SET LOCAL statement_timeout = '10s';
ALTER TABLE fixture_items ADD COLUMN review_note text;
COMMIT;
-- On error: ROLLBACK before reusing this connection.The two and ten second values are synthetic test inputs. They are not deployment recommendations or measured downtime limits. The expected lock timeout should be distinguished from statement cancellation, deadlock detection and connection loss. Save SQLSTATE, command identity and a sanitized blocker reference so an operator can tell which budget actually fired.
| Observation | Meaning | Operator action |
|---|---|---|
| Lock wait expires first | Lock budget reached | Rollback and inspect blocker |
| Statement deadline expires | Overall command budget reached | Inspect work and transaction |
| Connection disappears | Outcome may be uncertain | Inspect actual schema |
| Retry window selected | Known blocker resolved | Run reviewed bounded retry |
Inspect the queue, then release the fixture cleanly
While B is waiting, use a separate monitoring connection to inspect activity and locks. Verify whether later fixture reads or writes are queued behind the requested DDL lock. That observation matters because a waiting migration can affect the application even if the migration has not changed the schema. Then let the timeout occur, roll back B and close A's transaction. Confirm ordinary fixture requests resume.
A retry policy should be bounded and owned. Repeatedly retrying the same DDL without inspecting the blocker can turn a short budget into prolonged contention. Prefer a clear failed migration record and an operator decision about the next safe window. If the migration partially completed through multiple statements or nontransactional operations, inspect the actual schema before rerunning. A failed command does not describe the entire migration state.
Make rollback claims specific
For transactional DDL, test which changes disappear after rollback. For concurrent index creation or multi-phase backfills, record intermediate objects and cleanup instructions separately. Code rollback does not undo every database effect. Keep old and new application compatibility in view so a schema change can be retried or deferred without breaking the running version.
No PostgreSQL lock lab was executed for this article. The worksheet is a proposed experiment and the available local runtime did not provide a database daemon. A useful migration approval artifact contains the exact command, budgets, expected lock mode, observed blocker trace from a test database and recovery procedure. That makes the decision reviewable without claiming that a generic timeout setting guarantees zero disruption.
CI/CD for a small team: ship a known build and rehearse recovery
Observability for small teams: start with the failed customer task
Bring one API or data workflow and a sanitized failure example for a bounded implementation assessment.
How should PostgreSQL migrations use lock_timeout and statement_timeout?
Set a shorter lock-wait budget and a separate statement budget for the exact operation, then test blocking, rollback and retry behavior on a disposable database.
Does lock_timeout limit the entire statement?
No. It limits lock-acquisition waits; statement_timeout covers the statement’s elapsed time.
Can every migration run inside one transaction?
No. Check the exact command; some concurrent operations have different transaction and cleanup requirements.