PgBouncer transaction vs session pooling: what each mode breaks and when to use it

pool_mode is the most consequential setting in pgbouncer.ini. It decides when a server connection is returned to the pool, which in turn decides how many PostgreSQL backends you need and which PostgreSQL features your application is no longer allowed to use.

The failure pattern that brings people to this page is consistent: someone switches from session to transaction for efficiency, deploys, and hours or days later the application starts throwing intermittent “prepared statement does not exist” errors, losing temp tables mid-request, or leaking advisory locks. Every PgBouncer metric looks healthy. The breakage only shows up under concurrency, because single-user testing keeps landing on the same backend.

How the modes differ

The only difference between the three modes is the lifetime of the binding between a client connection and a server connection. Everything else about PgBouncer stays the same.

flowchart TD
  C[Client sends query] --> M{pool_mode}
  M -->|session| S1[Server assigned for entire client session]
  S1 --> S2[Returned when client disconnects]
  S2 --> S3[server_reset_query runs - DISCARD ALL]
  M -->|transaction| T1[Server assigned per transaction]
  T1 --> T2[Returned at COMMIT or ROLLBACK]
  T2 --> T3[No reset query - session state must not exist]
  M -->|statement| ST1[Server assigned per statement]
  ST1 --> ST2[Returned after each statement]
  ST2 --> ST3[Multi-statement transactions rejected]

• Session mode: the server connection is assigned when the client connects and held until the client disconnects. Multiplexing is minimal; PgBouncer mostly acts as a connection funnel and reaper. Every PostgreSQL session feature works, because the client keeps the same backend for its whole life. When the connection is returned, PgBouncer runs server_reset_query (default DISCARD ALL) to scrub session state before reuse.
• Transaction mode: the server connection is assigned when a transaction starts and returned at COMMIT or ROLLBACK. An autocommit statement is a one-statement transaction, so it holds a server connection for exactly that statement. This is the standard production mode and gives real multiplexing: hundreds of clients can share a pool of 20 backends. The cost is that nothing which lives outside a transaction boundary survives.
• Statement mode: the server connection is returned after every statement. Multi-statement transactions are rejected outright. Rarely usable outside narrow read-only workloads, because most applications eventually issue BEGIN.

What transaction mode breaks

Check this table before touching pool_mode. Anything in it that your application uses will fail intermittently under load, not deterministically in testing, because failure depends on whether the next transaction lands on a different backend.

Three of these deserve expansion because they cause the worst real-world damage.

Prepared statements. Before PgBouncer 1.21, any use of prepared statements in transaction mode failed under concurrency. Version 1.21 added max_prepared_statements, which tracks protocol-level prepares (the extended query protocol used by most drivers: PQprepare, JDBC PreparedStatement, and similar) and replays them on whichever backend the next transaction lands on. Since 1.24 this is enabled by default with max_prepared_statements = 200. Two traps remain: SQL-level PREPARE foo AS ... is invisible to PgBouncer and still breaks, and some drivers are incompatible with the tracking (the PgBouncer FAQ calls out PHP/PDO unless PHP 8.4+ with libpq 17 is used). If you upgrade from a version older than 1.24, prepared statement tracking turns on by default. Usually an improvement, but it is a behavior change worth knowing about before the upgrade, not after.

Advisory locks. pg_advisory_lock() is session-scoped. If the application acquires the lock in one transaction and the unlock call lands on a different backend, the lock is orphaned on the first backend and held until that connection is recycled. The symptom is lock contention with no holder visible in the session you are inspecting. Transaction-scoped advisory locks (pg_advisory_xact_lock) are safe, because they release at commit on the same backend that took them.

SET variables. SET search_path or SET statement_timeout issued at connect time works in development, where one client keeps one backend, then fails intermittently in production when the next transaction lands elsewhere. The fix is SET LOCAL inside the transaction, which is scoped to the transaction and therefore survives transaction mode correctly. server_reset_query does not save you here: in transaction mode it is not executed at all, because the mode assumes no session state exists to reset.

What session mode costs

Session mode breaks nothing. That is its entire value proposition. The cost is efficiency: one server connection per connected client, held for the whole session even while the client sits idle between requests. With 500 connected clients you need 500 PostgreSQL backends, and PgBouncer adds little beyond connection funneling and the DISCARD ALL cleanup on return.

One hidden cost: server_reset_query runs on every connection return in session mode. If DISCARD ALL is slow on your backend (many temp tables to clean, for example), it delays connection reuse, but PgBouncer does not charge that reset time to avg_query_time; check reset timing and pool turnover separately.

Use session mode when:

• The application uses LISTEN/NOTIFY, session advisory locks, WITH HOLD cursors, or session-level SET state and you cannot or will not change the code.
• You need a compatibility landing zone during migration, while you audit and fix session-dependent features before switching to transaction mode.
• Client counts are genuinely low and multiplexing buys you nothing.

PgBouncer lets you set pool_mode per database in [databases] and per user in [users], so you can run well-behaved workloads in transaction mode and pin LISTEN/NOTIFY users to session mode.

Statement mode

Statement mode returns the server connection after each statement and rejects multi-statement transactions. Almost no general-purpose application qualifies, because the first BEGIN it issues will fail. It exists for simple, strictly autocommit read workloads where you want maximum connection turnover. If you are not sure whether your workload qualifies, it does not.

Auditing an application before switching to transaction mode

The mistake is not choosing transaction mode. The mistake is switching without an audit. Work through this list against the actual application code and its ORM configuration:

• Prepared statement usage. Search for PREPARE, and check the ORM or driver: Hibernate, Django with persistent connections, and many others prepare statements by default. Confirm the PgBouncer version (1.21+ with max_prepared_statements configured) and confirm driver compatibility.
• Session state. Search for SET statements issued outside transactions, SET SESSION, and connection-initialization hooks in the framework that run SET at connect time.
• Temp tables. Search for CREATE TEMP and ON COMMIT PRESERVE ROWS. Temp tables created and consumed inside one transaction are fine; temp tables that outlive a transaction are not. ON COMMIT DROP tables are safe.
• Advisory locks. Search for pg_advisory_lock. Convert to pg_advisory_xact_lock where the locking semantics allow it.
• LISTEN/NOTIFY. Search for LISTEN. If present, that workload needs session mode or a dedicated direct connection that bypasses PgBouncer.
• Cursors. Search for WITH HOLD. Ordinary cursors scoped to a transaction are fine.

If you find violations you cannot fix immediately, the escape hatch is server_reset_query_always = 1, which forces the reset query to run in transaction mode. The PgBouncer documentation is blunt about this: it is a workaround for running session-feature-using applications over a transaction-mode pooler. Treat it as a bridge, not a destination.

Confirming your current mode and watching for a mismatch

Check what is actually running, per database, not just what the config file says:

# Confirm the effective pool_mode and prepared statement settings
psql -h 127.0.0.1 -p 6432 -U pgbouncer pgbouncer -c "SHOW CONFIG;" | grep -Ei "pool_mode|max_prepared_statements|server_reset_query"

The dangerous property of a pool mode mismatch is that PgBouncer itself looks completely healthy. The pattern to recognize:

• Application logs show “prepared statement X does not exist”, “current transaction is aborted”, “relation does not exist” for temp tables, or stale reads.
• cl_waiting is zero, avg_wait_time is near zero, pool utilization looks normal.
• avg_query_time is low, because the failures are fast SQL errors, not slow queries.
• Errors correlate with concurrency, not with specific requests. Single-user testing passes.

There is no PgBouncer metric that detects session state loss. Detection lives in application error logs. The PgBouncer-side value of monitoring is confirming that the pooler is not the problem, which points the investigation at the application layer faster.

Signals to watch

How Netdata helps

• Netdata collects the SHOW POOLS and SHOW STATS families per database, so cl_waiting, sv_active, sv_idle, avg_wait_time, avg_query_time, and avg_xact_time are available as per-second time series rather than point-in-time snapshots.
• The avg_xact_time versus avg_query_time gap is directly chartable, which surfaces idle-in-transaction connection holding in transaction mode before it exhausts the pool.
• Comparing wait time against query time on the same dashboard answers the “pooler latency or database latency” question in one look, which is the first fork in any PgBouncer investigation.
• Because PgBouncer exposes no error counters, correlating healthy-looking PgBouncer metrics with application-side error spikes is what identifies a pool mode mismatch; having both in one place shortens that correlation from a log-diving exercise to a glance.
• PgBouncer 1.23 added server-assignment counters (total_server_assignment_count / avg_server_assignment_count) and 1.24 exposed prepared-statement counters in SHOW STATS/SHOW STATS_AVERAGES; collect these directly if your monitoring does not parse them.

Related guides

• How PgBouncer actually works in production: a mental model for operators
• PgBouncer pool exhaustion: clients queue, wait times climb, and the retry cascade
• PgBouncer avg_wait_time high: the latency the pool itself is injecting
• PgBouncer maxwait high: the oldest client waiter and how close it is to timing out
• PgBouncer pool utilization high: sv_active approaching pool_size before clients queue
• PgBouncer sv_idle at zero: no headroom and one slow query from a cascade
• PgBouncer pool_size sizing: matching pool capacity to transaction time and throughput
• PgBouncer capacity planning: runway for pools, clients, and PostgreSQL slots
• PgBouncer query_wait_timeout: clients disconnected after waiting too long for a connection
• PgBouncer reserve pool activation: overflow capacity that hides an undersized pool
• PgBouncer monitoring checklist: the signals every connection pooler needs
• PgBouncer monitoring maturity model: from survival to expert