Two of the worst PostgreSQL incidents I have been on call for had the same shape. Not corruption, not replication failure, nothing exotic. In one, someone ran an unindexed analytical query against production from a BI tool at ten in the morning, and it scanned and hashed for forty minutes while the connection pool filled with waiting requests. In the other, a developer opened a transaction in psql on a Friday, ran one UPDATE, and went home; by Saturday night that idle-in-transaction session was holding a lock that blocked an autovacuum and half the batch jobs, and its transaction ID horizon was quietly pinning dead tuples across the whole database. Both outages were entirely preventable with three settings that ship with PostgreSQL and that too many production databases still do not have configured.
The three are statement_timeout, lock_timeout, and idle_in_transaction_session_timeout. They protect against different failure modes, and you need all three, because a runaway query, a blocked DDL, and a forgotten transaction are three distinct ways to ruin a weekend.
statement_timeout: the runaway query killer
statement_timeout aborts any statement that runs longer than the given number of milliseconds, measured from when the command arrives at the server. The default is 0, meaning no limit, which is the correct default for a general-purpose tool and the wrong default for a production application role. The clean way to deploy it is per role, because different workloads have different ideas of "too long":
ALTER ROLE app_user SET statement_timeout = '5s';
ALTER ROLE reporting SET statement_timeout = '15min';
ALTER ROLE migration_user SET statement_timeout = 0;Per-role settings live in pg_db_role_setting and take effect at connection time, so no restart is needed. Sessions can override within their connection with SET statement_timeout, and SET LOCAL scopes the change to the current transaction, which is the safe pattern inside stored procedures that legitimately need longer. Two cautions from experience. First, anything that runs schema migrations or large backfills needs an explicit exemption, because a statement_timeout inherited from the application role will kill a long CREATE INDEX or data fix halfway through; the migration role above exists for exactly that reason. Second, if you use transaction-mode connection pooling, be careful with plain SET in application code, because it can leak to the next borrower of the connection. Prefer SET LOCAL, or set the value on the role and leave it alone.
Choosing the number: measure the role's normal traffic first. pg_stat_statements gives you mean, stddev, and max execution time per query, and your application metrics or log-based tooling supply the true p99; set the timeout an order of magnitude above the slow end of normal. A web role whose p99 is 200 milliseconds gets 5 seconds, not 50. The timeout exists to catch the query that has gone wrong, not to enforce an SLA, and when it fires, the error in the log names the offending statement, which is exactly what you want during an incident.
lock_timeout: the DDL pileup preventer
Here is the failure mode. Someone runs ALTER TABLE on a hot table. The ALTER wants an ACCESS EXCLUSIVE lock, one long-running query holds a conflicting lock, so the ALTER waits. Here is the part people miss: every new query that touches that table now queues behind the waiting ALTER, even plain SELECTs that would have been served instantly a second earlier. Within a minute the connection pool is exhausted, the dashboard says the database is down, and the postmortem says one schema change was willing to wait forever. lock_timeout puts a bound on how long any statement will wait to acquire a lock:
SET lock_timeout = '2s';
ALTER TABLE events ADD COLUMN source text;With lock_timeout set, the DDL fails fast after two seconds of waiting, the application never notices, and you retry the migration in a loop until it lands in a quiet moment. I set this in every migration script and every interactive session where I might touch a hot table. It also deserves a place on operational roles: an application job that occasionally takes an explicit LOCK or performs an in-place schema tweak should fail and retry rather than queue the world behind it. Note what lock_timeout does not cover: it bounds lock acquisition, not execution, so it complements statement_timeout rather than replacing it.
idle_in_transaction_session_timeout: the forgotten BEGIN
An idle-in-transaction session is poison in three ways. It holds whatever locks it has taken. Its snapshot prevents autovacuum from removing any row version that died after the transaction started, so bloat accumulates globally, not just on the tables it touched. And left long enough, its old transaction ID drags the cluster toward the wraparound horizon, at which point PostgreSQL starts taking defensive measures nobody enjoys. The Friday-afternoon psql session from my incident is the stereotype, but application bugs do it too: a code path that begins a transaction, throws before committing, and returns the connection to the pool mid-transaction is the same poison with better plausible deniability.
idle_in_transaction_session_timeout, available since PostgreSQL 9.6, terminates any session that sits idle inside an open transaction longer than the limit. The connection is closed and the transaction rolls back:
ALTER ROLE app_user SET idle_in_transaction_session_timeout = '30s';Thirty seconds is aggressive; pick whatever your application can genuinely tolerate, but pick something. PostgreSQL 14 added idle_session_timeout for the cousin problem, connections that sit idle outside any transaction forever, which matters mostly for connection hygiene on servers without an external pooler. Before you set either, find out what you currently have:
SELECT state, count(*),
max(now() - state_change) AS longest
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY state
ORDER BY longest DESC;If that query shows sessions idle in transaction for hours, you have found tomorrow's incident today. Terminate the offenders with pg_terminate_backend, fix the leak at the source, then set the timeout as the permanent guardrail.
Why these three together
Each timeout covers a hole the others leave open. statement_timeout does include time spent waiting on locks, which is precisely why lock_timeout still earns its place: it fires first, bounds just the lock wait, and lets a migration fail fast and retry instead of consuming a long statement's entire budget. Neither of them touches a session holding locks while idle between statements, because no statement is running, and neither touches the connection that drifts outside a transaction entirely. Rolled out per role, sized from measured latency, and logged when they fire, the three of them turn the most common self-inflicted PostgreSQL outages into log lines instead of pages.
Watching the guardrails with MonPG
Guardrails that fire rarely are the ones nobody graphs, and that is backwards: you want to know when a timeout kills something, what it killed, and whether idle-in-transaction sessions are trending upward before the limit has to do its job. MonPG watches production PostgreSQL today, from long-running queries and lock chains to wait events and session states like idle in transaction, so a forgotten BEGIN shows up on a dashboard long before it shows up in a postmortem. The PostgreSQL monitoring page describes the workflow, and the post on lock manager contention is a good next read if lock queues are your recurring pain.