PgBouncer server_reset_query and DISCARD ALL: the hidden per-return overhead

Every time PgBouncer returns a server connection to the pool in session pooling mode, it runs a cleanup query on that connection before anyone else can use it. By default that query is DISCARD ALL, which wipes every piece of session state PostgreSQL is holding: temp tables, prepared statements, SET variables, advisory locks, cursors, LISTEN subscriptions. The reset is what makes connection sharing safe. It is also a backend round trip you pay for on every connection return, and it is rarely accounted for.

The cost is hard to attribute from PgBouncer’s console. SHOW STATS has no reset-query counter and does not isolate reset execution time; avg_query_time reports client queries. When DISCARD ALL is slow on the backend—for example because a client left behind dozens of temp tables to drop—the work appears as delayed connection reuse rather than as a separate metric. Teams can waste time looking for lock contention or missing indexes that do not exist.

This article covers when the reset query actually runs (the answer changed in PgBouncer 1.7 and a lot of configuration in the wild is wrong because of it), where the connection is held while the reset runs, and how to tune it deliberately instead of either cargo-culting DISCARD ALL everywhere or disabling it and leaking session state between clients.

What server_reset_query is and why it exists

Connection pooling only works if each client gets a connection that behaves like a fresh one. PostgreSQL sessions accumulate state: SET search_path, SET statement_timeout, prepared statements, temporary tables, advisory locks. If client A sets statement_timeout = 1 and the same backend connection is then handed to client B without cleanup, client B’s queries start timing out for no visible reason.

server_reset_query is PgBouncer’s answer: a SQL statement executed on a server connection when it is returned to the pool, before the connection is made available to another client. At that moment no transaction is in progress, so the reset runs in a clean context. The default is DISCARD ALL, the maximal cleanup: it resets everything session-scoped in one command. The official configuration documentation notes the tradeoff directly: DISCARD ALL cleans everything, but leaves the next client no pre-cached state.

There is a companion setting, server_reset_query_always (default 0). It exists because of a behavior change that still trips people up, covered below.

How it works

The mechanics per connection return, in session pooling mode:

flowchart LR
  A[Client transaction or session ends] --> B[Server connection returned to pool]
  B --> C{Reset required?}
  C -->|session mode| D[server_reset_query sent to backend]
  C -->|transaction mode, 1.7+| E[No reset by default]
  D --> F[Backend executes DISCARD ALL]
  F --> G[Connection idle and reusable]
  E --> G
  G --> H[Next client assigned]

Key points about the mechanism:

• The reset runs on the PostgreSQL backend, synchronously, while the connection transitions back to idle. Its execution time is real backend work, but PgBouncer does not add it to avg_query_time; that statistic counts client queries.
• The cost is not constant. DISCARD ALL on a clean session is nearly free. DISCARD ALL on a session with 40 temp tables, prepared statements, and accumulated SET state does real work: dropping temp relations, invalidating cached plans, releasing locks. The overhead scales with how dirty clients leave their sessions.
• Because PgBouncer is a single-threaded event loop, the reset also occupies a connection slot for its duration. A slow reset delays the connection’s return to the usable pool, which matters under turnover pressure.
• The reset runs per return, not per client. In session mode, returns are infrequent (one per client disconnect), so the amortized cost is usually small. The overhead becomes visible when session turnover is high, when sessions are dirty, or when server_reset_query_always = 1 is set in transaction mode, which forces a reset on every transaction boundary.

The version trap: what actually runs in your deployment

This is where most real-world configurations are silently wrong.

• server_reset_query_always was introduced in PgBouncer 1.6.1.
• Since PgBouncer 1.7, server_reset_query is not executed at all in transaction pooling mode, unless server_reset_query_always = 1 is set. The 1.7 changelog explicitly warns that this change was under-announced.
• A large amount of documentation and example configuration shows server_reset_query = DISCARD ALL in transaction-pooling configs. On any PgBouncer from 1.7 onward, that line does nothing by itself. It only takes effect if server_reset_query_always = 1 is also present.

So the first step in reasoning about this overhead is not tuning the query. It is establishing ground truth about your own config:

# Check pool mode and reset query behavior as actually loaded
psql -h 127.0.0.1 -p 6432 -U pgbouncer pgbouncer -Atc "SHOW CONFIG;" | grep -Ei "pool_mode|server_reset_query"

Three situations to distinguish:

1. Session mode, default settings. DISCARD ALL runs on every connection return. This is the only configuration where the reset overhead is on by default.
2. Transaction mode, default settings (1.7+). No reset runs. DISCARD ALL in your ini is decorative.
3. Transaction mode with server_reset_query_always = 1. The reset runs on every transaction return. The official docs describe this setting as a workaround for broken setups that use session features over transaction pooling, and warn that it changes non-deterministic breakage into deterministic breakage: clients always lose their state after each transaction. If this is set, you are paying the reset cost at the highest possible frequency.

If you are investigating a latency mystery, find out which of these three you are in before touching anything else.

Where the overhead shows up in production

There is no direct reset-query signature in SHOW STATS. Measure backend-side reset cost and correlate connection reuse. In particular, do not use avg_query_time as the reset-time measure: it reports client queries, while reset work delays reuse and can appear through sv_tested churn or avg_wait_time when the usable pool shrinks.

Corroborating evidence:

• pg_stat_statements on the backend and a direct DISCARD ALL timing test show whether dirty-session cleanup is expensive.
• Connection turnover (avg_server_assignment_count on 1.23+; session or transaction rates on older releases) shows how often the per-return cost repeats.
• sv_tested accumulation and rising wait time under turnover point to delayed connection reuse rather than application-query execution.

To size the actual cost, time DISCARD ALL on the backend with representative leftover state. A clean-session measurement is misleading:

# Measure DISCARD ALL cost with realistic leftover session state
psql -c "CREATE TEMP TABLE t1(a int); CREATE TEMP TABLE t2(a int); PREPARE p1 AS SELECT 1; SET application_name = 'x'; \timing on" -c "DISCARD ALL;"

Then multiply by your connection return rate. If returns happen 50 times per second and a dirty reset costs 5 ms, that is 250 ms of backend work per second spread across connections, plus 5 ms of delayed reuse per return. Under pool pressure, that delay compounds into wait time.

Two cautions on measurement. avg_query_time is a rolling average over the stats period, so it smooths short spikes. And SHOW STATS column positions are version-dependent (1.23 added total_server_assignment_count/avg_server_assignment_count; 1.24 added prepared-statement counters). Reference columns by name, or use SHOW STATS_AVERAGES, not fixed field positions.

Tradeoffs: what you gain and what you lose by changing it

DISCARD ALL clears, among other things: prepared statements, cursors, temporary tables, session-level SET variables, advisory locks, LISTEN subscriptions, and sequence currval state. If you replace it with something lighter, or empty it, every item on that list that your applications actually use becomes a cross-client contamination channel.

This is not hypothetical hygiene. In session mode with an empty server_reset_query, one client’s SET search_path or SET statement_timeout survives into the next client’s session on that connection. A search_path change can redirect function and table resolution to a different schema, which is a correctness problem and, between clients of different privilege levels, a security problem. Orphaned advisory locks left on a reused connection cause exactly the kind of mysterious contention that looks like a database bug. See PgBouncer advisory locks in transaction mode for how that failure presents.

There is also a subtler cost in the other direction. Because DISCARD ALL leaves the next client no pre-cached state, the first queries on a freshly reset connection can be slightly slower (cold session-level caches). Disabling the reset does not just risk contamination; it also changes this warm-start behavior. Neither direction is free.

Tuning it deliberately

The safe positions, from most to least conservative:

• Session mode, applications use session features: keep DISCARD ALL. The cleanup is doing its job. If the overhead is measurable, reduce session dirtiness instead: drop temp tables explicitly with ON COMMIT DROP or at end of use, deallocate prepared statements, prefer per-transaction SET LOCAL over session SET where possible. A clean session makes DISCARD ALL nearly free without giving up the safety net.
• Transaction mode, defaults: leave it alone. No reset runs; there is nothing to tune. Do not add server_reset_query_always = 1 unless you are deliberately containing an application that illegally uses session state in transaction mode, and treat that as temporary containment, not a destination.
• Empty or lighter reset: only with proof. Setting server_reset_query empty (or to a narrower statement) is only defensible when you have audited the application and can demonstrate it leaves no session state: no temp tables, no PREPARE, no session SET, no advisory locks, no LISTEN. In session mode this audit must hold for every client that shares the pool. “We think the ORM does not do that” is not an audit; ORMs change behavior across versions.

One more interaction matters on recent versions. Since PgBouncer 1.21, max_prepared_statements enables protocol-level prepared statement tracking in transaction mode, and since 1.24 the default is 200 (it was 0 before). Version 1.22 added support for client-issued DEALLOCATE ALL and DISCARD ALL when tracking is enabled: PgBouncer clears the tracked statements for the sending client and the backend that ran the command. This is distinct from server_reset_query, which is not sent in transaction mode without server_reset_query_always = 1. If you rely on tracked prepared statements, test the interaction on your exact version before changing either setting.

After any change, verify what is actually loaded and watch the before/after numbers:

# Confirm loaded config, then compare query and wait time trends
psql -h 127.0.0.1 -p 6432 -U pgbouncer pgbouncer -Atc "SHOW CONFIG;" | grep -Ei "server_reset_query|pool_mode"
psql -h 127.0.0.1 -p 6432 -U pgbouncer pgbouncer -Atc "SHOW STATS_AVERAGES;"

Do not judge the change on avg_query_time alone. Compare wait time, sv_tested churn, assignment rate, and the direct backend DISCARD ALL measurement before and after; then watch application error logs for “does not exist”, wrong-schema, or timeout errors over the following days.

Signals to watch in production

The diagnostic split that matters most: elevated avg_query_time points first at client-query execution on the backend; rising avg_wait_time with normal query time and turnover-related sv_tested churn points at delayed connection reuse, including reset cost. Confusing the two sends you tuning the wrong layer; see PgBouncer avg_wait_time high for the pool-injected side of that split.

How Netdata helps

• Netdata collects PgBouncer admin console stats continuously, so avg_query_time, avg_xact_time, and avg_wait_time are available as per-second time series rather than occasional manual snapshots. That granularity is what lets you correlate wait time and tested-connection churn with connection turnover separately from query mix.
• Per-database breakdowns expose whether the overhead is concentrated in pools whose clients use temp tables or prepared statements, or spread evenly.
• Comparing PgBouncer-reported query and wait metrics with PostgreSQL-side metrics on the same dashboard distinguishes client-query slowdown from delayed pool reuse, the fastest route to checking the reset path.
• Pool state metrics (sv_used, sv_tested, cl_waiting) alongside latency stats show whether slow returns are starting to hold connections long enough to cause queuing.

Related guides

• PgBouncer advisory locks in transaction mode: orphaned locks and mysterious contention
• PgBouncer avg_wait_time high: the latency the pool itself is injecting
• PgBouncer capacity planning: runway for pools, clients, and PostgreSQL slots
• How PgBouncer actually works in production: a mental model for operators
• PgBouncer LISTEN/NOTIFY not working: why pub/sub needs session pooling
• PgBouncer maxwait high: the oldest client waiter and how close it is to timing out
• PgBouncer monitoring checklist: the signals every connection pooler needs
• PgBouncer monitoring maturity model: from survival to expert
• PgBouncer pool exhaustion: clients queue, wait times climb, and the retry cascade
• PgBouncer pool utilization high: sv_active approaching pool_size before clients queue
• PgBouncer prepared statement does not exist: transaction pooling and lost session state
• PgBouncer query_wait_timeout: clients disconnected after waiting too long for a connection