2026-10-01 · 8 min read
- postgresql
- sql
- timeouts
PostgreSQL has several clocks that people collapse into one timeout setting. statement_timeout bounds a single statement. lock_timeout bounds lock waits inside that statement. idle_in_transaction_session_timeout bounds silence while a transaction is still open. idle_session_timeout bounds silence when no transaction is open.
Each one protects a different neighbor. Statement timeout protects the server from a query that runs too long. Lock timeout protects other sessions from waiting behind a blocked DDL or update. Idle-in-transaction timeout protects snapshots and row locks that a client forgot to commit.
Decide what should be cancelled
Use statement_timeout for application queries whose worst case you can name, such as an API request that must return within a few seconds. The timer starts when the command arrives and includes planning and lock waits. A value of zero disables it.
Use lock_timeout when a statement should give up waiting for a relation, row, or advisory lock instead of sitting in the queue. It is applied to each lock acquisition. It does not replace deadlock detection. If it is shorter than statement_timeout, a blocked statement fails with a lock timeout first.
- Keep migrations on a short lock_timeout so they fail instead of queueing traffic.
- Keep user-facing statements on a statement_timeout the API can explain.
- Do not use statement_timeout as a substitute for an idle-transaction limit.
- Set idle_session_timeout separately when pooled logins sit outside transactions.
Read the message and the session that survived
Statement timeout and lock timeout cancel the statement. The session stays up, and an open transaction is still open until the client rolls it back or the driver does. Idle-in-transaction timeout terminates the connection. The transaction is rolled back by the server, and the client sees a broken connection rather than a failed query it can retry in place.
Capture the message text. canceling statement due to statement timeout, canceling statement due to lock timeout, and terminating connection due to idle-in-transaction timeout are different incidents. Then read current_setting on a session that uses the same role, database, and pooler path as the failure.
Put the setting where the pooler will keep it
postgresql.conf, ALTER DATABASE, and ALTER ROLE apply when a server connection starts as that database and role. SET applies to one server session. SET LOCAL applies to one transaction. A transaction-mode pooler checks server connections out per transaction, so SET from the previous request is gone.
Session-mode pooling can leak SET statement_timeout from one client to the next if the pooler does not reset session state. Prefer a role setting for the limit you always want, and SET LOCAL only for a migration or admin statement that must be stricter than the role.
Test the wait you think you configured
Build three fixtures. A query that sleeps longer than statement_timeout should cancel with a statement timeout. A second session that holds a row lock should make an update fail with a lock timeout when that limit is shorter. A transaction that issues BEGIN and then sleeps in the client should die with an idle-in-transaction timeout even though no statement is running.
Repeat the fixtures through the pooler, not only through psql on the database host. Export pg_settings for the four timeout names and record the source column.
Review the values in local tools
Use Flashman's SQL formatter to lay out the ALTER ROLE and SET statements, the timestamp converter to place log lines against the limit, the JSON formatter for pg_stat_activity and pg_settings exports, and diff to compare staging and production sources.
Ship the change with the message you expect in the logs. A dashboard that only counts the word timeout will keep paging the wrong owner.