How to Size Database Connection Pools: Insights from HikariCP Performance Tests

I discovered a GitHub Wiki article about HikariCP that cleared many of my doubts, so I share a translated version here.

Configuring database connection pools is a common source of pitfalls; several counter‑intuitive principles must be understood.

10,000 Concurrent Users

Imagine a website handling roughly 10 k concurrent users (about 20 k TPS). The right question is not "how large should the pool be?" but "how small should it be?"

The Oracle Real World Performance Group video (9600 concurrent threads, 550 ms sleep between DB accesses) starts with a middleware thread‑pool size of 2048:

Initial configuration results show each request waiting 33 ms in the pool queue and 77 ms to execute SQL.

When the pool size is reduced to 1024 (same load), throughput remains similar but wait time halves.

Further reducing the pool to 96 (still 9600 threads) yields an average queue wait of 1 ms and SQL execution time of 2 ms, eliminating most wait events and increasing throughput. Response time drops from ~100 ms to ~3 ms without any other changes.

But Why?

Even on a single‑core CPU, the OS can interleave hundreds of threads via time‑slicing, but true parallelism is limited to the number of cores. Adding threads beyond the core count leads to context‑switch overhead and slower performance.

Limited Resources

Performance bottlenecks fall into three categories: CPU, disk, and network. Memory bandwidth is usually much higher, so it is omitted.

On HDDs, seek time and rotational latency cause I/O blocking; threads waiting on I/O free the CPU to serve other work, so having more threads than cores can be beneficial when I/O is the bottleneck.

SSD storage eliminates seek latency, so fewer threads (closer to core count) often yield better performance. Network I/O behaves similarly, with higher bandwidth reducing blocking.

PostgreSQL benchmark data shows TPS growth slowing after about 50 connections; the Oracle video reduced connections from 2048 to 96, which is still high unless the server has many cores.

Formula

PostgreSQL suggests a generic formula for connection pool size:

Connections = (CPU cores × 2) + effective disk count

CPU cores should exclude hyper‑threads. If all active data is cached, effective disk count is 0; otherwise it approaches the actual number of disks. The formula’s behavior on SSDs is not yet analyzed.

For a 4‑core i7 server, the calculation yields (4 × 2) + 1 = 9, rounded to 10 connections. Tests show this can comfortably handle 3000 users and 6000 TPS for simple queries; exceeding 10 connections starts to increase latency and reduce TPS.

Note: The same principle applies to many I/O‑bound programs; the optimal thread count often equals the number of CPU cores.

Axiom: Use a Small Pool with a Queue of Waiting Threads

For 10 k concurrent users, a 10 k‑size pool is absurd; even 1 k is excessive. A pool of around 10 connections, with the remaining business threads queued, is optimal. The pool size should match the number of queries the database can execute concurrently, typically not exceeding 2 × CPU cores.

Many small‑scale web apps use a 100‑connection pool for only a dozen concurrent users, unnecessarily burdening the database.

Please Note

The ideal pool size depends on system characteristics. Mixed workloads of long‑ and short‑lived transactions often require separate pools. For task‑queue systems, the number of concurrent tasks should dictate the pool size, not the other way around.

Additional promotional links and QR codes follow, which are not part of the technical content.