Idle CPU, terrible p99
A multi-tenant platform serving 380 tenants off one Postgres 16 instance reported p99 API latency of 900ms against a p50 of 24ms. Database CPU averaged 30%, buffer cache hit ratio was 99.4%, and no single query in pg_stat_statements had a mean above 8ms. Every instinct that says 'find the slow query' was going to waste the afternoon.
The time was spent waiting for a connection. PgBouncer was in session mode with default_pool_size 20 against an application running 14 pods with a 30-connection local pool each. The application believed it had 420 connections. It had 20, and the rest were queued in the pooler with no visibility in any application-side metric.
The tell was that p99 tracked request rate almost linearly while p50 was flat. That shape is queueing, not work.
Pool sizing is arithmetic, not a tuning knob
Little's law settles this. Concurrency required equals arrival rate multiplied by service time. At 1,800 requests per second with a mean database service time of 6ms, you need about 11 busy server connections to keep up. Provision 20 and you have headroom; provision 20 while the application opens transactions that stay open for 40ms of application-side work and you need 72.
The number that matters is not query duration, it is transaction hold time — from BEGIN to COMMIT, including anything the application does in between. We instrumented it and found a code path that fetched an order, called a pricing service over HTTP, then updated within the same transaction. Mean hold time on that path was 210ms. Moving the HTTP call outside the transaction dropped required concurrency by a factor of 30.
Oversizing has a cost too. Past roughly 2–4 connections per core, throughput on Postgres flattens and then declines as lock contention and context switching rise. On a 16-core instance we settled at 60 server connections total, with client-side pools deliberately larger so queueing is visible and bounded at the pooler.
What transaction mode takes away
Switching to transaction pooling raised effective concurrency by 6x on the same 60 server connections. It also broke four things in staging, each in a way that only appears under load: session-level SET statements leaking between tenants, advisory locks released to the wrong session, LISTEN/NOTIFY silently receiving nothing, and WITH HOLD cursors failing.
Server-side prepared statements are the subtle one. PgBouncer 1.21 and later track them per client with max_prepared_statements, but if that is left at 0 every driver using the extended protocol re-prepares constantly, and you pay parse and plan cost on every call. We set it to 200 and saw parse time drop out of the top ten wait events.
The tenant isolation risk deserves a paragraph of its own. If you set a session GUC for row-level security and the pooler hands that connection to another tenant mid-session, you have a data leak, not a bug. Use SET LOCAL inside the transaction, and add a test that asserts the GUC is empty at the start of every checkout.
Fairness between tenants
One tenant running a report can consume the whole pool. We now run three named pools against the same database — interactive, batch, and reporting — with separate PgBouncer databases and separate credentials. Interactive gets 40 server connections, batch 12, reporting 8. A runaway report queues behind its own kind.
Measurement comes from sampling pg_stat_activity every second into a table, bucketed by wait_event_type, plus PgBouncer's SHOW POOLS for cl_waiting and maxwait. cl_waiting above zero for more than 5 seconds is the alert. It fires before users notice, which the p99 alert never did.
After the split and the transaction-hold-time fix, p99 went from 900ms to 61ms at 20% higher traffic, on the same instance size. We did not buy any hardware.