PgBouncer transaction pooling, and what it breaks

What it is

PostgreSQL uses a process per connection. Every client connection forks a backend process with its own memory (roughly 5 to 10 MB of private memory before any query work), and they all contend on shared structures. A few hundred connections is comfortable; a few thousand degrades badly, and the degradation is not graceful.

PgBouncer sits between the application and PostgreSQL, holding a small pool of real server connections and multiplexing many client connections onto them. It has three pooling modes, and the entire topic is the difference between them:

Mode	A server connection is held	Multiplexing	Breaks
Session	For the client's whole connection	Almost none	Nothing
Transaction	For one transaction	High	Session state
Statement	For one statement	Highest	Multi-statement transactions

Transaction pooling is the one that matters and the only one that delivers a large ratio. A server connection is checked out when a transaction begins and returned when it commits or rolls back, so 2,000 idle-between-transactions clients can share 40 server connections.

What it is confused with: a connection pool in your application (HikariCP, pgx's pool, SQLAlchemy's) is not a substitute. An application pool bounds connections per instance. With 60 instances each holding a 20-connection pool, PostgreSQL sees 1,200 connections regardless of how well each pool is tuned. PgBouncer bounds the total across all instances, which is the number PostgreSQL cares about. You generally want both: a small application pool per instance, and PgBouncer bounding the fleet.

The problem it solves

The concrete cost of too many PostgreSQL connections, in three parts:

Memory. Each backend uses several megabytes of private memory before it does any work, and work_mem allocations are per sort node per backend on top. A thousand connections is gigabytes of overhead for processes that are mostly idle.

Contention. Backends contend on the lock manager, the buffer mapping table, and ProcArray, the structure listing every active backend. Taking a snapshot walks ProcArray, so the cost of starting a transaction grows with the number of connections, including idle ones. This is why throughput can fall as connections rise past the useful point, which is the coherence term of the Universal Scalability Law showing up in a specific system.

Connection establishment cost. A new PostgreSQL connection forks a process, runs authentication, and populates catalog caches: typically 20 to 50 ms. For a serverless or short-lived workload that is paid per request.

The measured shape: throughput on PostgreSQL typically peaks somewhere between 2x and 4x the core count in active connections and declines beyond it. A 16-core machine does its best work with roughly 30 to 60 active connections, not 500.

Mechanics

Configuration

[databases]
prod = host=10.0.1.20 port=5432 dbname=prod

[pgbouncer]
pool_mode = transaction
listen_port = 6432

max_client_conn = 5000          ; how many APP connections PgBouncer accepts
default_pool_size = 40          ; server connections PER (user, database) pair
reserve_pool_size = 10          ; emergency extras
reserve_pool_timeout = 3        ; seconds waiting before reserve opens
max_db_connections = 80         ; hard cap per database
server_idle_timeout = 600
query_wait_timeout = 20         ; fail fast rather than queue forever

; Transaction mode is incompatible with server-side prepared statements
; on PgBouncer < 1.21. From 1.21+, this makes them work:
max_prepared_statements = 200

The ratio is the point: max_client_conn = 5000 with default_pool_size = 40 means 5,000 application connections share 40 PostgreSQL backends. That works because in a typical OLTP application, a connection is inside a transaction for a small fraction of its life.

Sizing default_pool_size is arithmetic, not intuition, and it is Little's Law again:

Target throughput:      3,000 transactions/sec
Mean transaction time:  8 ms
Required concurrency  = 3000 x 0.008 = 24 server connections
Plus headroom for variance                 -> 40

Bigger is not better. A pool of 200 against a 16-core machine puts 200 active backends on 16 cores, which is where contention costs exceed parallelism gains. The pool size should be near the database's optimal concurrency, and PgBouncer's queue absorbs the rest.

What transaction pooling breaks

This is the substance of the topic. In transaction mode, consecutive statements from one client can land on different server connections, so anything PostgreSQL stores per session is unreliable.

1. Session-level SET

SET work_mem = '256MB';           -- applies to whatever server connection was used
SELECT ...;                       -- may run on a DIFFERENT connection: default work_mem

The fix is SET LOCAL inside an explicit transaction, which is scoped to the transaction and therefore to the checkout:

BEGIN;
SET LOCAL work_mem = '256MB';
SELECT ...;
COMMIT;

2. Session-level advisory locks

SELECT pg_advisory_lock(12345);      -- held by a server connection you no longer own
-- ... other clients' transactions now run on that connection, holding your lock
SELECT pg_advisory_unlock(12345);    -- probably a DIFFERENT connection: fails

This is the dangerous one, because it does not error, it leaks a lock on a shared connection. Use pg_advisory_xact_lock, which is released automatically at transaction end:

BEGIN;
SELECT pg_advisory_xact_lock(12345);   -- released on COMMIT/ROLLBACK, guaranteed
-- work
COMMIT;

3. LISTEN / NOTIFY

LISTEN registers interest on a session. In transaction mode the session is not yours after the transaction ends, so notifications go to whoever holds that connection. LISTEN/NOTIFY cannot work through transaction pooling at all. The options are a separate direct connection (bypassing PgBouncer) for the listener, or a different mechanism entirely (a queue).

4. WITH HOLD cursors and temporary tables

Both are session-scoped. A temp table created in one transaction is invisible to the next, and worse, it may still exist on a server connection some other client is now using, causing confusing name collisions.

5. Prepared statements

This is the one that bites hardest because it is invisible: most drivers use prepared statements automatically. A PREPARE on one connection is not visible from another, so the EXECUTE fails with prepared statement "S_1" does not exist.

PgBouncer 1.21 (released 2023) added max_prepared_statements, which tracks prepared statements per client and re-prepares them on whichever server connection is assigned. This changed the standard advice substantially: before 1.21 you disabled server-side prepared statements, and now you can keep them.

If on an older PgBouncer, disable them at the driver:

# JDBC
jdbc:postgresql://pgbouncer:6432/prod?prepareThreshold=0

# Go, pgx
pool_config.ConnConfig.DefaultQueryExecMode = pgx.QueryExecModeSimpleProtocol

# Python, asyncpg
await asyncpg.connect(..., statement_cache_size=0)

The cost of disabling them is real: parse and plan on every execution, typically 5 to 15 percent more CPU on the database and higher latency on complex queries. Upgrading PgBouncer to 1.21+ to get prepared statements back is usually worth more than any other tuning here.

The compatibility summary

Feature	Session mode	Transaction mode
`SET` (session)	Works	Broken: use `SET LOCAL` in a transaction
`SET LOCAL`	Works	Works
Prepared statements	Works	Works on 1.21+ with `max_prepared_statements`
`pg_advisory_lock`	Works	Broken: use `pg_advisory_xact_lock`
`LISTEN` / `NOTIFY`	Works	Broken: needs a direct connection
Temp tables	Works	Broken
`WITH HOLD` cursors	Works	Broken
Cursors within a transaction	Works	Works
Multi-statement transactions	Works	Works

Monitoring

-- Connect to PgBouncer's admin database on the same port.
SHOW POOLS;

 database | user | cl_active | cl_waiting | sv_active | sv_idle | maxwait
----------+------+-----------+------------+-----------+---------+---------
 prod     | app  |        38 |        412 |        40 |       0 |      8.2

cl_waiting and maxwait are the metrics that matter. cl_waiting = 412 with maxwait = 8.2 means 412 client connections are queued and the oldest has waited 8.2 seconds. sv_idle = 0 confirms the pool is saturated.

The important diagnostic distinction: a saturated pool is usually a symptom, not the problem. Transactions are being held too long (a slow query, an application holding a transaction open across a network call, a missing index), and the pool queue is where it becomes visible. Raising default_pool_size in response typically makes it worse, by putting more concurrent load on a database that is already the constraint.

A worked example: 1,400 connections, and a transaction held across an HTTP call

An e-commerce backend. 70 application pods, HikariCP with a pool of 20 each, connecting directly to PostgreSQL on a 16-core, 128 GB instance.

Symptoms:

PostgreSQL connections:        1,398 of max_connections 1,500
p99 API latency:               2,900ms
database CPU:                  34%              <- not CPU-bound
p99 query time (pg_stat_statements): 4ms         <- queries are FAST
connection errors:             ~40/min "remaining connection slots are reserved"

The tell is CPU at 34 percent with 1,400 connections and 4 ms queries. The database was not working hard; it was managing connections. The team's instinct was to raise max_connections, which they had already done twice.

Step 1: PgBouncer in transaction mode.

pool_mode = transaction
max_client_conn = 4000
default_pool_size = 40           ; from 3000 tps x 8ms = 24, plus headroom

with HikariCP repointed at PgBouncer and shrunk (the app pool now only needs to bound per-pod concurrency, not the fleet):

maximumPoolSize: 20 -> 10        # 70 pods x 10 = 700 client conns, well under 4000

It broke immediately. The JDBC driver used server-side prepared statements by default, and PgBouncer was 1.18:

org.postgresql.util.PSQLException: ERROR: prepared statement "S_3" does not exist

Two options. They took the fast one first (prepareThreshold=0), measured a 12 percent CPU increase on the database from re-planning, then upgraded PgBouncer to 1.21 and turned prepared statements back on:

max_prepared_statements = 200

Step 2: the second breakage, which was silent. An admin endpoint used pg_advisory_lock to serialise a nightly job. In transaction mode the unlock ran on a different server connection and failed silently in a swallowed exception, so the lock stayed held on a pooled connection. Two nights later the job ran twice concurrently. Changed to pg_advisory_xact_lock inside an explicit transaction.

Step 3: the pool saturated anyway.

 database | cl_active | cl_waiting | sv_active | sv_idle | maxwait
----------+-----------+------------+-----------+---------+---------
 prod     |        40 |        380 |        40 |       0 |     11.4

Forty server connections all busy and 380 clients queued. The instinct was to raise default_pool_size, and instead they asked what was holding the connections, since pg_stat_statements said queries averaged 4 ms.

SELECT pid, state, now() - xact_start AS txn_age, query
  FROM pg_stat_activity
 WHERE state <> 'idle' AND xact_start IS NOT NULL
 ORDER BY xact_start LIMIT 10;

 pid  |        state        | txn_age  | query
------+---------------------+----------+------------------
 4412 | idle in transaction | 00:00:02 | SELECT ... orders
 4418 | idle in transaction | 00:00:02 | SELECT ... orders
 ...

idle in transaction, consistently around 2 seconds. The checkout endpoint did this:

@Transactional                                    // transaction OPENS here
public Order checkout(CheckoutRequest req) {
    Order order = orderRepository.save(...);      // 3ms
    PaymentResult p = paymentGateway.charge(...); // 1,900ms  <- HTTP call, IN the txn
    order.setStatus(p.isOk() ? PAID : FAILED);
    return orderRepository.save(order);           // 2ms
}                                                 // transaction commits here

A 1.9-second external HTTP call inside a database transaction. Five milliseconds of database work held a connection for nearly two seconds. At 40 connections that caps checkout throughput at about 21 per second regardless of how fast the database is, which is Little's Law giving the ceiling exactly.

Directly against PostgreSQL with 1,400 connections this had been hidden, because there were enough connections to absorb it. PgBouncer did not cause this problem; it made an existing one visible by bounding the resource it was wasting.

The fix:

public Order checkout(CheckoutRequest req) {
    Order order = txTemplate.execute(s -> orderRepository.save(...));   // txn 1: 3ms
    PaymentResult p = paymentGateway.charge(...);                        // NO txn open
    return txTemplate.execute(s -> {                                     // txn 2: 2ms
        order.setStatus(p.isOk() ? PAID : FAILED);
        return orderRepository.save(order);
    });
}

Two short transactions with the network call between them. Connection hold time went from 1,900 ms to about 5 ms.

Measured:

                              before      after PgBouncer   after txn fix
PostgreSQL connections        1,398       40                40
p99 API latency               2,900ms     3,400ms (worse!)  180ms
checkout throughput           ~20/s       ~21/s             ~2,400/s
database CPU                  34%         31%               58%
database memory (backends)    11.2 GB     0.4 GB
cl_waiting (PgBouncer)        n/a         380               0-3
connection errors             ~40/min     0                 0

PgBouncer alone made p99 latency worse, from 2,900 ms to 3,400 ms, because the queueing that had been spread across 1,400 connections was now concentrated in a visible queue. That is worth stating plainly: connection pooling does not create capacity, it allocates it, and if the underlying problem is that transactions are held too long, a pool surfaces it as a queue rather than fixing it.

The 120x checkout throughput improvement came from the transaction scope change, which was only diagnosable because the pool made the hold time visible. Database CPU went up, from 31 to 58 percent, which is the correct direction: the database was finally doing work rather than managing connections.

Production evidence

PgBouncer is the standard PostgreSQL pooler and transaction mode is what nearly everyone runs it in. Its documentation contains an explicit table of which features are incompatible with each pooling mode, which is the reference for the compatibility list above.

PgBouncer 1.21 added prepared-statement support in transaction mode (max_prepared_statements), tracking them per client and re-preparing on the assigned server connection. This was a long-requested feature and it removed the most common reason teams could not use transaction pooling.

Amazon RDS Proxy, Google Cloud SQL's built-in pooler, and Supabase's Supavisor all implement transaction pooling with the same constraints, and all document the same incompatibilities. That every managed provider built one is evidence that PostgreSQL's connection model requires external pooling at scale.

Odyssey (Yandex) is the main alternative, multi-threaded where PgBouncer is single-threaded per process, which matters above roughly 20,000 connections where PgBouncer itself becomes CPU-bound on one core. The standard PgBouncer answer to that is so_reuseport with multiple processes.

HikariCP's documentation on pool sizing makes the same argument this page makes: smaller pools outperform larger ones, and their recommended formula (connections = ((core_count * 2) + effective_spindle_count)) produces numbers far smaller than most teams' intuition. Their write-up cites the same throughput-peaks-then- declines curve.

The debate

Session or transaction mode? Transaction, unless something in the compatibility table forces otherwise. Session mode gives almost no multiplexing (a connection is held for the client's whole life), so it solves the connection-establishment cost and not the connection-count problem, which is the one that matters. My position: use transaction mode, fix the incompatibilities in the application, and run a small separate session-mode pool on a different port for the few things that genuinely need session state. That split is common and works well.

Is PgBouncer necessary if the application pools? Yes, at scale, because application pools bound connections per instance and PostgreSQL cares about the total. Sixty instances with a 20-connection pool is 1,200 connections whatever each pool does. PgBouncer is the only place that number can be bounded. The corollary is that when you add PgBouncer you should shrink the application pool, since its job changes from "limit database load" to "limit per-instance concurrency."

How big should the pool be? Little's Law: target_tps x mean_transaction_seconds, plus headroom, and then check it against the database's capacity (roughly 2 to 4 times core count in active connections). The mistake is treating a saturated pool as under-sized. A queue in PgBouncer usually means transactions are held too long, and enlarging the pool moves the queue into the database where it is worse. Check idle in transaction durations before raising default_pool_size.

Is PgBouncer a single point of failure? It is on the path for every query, so yes. Run at least two behind a virtual IP or as a sidecar per application pod. The sidecar pattern is worth considering carefully: it removes the network hop and the shared failure domain, and it gives up the fleet-wide bound, which was the reason for introducing it. My default is a small centralised HA pair or a per-node deployment, not per-pod.

Should you use statement mode? Almost never. It returns the connection after every statement, so multi-statement transactions are impossible, which means no BEGIN at all. The narrow use is a workload that is entirely single-statement autocommit reads, and transaction mode handles that case just as well with fewer surprises.

Follow-up Q&A

"Why does PostgreSQL need external pooling at all?"

Process per connection. Each backend is an OS process with several megabytes of private memory, and they contend on shared structures including ProcArray, which is walked when taking a snapshot, so the cost of starting a transaction grows with the number of connections including idle ones. Throughput peaks around 2 to 4 times core count in active connections and declines beyond it. That decline is the coherence term of the Universal Scalability Law in a specific system, and no amount of max_connections fixes it.

"What breaks in transaction mode?"

Anything session-scoped, because consecutive statements can land on different server connections. Session-level SET (use SET LOCAL inside a transaction), session advisory locks (use pg_advisory_xact_lock), LISTEN/NOTIFY (needs a direct connection), temp tables, and WITH HOLD cursors. Prepared statements used to be on that list and are supported from PgBouncer 1.21 via max_prepared_statements. The advisory lock one is the most dangerous because it fails silently and leaks a lock onto a shared connection.

"cl_waiting is 400 and maxwait is 10 seconds. Do you raise the pool size?"

Not first. A saturated pool usually means transactions are being held too long, so I would check pg_stat_activity for idle in transaction and for long transaction ages. The classic cause is an external call inside a transaction: a payment gateway, an HTTP request, an S3 upload, holding a connection for seconds while doing 5 ms of database work. Raising the pool moves the queue into the database, where 200 concurrent backends on 16 cores is worse than a queue in PgBouncer. Fix the hold time, then re-check.

"How do you size the pool?"

target_tps x mean_transaction_seconds gives required concurrency: 3,000 tps at 8 ms is 24, so 40 with headroom. Then sanity-check against the database: active connections should be roughly 2 to 4 times core count, so a 16-core machine wants something like 30 to 60, and a pool of 200 would be past the point where contention costs exceed parallelism.

"Your application already pools. Why add another layer?"

Because the application pool bounds connections per instance and PostgreSQL cares about the fleet total. Seventy pods with a pool of 20 is 1,400 connections no matter how each pool is configured, and the only place to bound that is in front of the database. When you add PgBouncer you should also shrink the application pool, because its purpose changes from limiting database load to limiting per-instance concurrency.

"You add PgBouncer and latency gets worse. What happened?"

You bounded a resource that was previously over-allocated, so contention that was spread thin across 1,400 connections is now a visible queue. That is diagnostic rather than a regression: pooling allocates capacity, it does not create it. The queue is telling you that transaction hold time times throughput exceeds the pool, and the fix is almost always to shorten hold time rather than to enlarge the pool.

Common misconceptions

"PgBouncer makes the database faster." It reduces connection overhead and bounds concurrency to a range where the database performs well. It adds a network hop and it creates a queue that makes existing problems visible. Throughput improves because the database stops managing connections, not because queries get faster.

"An application connection pool is enough." It bounds per instance. The fleet total is what PostgreSQL sees, and only a shared pooler can bound that.

"A bigger pool handles more load." Beyond the database's optimal concurrency, more active connections reduce throughput. The pool should be near that optimum, with PgBouncer's queue absorbing excess.

"Transaction mode is fine, we do not use anything exotic." Prepared statements are used automatically by most drivers, and they were incompatible before PgBouncer 1.21. Advisory locks and SET appear in ordinary code. "We do not use anything exotic" is usually untrue.

"SET LOCAL and SET are interchangeable." SET is session-scoped and unreliable under transaction pooling; SET LOCAL is transaction-scoped and safe. Using SET outside an explicit transaction under transaction pooling applies the setting to a connection you are about to hand back to someone else.

Interview delivery note

Say this verbatim: "Transaction pooling breaks anything session-scoped, because consecutive statements can land on different server connections: session SET, session advisory locks, LISTEN/NOTIFY, temp tables, and prepared statements before PgBouncer 1.21. The advisory lock is the dangerous one, because it does not error, it leaks a lock onto a connection someone else is now using." Naming a failure that is silent rather than loud is what shows you have operated it.

The senior-versus-staff separator is treating a saturated pool as a symptom. A senior engineer sees cl_waiting climbing and raises default_pool_size. A staff engineer checks idle in transaction first, finds a payment call inside a @Transactional method holding a connection for 1.9 seconds to do 5 ms of database work, and recognises that the pool did not cause the problem, it made an existing one visible by bounding the resource being wasted.

The second signal is expecting latency to get worse when you introduce pooling. Saying "p99 may regress initially because contention that was spread across 1,400 connections becomes a visible queue, and that queue is the diagnosis" shows you understand that pooling allocates capacity rather than creating it.

The Staff & Team Lead Interview Reference