Question: Configuration
How do I set statement_timeout safely?
Answered in the first paragraph. Last updated .
Attach it to the role or to the database, not to the configuration file. The documentation advises against the global form and the reason is practical rather than stylistic: a single value would have to be short enough to protect the server from a runaway query and long enough for the nightly maintenance job, and no such value exists. Give the application role a short limit and the maintenance role a generous one.
The four timeouts, which are not interchangeable
The statement limit applies to each statement separately. A transaction issuing ten statements can therefore run for ten times the limit without ever breaching it, which surprises people who set it expecting a bound on transaction length.
The lock limit bounds only the time spent waiting for a lock. It is the one that protects a schema change from queueing behind a long query and taking the table down with it, and it only ever fires if it is shorter than the statement limit, since otherwise the statement limit gets there first.
The transaction limit does bound the whole transaction including the time the client spends thinking between statements, and when it is the shortest of the three the others are ignored.
The fourth is the one for sessions sitting inside an open transaction doing nothing, which is a different failure with a different cost, covered in idle in transaction.
Rolling it out without breaking a job
Measure first. The slow query log gives the distribution of what your application actually runs, and the limit belongs above the slowest statement you intend to keep rather than at a round number somebody liked. How to log slow queries covers getting that distribution.
Then apply it to the role and wait for sessions to turn over, since a setting attached to a role takes effect at the next connection rather than immediately.
Two exemptions to plan for. Index builds, vacuums and restores are statements too, and they are killed by the same limit, so the role that runs maintenance needs its own value or its own session override. And a cancellation arrives at the client as an error, which means the application has to treat it as a retryable condition rather than as a bug; what that looks like from the other side is in is it safe to kill a Postgres query.
The lock limit deserves its own rollout on migrations specifically, and lock contention and blocking trees covers why that one matters more than the others on a busy table.