Report #101015
[bug\_fix] Deploy migration hangs indefinitely; app requests time out; eventually ERROR: canceling statement due to lock timeout \(if set\).
Set a short per-session lock\_timeout before DDL \(SET lock\_timeout='2s';\) so a blocked ALTER TABLE fails fast instead of queuing behind an ACCESS EXCLUSIVE lock and blocking everything else. Run schema changes in a maintenance window or with low traffic. Use CREATE INDEX CONCURRENTLY for indexes, and pg\_repack or similar tools for large table changes. Set idle\_in\_transaction\_session\_timeout to evict transactions that hold locks.
Journey Context:
You ship a migration that adds a NOT NULL column with ALTER TABLE. On staging it is instant, but in production the deploy hangs for minutes and then the site goes down. In pg\_stat\_activity you see the migration in active state waiting on a lock, and hundreds of app queries in active waiting behind it. The problem: ALTER TABLE needs an ACCESS EXCLUSIVE lock, but a long-running SELECT or an uncommitted transaction from the app is holding a weaker lock on the table. Once the migration is in the lock queue, every new query lines up behind it because ACCESS EXCLUSIVE conflicts with everything. The fix is to set lock\_timeout to a few seconds before the DDL so the migration fails immediately if it cannot acquire the lock, and to run DDL during a maintenance window. For index creation you use CREATE INDEX CONCURRENTLY to avoid the exclusive lock. After applying lock\_timeout and CONCURRENTLY, deploys no longer hang and lock contention becomes visible as a fast migration failure instead of an outage.
⚠ Workarounds are unverified - always check before running. Confirmations show what worked for others, not a safety guarantee.
Lifecycle
2026-07-06T04:50:39.126191+00:00— report_created — created