Postgres Timeouts: statement_timeout, lock_timeout and Friends

Out of the box, PostgreSQL will wait forever. A query can run for six hours, a transaction can sit idle holding locks all weekend, and an ALTER TABLE can wait indefinitely for a lock — blocking everything behind it while it waits.

Timeouts are how you put limits on that. They're all off (0) by default, which is the right default for a general-purpose database and the wrong one for most web apps.

The five timeouts

Setting	Cancels / terminates	Since
statement_timeout	Any single statement running longer than this	Forever
lock_timeout	A statement waiting longer than this to acquire a lock	9.3
idle_in_transaction_session_timeout	A session that has an open transaction but is doing nothing	9.6
idle_session_timeout	A session idle outside a transaction	14
transaction_timeout	A whole transaction (or single-statement implicit transaction) longer than this	17

statement_timeout and lock_timeout cancel the statement (error 57014, "canceling statement due to statement timeout" / 55P03 "lock timeout"); the transaction is aborted but the connection survives. The idle timeouts and transaction_timeout terminate the session.

The outage they prevent: the lock queue

Here's the scenario that takes down more production Postgres databases than almost anything else:

1. A long-running analytics query (or an idle-in-transaction session) holds an ACCESS SHARE lock on orders.
2. A deploy runs ALTER TABLE orders ADD COLUMN note text — a fast operation, but it needs an ACCESS EXCLUSIVE lock. It waits behind step 1.
3. Every new SELECT on orders now queues behind the ALTER, because Postgres grants locks in order and they conflict with the pending exclusive lock.
4. Connections pile up, the pool is exhausted, and the app is down — because of a migration that would have taken 5 milliseconds.

lock_timeout breaks the chain: the ALTER gives up after a few seconds, the queue drains, and you retry later. (Postgres migrations on large tables)

SET lock_timeout = '3s';
ALTER TABLE orders ADD COLUMN note text;

Wrap migrations in a retry loop with backoff. Most migration tools let you set this per migration; if yours doesn't, put the SET at the top of the migration file.

Finding what's blocking

SELECT pid,
       pg_blocking_pids(pid) AS blocked_by,
       state,
       now() - xact_start AS xact_age,
       wait_event_type, wait_event,
       left(query, 80) AS query
FROM pg_stat_activity
WHERE datname = current_database()
ORDER BY xact_start NULLS LAST;

Look for sessions with state = 'idle in transaction' and a large xact_age — that's usually the root. pg_cancel_backend(pid) cancels its current query; pg_terminate_backend(pid) kills the session. (Postgres deadlocks)

statement_timeout: bound every query

A runaway query — a missing WHERE, a bad plan, an N+1 that turned into a cross join — burns CPU and I/O and holds a snapshot that blocks vacuum cleanup. For web requests, nothing should run longer than your HTTP timeout anyway; the client gave up long ago. (504 Gateway Timeout)

Set it per role so different workloads get different limits:

ALTER ROLE web_app      SET statement_timeout = '15s';
ALTER ROLE background   SET statement_timeout = '5min';
ALTER ROLE analytics    SET statement_timeout = '30min';
ALTER ROLE migrator     SET statement_timeout = 0;      -- but always set lock_timeout

Override for a single known-slow operation:

BEGIN;
SET LOCAL statement_timeout = '2min';
-- the slow report
COMMIT;

SET LOCAL reverts at transaction end, which is what you want with connection pools — a plain SET would leak to whatever request gets that connection next. (Postgres connection pooling)

Avoid setting statement_timeout globally in postgresql.conf: it applies to maintenance, pg_dump and replication-adjacent tooling too. Some tools set their own, but don't rely on it.

idle_in_transaction_session_timeout: the forgotten transaction

An idle-in-transaction session is a connection that ran BEGIN (or an ORM did) and then… stopped. Typical causes:

• Code that does HTTP calls or other slow work between queries inside a transaction.
• An exception path that skips COMMIT/ROLLBACK and returns the connection to a pool.
• A human in psql who typed BEGIN; and went to lunch.

It's quietly destructive: it holds its locks, and it holds back the xmin horizon, so vacuum can't remove dead rows anywhere in the database. Tables bloat, HOT updates stop working, and in extreme cases you approach transaction ID wraparound. (Postgres vacuum and bloat, Postgres HOT updates)

ALTER ROLE web_app SET idle_in_transaction_session_timeout = '60s';

The session is terminated and the client sees an error on its next use. That's intended — the bug was already there; now it's visible.

transaction_timeout (Postgres 17+)

statement_timeout limits each statement, but a transaction of a thousand 1-second statements still holds locks for 17 minutes. transaction_timeout caps the whole thing:

ALTER ROLE web_app SET transaction_timeout = '2min';

Set it longer than statement_timeout and idle_in_transaction_session_timeout for the same role. The docs are explicit: if transaction_timeout is shorter than or equal to either of them, the longer one is simply ignored — so you lose the more specific error that tells you whether a query was slow or a transaction was left idle. Like the idle timeouts, it terminates the session rather than just cancelling the statement.

idle_session_timeout: use carefully

It terminates connections that are idle outside a transaction. That's fine for humans connected directly, and harmful for connection pools, which keep idle connections open on purpose — the pool hands out a dead connection and the next query fails. If you use it, set it on human/admin roles, not on the app's role, or make sure the pool's idle lifetime is shorter.

Client-side timeouts are different

Driver timeouts (connectionTimeoutMillis, query_timeout in node-postgres, socket_timeout) stop the client waiting; they don't necessarily stop the server working. Unless the driver sends a cancel request, the query keeps running in Postgres. Prefer server-side statement_timeout as the real limit, with client timeouts slightly longer as a backstop.

A sensible starting point for a web app

ALTER ROLE web_app SET statement_timeout = '15s';
ALTER ROLE web_app SET lock_timeout = '5s';
ALTER ROLE web_app SET idle_in_transaction_session_timeout = '60s';
ALTER ROLE web_app SET transaction_timeout = '2min';  -- Postgres 17+; keep it above the two above

ALTER ROLE migrator SET lock_timeout = '3s';

Role settings apply to new sessions — restart the app or recycle the pool afterwards. Then watch logs for timeout errors: each one is a slow query or a transaction bug you didn't know about. (pg_stat_statements)

---

EasySpawn runs Postgres on your own server, so you can set per-role timeouts like these — and Claude Code can help you trace an idle-in-transaction session back to the code that left it open. See how it works or join the waitlist.

Related: Postgres Migrations on Large Tables · Postgres Deadlocks · Postgres Connection Pooling · Postgres Vacuum and Bloat