Postgres lock timeout Strategies: Postgres lock_timeout Strategies: Fail Fast Instead of Cascading Wait Queues

Long holder (migration / idle-in-tx)
 -> waiter 1..N block on relation/row lock
 -> app pool exhausts
 -> upstream timeouts + retries amplify load
 -> "everything 500" without a single crash

-- OLTP app role: fail fast on locks and statements
ALTER ROLE app_oltp SET lock_timeout = '2s';
ALTER ROLE app_oltp SET statement_timeout = '15s';
ALTER ROLE app_oltp SET idle_in_transaction_session_timeout = '30s';

-- Migration role: longer lock budget, still bounded
ALTER ROLE app_migrate SET lock_timeout = '5s';
ALTER ROLE app_migrate SET statement_timeout = '10min';

// GOOD: set on checkout for Node (pg)
pool.on("connect", (client) => {
 client.query("SET lock_timeout = '2s'");
 client.query("SET statement_timeout = '15s'");
});

// BAD: rely on cluster defaults of 0 (wait forever)

-- Prefer non-blocking claim patterns for workers
UPDATE jobs
SET status = 'running', locked_at = now()
WHERE id = (
 SELECT id FROM jobs
 WHERE status = 'queued'
 ORDER BY id
 FOR UPDATE SKIP LOCKED
 LIMIT 1
)
RETURNING *;

try {
 await db.query("UPDATE accounts SET balance = balance - $1 WHERE id = $2", [amt, id]);
} catch (e: any) {
 if (e.code === "55P03") { // lock_not_available
 metrics.increment("pg.lock_timeout");
 throw new TransientError("lock_timeout"); // retry with jitter, or shed
 }
 throw e;
}

SELECT pid, usename, state, wait_event_type, wait_event,
 now() - query_start AS runtime, left(query, 120)
FROM pg_stat_activity
WHERE wait_event_type = 'Lock'
ORDER BY runtime DESC;