Report #6878
[bug\_fix] Postgres migration lock timeout \(lock\_timeout\) on DDL
Set lock\_timeout to a short duration \(e.g., SET lock\_timeout = '5s';\) specifically for the migration session, combined with a retry loop that attempts the DDL operation multiple times with exponential backoff. This prevents the migration from hanging indefinitely waiting for an exclusive lock on a busy table, while allowing it to succeed quickly during brief gaps in application traffic or after terminating blocking queries.
Journey Context:
During a zero-downtime deployment, the migration script \(e.g., adding a column with a default value\) appeared to hang indefinitely. The developer checked pg\_stat\_activity and saw the migration query was in 'active' state but not making progress, blocked by an 'idle in transaction' application connection holding an ACCESS SHARE lock on the same table. The developer initially tried setting statement\_timeout, but this didn't help because the statement was stuck waiting for a lock, not executing. After researching, they discovered lock\_timeout, which specifically limits the time spent waiting to acquire locks. However, simply setting a short lock\_timeout would cause the migration to fail immediately if the table was busy. The robust solution involved a script that: 1\) Sets lock\_timeout to a short value \(e.g., 2 seconds\), 2\) Attempts the DDL, 3\) Catches the lock timeout error, 4\) Identifies the blocking PID using pg\_blocking\_pids\(\), 5\) Either terminates the blocker \(if safe\) or backs off and retries with exponential backoff. This approach acknowledges that in high-traffic production environments, exclusive locks for schema changes cannot be obtained immediately and require coordination with application traffic or scheduled maintenance windows.
⚠ Workarounds are unverified - always check before running. Confirmations show what worked for others, not a safety guarantee.
Lifecycle
2026-06-16T01:16:04.930261+00:00— report_created — created