Choosing a Postgres Lock Timeout and Retry Policy

You need two numbers, and they do different jobs.

lock_timeout caps how long a single statement may wait to acquire a lock. It protects the rest of the database from your migration. Set it to a small value, in the 2s to 5s range, because the cost of failing fast is a retry, while the cost of waiting is queued writers.

statement_timeout caps how long a single statement may run once it has the lock. It protects the migration from hanging forever and protects you from holding ACCESS EXCLUSIVE while a scan crawls. Set it higher than the longest step you legitimately expect, not higher than your worst nightmare. It needs to be greater than lock_timeout, because the lock wait counts toward statement execution in practice and you want the lock-specific error, 55P03, to fire first rather than a generic cancellation.

The table below turns that into a decision rule. The right-hand column is the one people skip.

Two rules follow from the table. First, the more the step rewrites or scans, the more you should be reaching for a different technique rather than a bigger timeout. Adding NOT NULL safely is a sequence, not a statement: add a NOT VALID check constraint, validate it under a weak lock, then promote it, as Atlas's lock-safe guide and RunxBuild both lay out. Second, the same engineering applies to every migration you write, not just the risky ones. doctolib's safe-pg-migrations exists because the safe form of each PostgreSQL migration is bookkeeping a team will get wrong by hand.

To pick the number from data rather than taste, sample lock waits from pg_stat_activity and pg_locks during a normal week. If p99 lock acquisition on your busy tables is 40ms, a 3s timeout gives you roughly 75 times headroom and still fails an order of magnitude before user-visible timeouts. If you see waits of 800ms routinely, a 1s timeout will fail on healthy traffic and train everyone to ignore it.

The settings must not leak into application sessions, and they must not require a server restart or a role change. SET LOCAL is the tool: it applies inside the current transaction and reverts at commit or rollback, which Stack Overflow's discussion of per-migration statement_timeout and the PostgreSQL client connection defaults documentation both make clear. One caveat from that thread is worth internalizing: the timeout applies per statement, so a SET and a pg_sleep inside a single execute call behave differently than two separate statements. Keep SET LOCAL on its own line.

BEGIN;
SET LOCAL lock_timeout = '3s';
SET LOCAL statement_timeout = '15s';

ALTER TABLE users ADD COLUMN last_login timestamptz;

COMMIT;

Two traps. First, CREATE INDEX CONCURRENTLY cannot run inside a transaction block, as Bytebase notes, so SET LOCAL is not available for it. Use a plain SET on a dedicated connection for those, and reset it afterwards, or set the values at the role level for the migration user and accept that they apply to every session that role opens. Second, be aware that concurrent index builds on the same table serialize anyway, which is another reason to retry rather than parallelize.

If a migration framework opens its own transaction, put the SET LOCAL statements first and keep all the DDL in that same transaction. If the framework commits between statements, SET LOCAL silently does nothing for the later statements. Verify by running the migration against a scratch database and querying SHOW lock_timeout; in the middle of it.

When the timeout fires, the error is specific. SQLSTATE 55P03 means lock_not_available: the lock could not be obtained within lock_timeout, with a detail line reporting the wait in milliseconds and a hint to close competing transactions, as documented in Bytebase's error reference. Catch that code and only that code. A statement_timeout cancellation arrives as 57014, which usually means the statement was doing real work and will do it again on retry. Retrying a 57014 blindly turns one slow migration into five slow migrations.

Before each retry, look at who holds the lock. The reference query joins pg_locks for ungranted locks against pg_locks and pg_stat_activity to pair blocked PIDs with blocking PIDs and show the blocking statement. Run it in a separate session, not the migration's transaction, because the migration has rolled back by then anyway.

What you are looking for is a queue, not a culprit. One session holding ACCESS EXCLUSIVE briefly is normal. Three sessions queued on pg_locks with NOT granted and an idle in transaction session at the front is not: it means traffic is already piling up behind your failed attempt or someone else's, and another immediate retry will make it worse. postgres.ai's lock analysis snippet builds the full blocking tree recursively, which is the clearest view of who is at the head.

The decision rule: if the blocking session is idle in transaction, the fix is that session, not your migration. Ask for it to be ended, or end it if it is yours to end. If the blocker is a genuine long query, back off and let it finish. If the blocker is another migration, stop and coordinate; two migrations racing for the same table will retry in lockstep forever.

The retry loop has four jobs: run the migration in its own short transaction, fail fast on lock acquisition, back off proportionally, and give up cleanly.

import random, time
import psycopg

LOCK_TIMEOUT = "3s"
STATEMENT_TIMEOUT = "15s"
MAX_ATTEMPTS = 5

def run_migration(conn, statements):
    for attempt in range(MAX_ATTEMPTS):
        try:
            with conn.transaction():
                with conn.cursor() as cur:
                    cur.execute(f"SET LOCAL lock_timeout = '{LOCK_TIMEOUT}'")
                    cur.execute(f"SET LOCAL statement_timeout = '{STATEMENT_TIMEOUT}'")
                    for stmt in statements:
                        cur.execute(stmt)
            return "ok"
        except psycopg.errors.LockNotAvailable as e:
            if attempt == MAX_ATTEMPTS - 1:
                raise
            base = 2 ** attempt          # 1, 2, 4, 8 seconds
            sleep_for = base + random.uniform(0, base / 2)
            print(f"lock not available ({e}); sleeping {sleep_for:.1f}s")
            time.sleep(sleep_for)

The numbers matter. A base of one second doubling to eight gives roughly fifteen seconds of patience across five attempts, most of it in the later steps where you would rather wait than hammer. The jitter, up to half the base, prevents two deployments started by the same pipeline from retrying in lockstep. MAX_ATTEMPTS exists so that a genuinely blocked table fails the deploy with a clear message instead of a migration process that lives forever. If the migration is not idempotent, wrap the statements so a partial success cannot double-apply; the transaction per attempt already covers that for transactional DDL.

Two refinements pay for themselves. First, before retrying, run the blocking-session query and abort immediately if the queue length exceeds a threshold you choose, because the retry cannot succeed while a queue is forming. Second, treat the final failure as a signal, not an incident: alert, keep the previous schema, and re-run manually during a quiet window. This is precisely the discipline that lets expand and contract changes ship continuously, since each step is itself small and safe to retry, as Prisma's guide to the pattern and Jasmin Fluri's overview describe.

A lock timeout does not hurt if the migration is short and restartable. That is a schema design decision as much as a timeout decision.

Break the change into expand, migrate, contract. Add the new nullable column with no default, deploy code that writes both old and new structures, backfill in batches, then remove the old column once nothing reads it, as the expand-and-contract literature and Harness's zero-downtime guide both set out. Each phase is a fast, separately retryable step. A batch backfill with a 2s statement_timeout per batch and a commit between batches can be interrupted and resumed at any point, which is the opposite of one enormous transaction holding a table for minutes.

Avoid mixing lock-hungry statements into the same transaction as slow work. If a single transaction does an ALTER TABLE and then a long backfill, it holds ACCESS EXCLUSIVE for the whole backfill, and no timeout on the lock acquisition will save you, because you already have the lock. The RunxBuild batching advice and the Bytebase prevention notes both point the same way: short transactions, small chunks, retries at a coarse granularity.

There is also a version-compatibility angle that pure lock mechanics miss. During a rolling deploy, old and new application versions hit the same schema, so every step must be safe for both, and a rule of thumb from the Hacker News discussion of migration safety is that no more than two software versions should share a database at once. A migration that needs to be retried for 15 seconds while application instances swap is far more likely to collide with traffic than one that runs during a quiet window; the retry loop is a safety net, not a scheduling strategy.

If your schema changes run from an agent, a build pipeline or a coding assistant, the retry logic and the lock inspection query are the kind of thing worth standardizing once. Quanta Skills is the library of installable skills built for exactly that: a coding agent gets the migration rule, the timeout policy and the retry loop without a human retyping them for each project.

A users table with live traffic. You add last_login timestamptz, nullable, no default, so the statement is catalogue-only and should take single-digit milliseconds. You set lock_timeout to 3s and statement_timeout to 15s.

Attempt one starts at 10:00:00. A reporting query holds ACCESS SHARE on users. Your ALTER requests ACCESS EXCLUSIVE and waits. At 10:00:03 the lock timeout fires, 55P03, and the transaction rolls back. Total exposure to writers: three seconds, during which ordinary reads queue behind the ALTER. That is bad but recoverable, and no worse than the p99 latency of the reporting query.

Backoff is one second with jitter, so attempt two starts around 10:00:04. The reporting query is still running. Same result. Attempt three starts around 10:00:07, attempt four around 10:00:13, attempt five around 10:00:25, each with a three second wait. Total worst-case dwell is roughly 40 seconds of intermittent queueing, after which the deploy fails with a clear message rather than an outage of unknown length.

The alternative without timeouts is the same reporting query running for six minutes while every write to users queues. That is the failure mode RunxBuild calls the lock queue problem nobody expects. Three seconds versus six minutes, chosen by one line.

If instead you had needed CREATE INDEX CONCURRENTLY on users, the failure mode changes. No writes block, but the build is slower, and an interrupted build leaves an invalid index that you must drop before retrying, per Bytebase's limitations list. Your retry loop needs a cleanup step for that case: check pg_index.indisvalid, drop if false, then retry.