Three Thousand Connections, Forty Slots

Our API had 300 pods, each opening 10 connections to Postgres: 3,000 clients against a server configured with max_connections = 500. The database was not slow. It was queuing, and the queue was in front of the database rather than inside it.

p99 was 6.1 seconds. CPU on the database was 38%. The connection count was the bottleneck, and no amount of indexing was going to fix it.

Pool size is concurrency, not clients

The useful number is not how many clients you have. It is how many queries are in flight at once:

concurrent queries = QPS × query time
                  = 400 × 0.008s
                  = 3.2

Little's law again. A pool of 20 server connections serves 400 QPS at 8ms with enormous headroom. The other 2,980 client connections are mostly idle; they hold file descriptors and backend memory for no reason.

The formula people actually need:

pool_size ≈ peak_qps × p99_query_time × safety_factor
          = 400 × 0.02 × 1.5
          = 12

Round up, add a reserve for admin queries, and stop. A pool of 40 was already generous for our workload.

What session pooling actually does

PgBouncer's default mode is session pooling: a client gets a server connection for the entire session, which is what a naive connection pool does — it just moves the queue one hop closer to the client.

Mode	Server connection held for	What breaks
session	the whole client session	nothing, but no multiplexing
transaction	one transaction	session state; named prepared statements on older versions
statement	one statement	multi-statement transactions

We switched to transaction mode and the server connection count fell from 500 to 40. p99 went from 6.1s to 92ms. Database CPU dropped because 460 backends stopped competing for memory and locks.

The prepared statement trap

Transaction mode changes what session state means, and the first thing that breaks is prepared statements. Before PgBouncer 1.21, a named prepared statement created on one server connection was invisible on another, and the driver failed with "prepared statement does not exist" at random.

Two fixes:

• Use protocol-level unnamed statements, prepared per execution.
• On 1.21+, set max_prepared_statements = 200 and let PgBouncer track them.

[pgbouncer]
pool_mode = transaction
max_client_conn = 3000
default_pool_size = 40
reserve_pool_size = 8
reserve_pool_timeout = 2
max_prepared_statements = 200
server_idle_timeout = 60

The other session state landmines

In transaction mode every statement may land on a different backend. Anything that assumes continuity breaks:

• SET statements (including SET search_path) leak or vanish.
• Advisory locks taken in one transaction and released in another.
• Temporary tables.
• LISTEN/NOTIFY.
• Server-side cursors held across transactions.

The rule: if it requires the same backend twice, it belongs in a single transaction, or in a separate session-mode pool.

What to watch

Three PgBouncer metrics tell you almost everything:

Metric	Meaning	Alert
cl_waiting	clients waiting for a server connection	> 0 for 30s
sv_active	server connections executing	> 80% of pool for 5m
avg_wait_time	time spent in the queue	p99 > 50ms

We alert on cl_waiting because it is the leading indicator; by the time p99 moves, the queue has been growing for minutes.

When session mode is the right answer

Not every workload can use transaction pooling. If the application relies on session state — long-lived temp tables, advisory locks, or a driver that cannot do protocol-level prepares — session pooling is honest. Size the pool to the number of active sessions you actually need, and cap clients in front of it.

The point is not that one mode is better. The point is that "3,000 connections" was never a requirement; it was an accident of default configuration.

---

The database had 500 slots and we were using them as a parking lot. Transaction pooling turned them back into workers.