PgBouncer transaction pooling explained

PgBouncer offers three modes, which differ only in when the Postgres server connection returns to the common pool. The official documentation names them session, transaction and statement (pgbouncer.org/features.html, “Pooling modes” section, accessed August 24, 2026).

Session mode is the most permissive but the least effective in scalability: a Postgres connection remains reserved for a client as long as it remains connected, even if it does nothing between two requests. Statement mode is reserved for very specific cases (read-only proxy, health-checks) and even breaks classic explicit transactions. Transaction mode is the compromise that dominates in practice for a REST API: each HTTP request generally corresponds to a single short Postgres transaction.

In transaction mode, PgBouncer only attaches a server connection to a client when the latter opens a transaction, and returns it to the pool upon COMMIT or ROLLBACK. Between two transactions, the same client may find itself reassigned to a completely different server connection.

Concretely, with a default_pool_size of 20, PgBouncer can absorb several hundred simultaneous clients who, at any given time, only have a few transactions actually in progress. It is this ratio which justifies transaction mode for a REST API with high traffic but short transactions: the rare resource (a Postgres connection, expensive in memory on the server side) is only occupied for the time strictly necessary.

[pgbouncer]
listen_port = 5432

; La connexion serveur est libérée dès la fin de chaque transaction
pool_mode = transaction

default_pool_size = 80
max_client_conn = 1000

; Ne pas utiliser avec des sessions stateful (SET, advisory locks, LISTEN/NOTIFY)

This last comment sums up the gist: transaction mode works because it deliberately breaks the link between "my application session" and "my Postgres connection". Everything based on this link breaks. The next section lists precisely what.

The official PgBouncer documentation explicitly lists PostgreSQL features which lose their meaning as soon as a server connection can be recycled between two requests from the same client.

Most modern Postgres drivers prepare named requests on the protocol side by default, without the application code explicitly requesting it. This is precisely what makes this trap difficult to anticipate.

A protocol prepared statement is named and cached on a specific server connection, at the time of Parse. In transaction mode, this connection can be reassigned to another client between two requests from the same logical client. If the driver then replays the same statement name on a connection where it was never prepared, Postgres responds with an explicit error, typically prepared statement "sqlx_s_N" does not exist for an sqlx client. The behavior is intermittent: it depends on how the connections perform under load, not on a deterministic bug reproducible on each call.

The client-side correction is the same regardless of the language: disable the cache of named prepared statements, or force unnamed queries, for any pool that crosses a pooler in transaction mode. In Rust with sqlx, it goes through statement_cache_capacity(0) on the connection options.

let connect_options = url
    .parse::<PgConnectOptions>()?
    .statement_cache_capacity(0);

// Equivalents: asyncpg -> statement_cache_size=0, pgjdbc -> prepareThreshold=0

Since version 1.21, PgBouncer alleviates part of the problem on the server side: it can follow protocol prepared statements in transaction mode and prepare them on the fly on the assigned connection, with an LRU cache per connection whose size is adjusted via max_prepared_statements. This reduces the number of misses, but it does not exempt you from disabling the client cache in a multi-tenant pool where the search_path changes from one request to another: a cached plan freezes the internal identifier (OID) of the resolved table at the time of Parse, and replaying it under another schema may return data from the wrong tenant rather than a simple error.

The Aurabase repository deploys PgBouncer as pool_mode=transaction in front of the shared data plane (deploy/helm/aurabase/templates/infra/pgbouncer.yaml), and an identically configured Pooler CNPG in front of each dedicated Postgres instance of a tenant (deploy/cnpg/tenant-pooler.yaml). Both paths apply the same discipline described above.

The source code documents a specific security reason for this choice, not just a stability reason. Postgres pools shared between tenants position a different search_path per project on reused connections. A cached prepared statement freezes the OID of the resolved table at the time of Parse; replaying it for another tenant on the same connection would run the query against the first tenant's schema, an isolation bypass, not just an application error. statement_cache_capacity(0) is therefore applied without exception, including on dedicated instances which also pass through a CNPG Pooler in transaction mode.

Second session hygiene measure: when each connection returns to the pool, a hook executes DISCARD ALL (resetting settings, deallocating prepared statements on the server side, releasing advisory locks, purging cursors and temporary tables). Without this hook, a session residue posed by a previous request could leak on the next request from a different tenant reusing the same recycled connection.

Exception accepted: dedicated PostgREST instances remain in direct connection to the primary, without going through the pooler. PostgREST schema reloading relies on LISTEN/NOTIFY, which assumes a persistent connection, exactly the functionality that transaction mode breaks (detail already documented in our article on PostgREST compatibility at Aurabase). RLS settings per request are passed to SET LOCAL within an explicit transaction, the only way to remain compatible with a pool that can change server connections at any COMMIT (see our article onmulti-tenant RLS isolation).

A short checklist, applicable to any backend moving from a direct Postgres connection to a PgBouncer in transaction mode.

1. Audit the application code. Look for non-transaction SET, LISTEN/NOTIFY, session advisory locks, WITH HOLD cursors and temporary tables reused between queries.
2. Replace session SETs with LOCAL SETs within an explicit transaction. This is the only setting that properly survives connection recycling, because it is cleaned up at COMMIT/ROLLBACK rather than leaking on the next connection.
3. Disable the driver-side prepared statements cache if your pool traverses the pooler and the schema or role changes from one request to another. The cost in performance is real but measurable, and much lower than the risk of leakage between tenants.
4. Isolate connections that really need session mode (migrations, admin scripts, anything that depends on LISTEN/NOTIFY) to a direct non-pooler connection, rather than forgoing transaction mode for all other traffic.
5. Size default_pool_size and max_client_conn relative to Postgres' actual max_connections, not by an arbitrary figure copied from another project.
6. Test under real load, not just smoke test. Prepared statement errors and session settings leaks almost never appear on a single local connection.
7. Monitor SHOW POOLS and SHOW STATS from the PgBouncer administration console once in production, to spot pool saturation before it becomes visible on the client side.

No, but it is the correct default choice for the vast majority of REST APIs. Session mode remains preferable for a legacy application heavily dependent on session functionalities that you cannot refactor quickly, or for low traffic where the pooling gain does not compensate for the migration effort.

PgBouncer is not the only implementation of this pooling model either: Supavisor (Supabase) and PgCat are two recent alternatives, with different trade-offs on load distribution and clustering. See our detailed comparison, PgBouncer vs Supavisor vs PgCat, to choose between the three depending on your topology.

The questions that come up most often once transaction mode is activated in production.