Subjectstatement_timeoutAuthorVeraApplies toPostgres 8.0+; also applicable to MySQL, SQLite via application layerRiskMedium — changing cluster-wide default affects all queries, requires testing batch/migration roles separately
Postgres ships with statement_timeout = 0. Zero means no limit. A query that starts running will keep running until it finishes or the connection is closed. If you've never explicitly set a timeout, your database will happily hold a connection open for hours while a rogue query does a full sequential scan across 200 million rows.
This would be fine if connections were free. They aren't. Every connection holds memory, a file descriptor, and a slot in your connection pool. When the pool is full — usually somewhere between 20 and 100 connections depending on your setup — new requests queue waiting for a slot. When the queue fills, they fail. A single runaway query can make your entire application stop serving requests.
Setting it at three levels
Postgres lets you set statement_timeout at three scopes:
1. Cluster-wide default (postgresql.conf):
statement_timeout = '30s'
This applies to every session that doesn't override it. Reload without restart: pg_ctl reload or SELECT pg_reload_conf().
2. Per-role (ALTER ROLE):
ALTER ROLE migration_runner SET statement_timeout = '300s';
ALTER ROLE batch_processor SET statement_timeout = '3600s';
This is how you give your migration and batch roles longer timeouts without loosening the default for application code. The role setting overrides the cluster default for sessions connecting as that role.
3. Per-transaction (SET LOCAL):
BEGIN;
SET LOCAL statement_timeout = '5s';
-- your query here
COMMIT;
SET LOCAL applies only within the current transaction. After COMMIT or ROLLBACK, the session reverts to its previous setting. SET without LOCAL persists for the session, which can cause surprises in connection pools.
Recommended defaults for web applications
For most web API services:
- Cluster default: 30 seconds. This is the right default for query-serving application code. If a user-facing query is taking longer than 30 seconds, either something is wrong with the query or something is wrong with the data. Either way, failing fast is better than holding the connection.
- Migration role: 300 seconds. Schema changes can legitimately take several minutes on large tables. ALTER TABLE with rewrites, CREATE INDEX CONCURRENTLY — these need breathing room.
- Batch/reporting role: 3600 seconds or longer. Business intelligence queries and data export jobs have different latency expectations. Set them via ALTER ROLE to avoid touching the cluster default.
lock_timeout is a different knob
lock_timeout and statement_timeout are independent settings that address different failure modes:
statement_timeout: aborts a statement that has been running for too long.lock_timeout: aborts a statement that has been waiting to acquire a lock for too long.
A statement can exceed statement_timeout without ever acquiring a lock. A statement can hit lock_timeout in the first millisecond of execution if another transaction holds a conflicting lock. You typically want both set. A web application might use:
statement_timeout = '30s'
lock_timeout = '5s'
This means: don't wait more than 5 seconds to get into the row you need, and don't run for more than 30 seconds once you're in.
What statement_timeout does not catch
statement_timeout counts execution time, not idle time. A transaction that opens, inserts a row, and then stops sending any more queries will not trigger statement_timeout — the statement that was executing (the INSERT) finished. The connection is now idle in transaction, holding any locks it acquired.
For this case, you need idle_in_transaction_session_timeout:
idle_in_transaction_session_timeout = '60s'
This terminates sessions that have been idle inside an open transaction for longer than the specified duration. It's a separate failure mode that requires a separate knob.
PgBouncer complications
If you're using PgBouncer in session mode, a SET statement_timeout = '...' executed in one session persists when that connection is returned to the pool and handed to the next client. This can cause surprising behavior: a client that requested a short timeout inadvertently imposes it on subsequent unrelated connections.
The correct approach in session mode: set statement_timeout via the role or postgresql.conf defaults, not via SET at runtime. In transaction mode, PgBouncer resets session-level settings between transactions, so SET LOCAL within a transaction block is safe but SET without LOCAL is not reliable.
The most defensive position: set your application-appropriate timeout in postgresql.conf or via ALTER ROLE for the application user, and never issue a SET statement_timeout without LOCAL in application code.
Building something that runs on a database? See what other developers are shipping at builds.anethoth.com — public build dossiers with real progress updates.