Connection Pooling Tuning Lab (Interactive)
Multiplex thousands of client connections over a small DB pool and find the throughput peak with the HikariCP formula. Adjust cores, spindles, clients, and pooling mode to see RAM burn, context-switch thrashing, and the (cores x 2) + spindles optimum.
PgBouncer Connection Pool Tuner
Fewer connections = more throughput. Multiplex thousands of app clients over a small DB pool.
Transaction: backend borrowed per BEGIN..COMMIT, then returned — 10,000 clients over ~50 sockets.
DB RAM for conns
0.2 GB
33 x 7.5MB backends
Aggregate QPS
10,927
CPU-capped at 12,000
p99 Latency
274.5 ms
queue depth 2,967
vs un-pooled
1.7x
throughput multiplier
Try this: set mode to Session with 3,000 clients — PostgreSQL forks 3,000 worker processes (~22 GB RAM) and throughput collapses. Switch to Transaction pooling with pool = 33: the same clients multiplex over 33 sockets and QPS rises. Statement pooling would multiplex harder still but abandons BEGIN...COMMIT support.
How It Works Under the Hood
PostgreSQL forks a memory-hungry backend process per connection, so thousands of direct clients exhaust RAM and trap the CPU in context switching rather than query execution. Poolers like PgBouncer cap active backends: session pooling keeps one backend per client, transaction pooling borrows one only for a BEGIN...COMMIT block, and statement pooling is stricter still. The counterintuitive law of pool sizing is that fewer connections yield higher throughput, quantified by the cores-times-two-plus-spindles formula.
Core Architectural Principles
- Each DB backend costs about 7.5MB RAM plus lock and buffer bookkeeping before doing work.
- Throughput peaks near (CPU cores x 2) + effective spindle count and decays with oversubscription.
- Transaction pooling multiplexes ten thousand clients over tens of sockets but resets session state.
Quote the sizing formula when serverless or microservice fleets meet a relational database: "Lambda cannot hold sockets, so I front PostgreSQL with PgBouncer in transaction mode sized around cores times two." Explain the thrashing mechanism, context switches and cache pollution, to prove the fewer-connections-is-faster result rather than asserting it.
Huge multiplexing gains and RAM savings versus lost session features like prepared statements and advisory locks.