A p99 that climbs while CPU sits at 35% is rarely a capacity problem. More often it is a queue: requests waiting for one of a fixed number of backends that are themselves waiting on locks, IO, or a connection slot. The pool sits at the centre of that queue, and the two common ways to build it — an application-side pool inside every service instance, or PgBouncer in front of the database — produce very different numbers on the same hardware. This post is about measuring which one your workload actually needs before you pick.

Why a Postgres connection is not free

Postgres serves every client connection with its own backend process, and it sizes certain resources directly off max_connections, including shared memory (PostgreSQL docs: Connections and Authentication). Two consequences follow:

• Raising max_connections to make a connection storm go away costs memory and adds contention in shared structures. You buy admission, not throughput.
• Each new backend also pays fork, authentication (SCRAM is deliberately expensive), and first-query planning. An application that reconnects per request pays that on every request.

On a 16-vCPU machine, 400 backends do not deliver 25 times the work of 16 backends. Past the point where runnable backends exceed cores, adding connections mostly adds context switches.

The only number that matters: server-side connections

Application pools are sized per process, so the server sees the product. Twelve pods with a 20-connection pool is 240 backends, plus migrations, monitoring, and a few psql sessions. Deploy a second service and you are at 480. This is how teams meet FATAL: sorry, too many clients already during a rolling deploy, when old and new pods overlap.

PgBouncer breaks that multiplication. Clients connect to the pooler, and the pooler holds at most default_pool_size (default 20) server connections per user/database pair. Client count and backend count become independent variables, which is the entire point. PgBouncer needs roughly 2 kB per client connection, so 2,000 waiting clients are cheap; 2,000 Postgres backends are not.

Sizing from the server’s capacity, not the app’s

Start from how many backends can actually be active at once, then divide. A defensible starting point is 2× vCPU for a mostly-OLTP workload, 4× if queries are IO-bound and spend most of their time waiting, and fewer if you run long analytical queries alongside them. If you do not know which non-default settings are already in play, work through our postgresql.conf audit first: pool sizing against a hand-edited config is guesswork.

Then measure what the current connections are doing, because idle and working connections need completely different answers:

-- How many backends, and what are they doing?
SELECT state, count(*)
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY 1 ORDER BY 2 DESC;

-- If active backends are waiting, on what?
SELECT wait_event_type, wait_event, count(*)
FROM pg_stat_activity
WHERE state = 'active'
GROUP BY 1, 2 ORDER BY 3 DESC;

If most backends sit in idle, you are over-provisioned and paying for it in memory and snapshot overhead; a pooler cuts the backend count without touching application code. If most are active and waiting on Lock or IO, a pooler will not help — that is a query or schema problem.

What transaction pooling actually breaks

Transaction pooling is where the savings come from, and it changes session semantics by design. PgBouncer’s own feature map lists what stops working: SET/RESET, LISTEN, SQL-level PREPARE/DEALLOCATE, session-level advisory locks, WITH HOLD cursors, and PRESERVE ROWS temp tables (PgBouncer features). Protocol-level prepared statements do work in transaction mode, but only when max_prepared_statements is non-zero; at 0 the tracking is disabled and drivers that rely on named statements fall back to the slow path. The payoff is real: the docs note that 100 clients preparing the same query through a pool of 20 parse it 20 times, not 100.

[databases]
app = host=10.0.1.10 port=5432 dbname=app pool_size=60

[pgbouncer]
listen_port = 6432
pool_mode = transaction
max_client_conn = 2000
default_pool_size = 20
min_pool_size = 10
reserve_pool_size = 10
reserve_pool_timeout = 3
max_prepared_statements = 200
server_idle_timeout = 60

reserve_pool_size covers bursts without inflating the steady-state pool, and server_idle_timeout (default 600 seconds) controls how long an idle backend is kept. Deploy one pooler per node or per database cluster, not inside every pod: a pooler per replica is an application pool with extra steps and a new failure mode.

Benchmark both arms at the same client count

Do not benchmark the pooler against nothing. Benchmark the topologies you are choosing between, on a restored copy, with identical client concurrency. pgbench is enough:

# Arm 1: application-side pooling only, 200 clients, read-only
pgbench -h db-primary -U bench -c 200 -j 8 -T 300 -P 30 -M prepared -S benchdb

# Arm 2: same 200 clients through PgBouncer, same server
pgbench -h pgbouncer -p 6432 -U bench -c 200 -j 8 -T 300 -P 30 -M prepared -S benchdb

# Arm 3: connection cost per transaction, the worst case for app pools
pgbench -h db-primary -U bench -c 50 -j 8 -T 120 -P 30 -C -S benchdb

# Arm 4: write mix from a script file
pgbench -h pgbouncer -p 6432 -U bench -c 200 -j 8 -T 300 -P 30 -f write-mix.sql benchdb

Then hold client count fixed and vary pool size (20, 40, 60). If throughput is flat across all three, the database was never the constraint and you can size conservatively.

Reading the results honestly

• TPS flat, latency rising as clients grow: queueing. Either the pool is too small for the arrival rate or the queries are too slow — check both before raising anything.
• TPS peaks then falls as backends grow: contention. Fewer connections, same work.
• Averages look fine but users complain: measure p99 with histograms rather than means, as we did in pg_hdr_histogram for real p99 latency.
• Pooler changed nothing: look for idle in transaction. Transaction pooling cannot recycle a backend until the transaction ends, so one forgotten BEGIN parks a connection for everyone behind it.

What to do this week

1. Count backends per application, including sidecars, migrations, and BI tools.
2. Compute the target from cores and IO profile, not from what the application asks for.
3. Put one PgBouncer in front, start at half that target, and set query_wait_timeout so clients fail fast instead of queuing invisibly.
4. Run the four pgbench arms before and after, on the same data, and keep the numbers in the repository next to the config.

If you would rather not run that experiment on a hunch, we do Postgres pool sizing and p99 latency reviews for Houston teams — bring the workload, we will bring the harness.