Statement Timeouts Behind a Pooler #

A team sets statement_timeout to thirty seconds to stop runaway reports, and a report runs for two hours anyway. The setting was applied — they can see it in the configuration — but not to the session that ran the query, because a pooler sat in between and the two do not share sessions the way anyone assumed.

Pooling’s effect on session state is covered in application-side pools and server-side state. This page is about timeouts specifically, which fail in ways that are quiet rather than loud.

The Condition #

statement_timeout is a session GUC: it aborts any statement running longer than the value, measured from when the statement starts executing. Its companions matter too — lock_timeout bounds waiting for a lock, and idle_in_transaction_session_timeout bounds a transaction left open without work.

Three things make it unreliable through a pooler:

The result is a class of incident where a timeout exists, is visible in configuration, and does nothing.

Four places a timeout can live Four layers. A role default applies to every session opened by that role and is the most reliable place for a baseline. A connection string option applies to sessions the pooler opens. SET LOCAL inside a transaction applies precisely to that transaction. PgBouncer's own query_timeout closes the client connection but does not stop the backend. role default (ALTER ROLE) every session of that role connection string options sessions the pooler opens SET LOCAL in a transaction exactly that transaction PgBouncer query_timeout client only; backend keeps running only the first three stop the server doing work

Annotated Evidence #

What the session actually has:

-- run through the application's own connection path, not psql
SHOW statement_timeout;
SHOW lock_timeout;
SHOW idle_in_transaction_session_timeout;
 statement_timeout
-------------------
 0                  ← no timeout at all, despite the configuration

The query that should have been stopped:

SELECT pid, now() - query_start AS runtime, state, left(query, 60)
FROM pg_stat_activity WHERE state = 'active' AND now() - query_start > interval '30s';
  pid  |  runtime  | state  |                   query
-------+-----------+--------+--------------------------------------------
 41204 | 02:04:12  | active | SELECT customer_id, sum(total) FROM orders…
-- two hours into a query a thirty-second timeout was supposed to abort

And the pooler-level timeout that fired without helping:

-- application log
ERROR: server closed the connection unexpectedly

-- server, at the same moment
pid 41204 | 02:04:12 | active | SELECT customer_id, sum(total) FROM orders…
-- the client is gone; the backend is still working
Three checks that locate the gap Three items are annotated. SHOW statement_timeout returning zero on the application's own connection proves the setting never reached it. A long running active query alongside that zero confirms the consequence. A client-side connection error with the backend still active indicates a pooler timeout rather than a server one. SHOW statement_timeout → 0 on the app path the setting never arrived active query running far past the intended limit the consequence client error, backend still active a pooler timeout, not a server one always check from the application's connection, not from psql

Step-by-Step Resolution #

  1. Measure from the application’s own connection. A statement issued through the application’s pool that runs SHOW statement_timeout is the only reliable evidence; psql takes a different path and often a different role.

  2. Set the baseline as a role default, which survives pooling because it applies whenever a session is opened for that role:

    ALTER ROLE app_web SET statement_timeout = '30s';
ALTER ROLE app_web SET lock_timeout = '3s';
ALTER ROLE app_web SET idle_in_transaction_session_timeout = '60s';
ALTER ROLE app_reports SET statement_timeout = '30min';

  3. Give different workloads different roles. A web role and a reporting role with different limits is far more robust than one role whose timeout is adjusted per request.

  4. Override per transaction with SET LOCAL where a single operation genuinely needs longer, so the change cannot outlive it.

  5. Do not rely on PgBouncer’s query_timeout to protect the server. It protects the client. Keep it as a backstop, set slightly above the server-side limit so the server aborts first and the error is the accurate one.

  6. Add idle_in_transaction_session_timeout. An idle open transaction blocks vacuum and holds locks — it does more damage than a slow query, and nothing else bounds it.

  7. Verify under load, not in isolation: check that long statements actually abort with canceling statement due to statement timeout in the log.

Per-request SET against role defaults Setting a timeout per request with a bare SET may reach a different backend under transaction pooling, persists on whichever backend it reached, and is easy to omit in a new code path. A role default applies to every session opened for that role, cannot leak between roles, and needs no application code at all. bare SET per request may reach a different backend persists for other requests easy to forget in new code invisible in configuration role default applies to every session cannot leak across roles no application code needed visible in pg_db_role_setting use SET LOCAL when one transaction truly needs an exception

Before and After #

-- BEFORE: timeout set in the pooler configuration only
SHOW statement_timeout → 0
report runs 2 h 04 m; client disconnected after 30 s, backend kept working

-- AFTER: ALTER ROLE app_web SET statement_timeout = '30s'
SHOW statement_timeout → 30s
ERROR: canceling statement due to statement timeout    (after 30 s, backend stopped)

Choosing the values #

A timeout that is too generous protects nothing and one that is too tight turns a slow afternoon into an outage, so the values are worth deriving rather than guessing. pg_stat_statements gives the distribution directly: for each role’s statements, the maximum execution time under normal conditions is the floor, and a limit at several times that leaves room for a cold cache without permitting a runaway.

lock_timeout deserves a much smaller value than statement_timeout, and for a different reason. A statement waiting on a lock is not doing work; it is queueing, and behind it a queue of its own is usually forming. Failing fast and retrying is almost always better than waiting, particularly for the migration statements that take ACCESS EXCLUSIVE locks and can block an entire table’s traffic while they wait.

idle_in_transaction_session_timeout is the one most often left unset and the one with the widest blast radius: an open transaction holds back vacuum across the whole database, which is how a forgotten BEGIN in a debugging session becomes an anti-wraparound vacuum a month later.

Timeouts and plan choice #

Timeouts do not change plans, but they change which plans are survivable, and that has a design consequence worth stating. A plan whose cost is dominated by a nested loop over a badly estimated inner relation degrades gradually as data grows: it is fine, then slow, then catastrophic. With a timeout in place the catastrophic case becomes a fast, loud failure instead of a silent hour of saturated I/O — which is a better outcome, and is often how a bad plan gets noticed at all.

That makes the timeout part of the detection apparatus rather than only a safety measure. A rising count of canceling statement due to statement timeout errors in the log, grouped by statement, is a plan-regression signal in the same family as the statement-statistics trends in tracking plan changes with pg_stat_statements, and it arrives faster because it is an error rather than a trend.

The failure mode to avoid is raising the timeout in response. A statement that starts hitting a limit it used to clear comfortably has changed behaviour, and the limit is reporting that accurately.

Common Pitfalls #

Setting the timeout in the pooler only. The server never enforces it. Diagnostic signal: SHOW statement_timeout returning 0 from the application. Fix: role defaults.

A bare SET under transaction pooling. It lands on an arbitrary backend and persists there. Diagnostic signal: unrelated requests timing out. Fix: SET LOCAL, or role defaults.

One timeout for every workload. Reports need minutes, web requests need seconds. Diagnostic signal: a limit loose enough to be useless. Fix: separate roles.

No idle_in_transaction_session_timeout. Open transactions block vacuum indefinitely. Diagnostic signal: vacuum running but relfrozenxid not advancing. Fix: set it for every application role.

Frequently Asked Questions #

Why does my statement_timeout not apply behind PgBouncer? #

Because it was set somewhere the application’s session never sees — the pooler’s own connection, or a bare SET that reached a different backend under transaction pooling. Setting it as a role default with ALTER ROLE applies it to every session for that role.

Does PgBouncer’s query_timeout stop the query on the server? #

No. It closes the client connection; the backend continues running the statement, holding its locks and consuming I/O. Use a server-side statement_timeout as the real limit and the pooler’s timeout only as a backstop.

Where should statement_timeout be set in a pooled application? #

As a role default per workload role, with SET LOCAL inside a transaction for the rare operation that legitimately needs longer.

Up: Connection Pooling and the Plan Cache