Guidelines for Sizing Database Connection Pools: Insights from HikariCP and Oracle Performance Tests

Main Content

When configuring a database connection pool, developers often fall into traps; several counter‑intuitive principles must be clarified.

10,000 Concurrent Users

Imagine a website handling roughly 10,000 concurrent requests (about 20,000 TPS). The correct question is not "How large should the pool be?" but rather "How small should the pool be?"

The following Oracle Real World Performance Group video demonstrates a stress test with 9,600 concurrent threads, each sleeping 550 ms between DB calls, initially using a middleware pool of 2,048 connections.

With 2,048 connections the average wait time in the pool queue was 33 ms and the SQL execution time 77 ms. The database showed heavy buffer busy‑waits and CPU usage near 95 %.

Reducing the pool to 1,024 connections kept throughput roughly the same but cut the wait events in half.

Further shrinking the pool to 96 connections (still with 9,600 threads) reduced the average queue wait to 1 ms and SQL time to 2 ms, virtually eliminating wait events and increasing throughput.

Only by shrinking the middleware connection pool did the request latency drop from around 100 ms to 3 ms.

But why?

Even a single‑core CPU can "run" hundreds of threads via time‑slice scheduling, but true parallel execution only occurs up to the number of cores. Adding threads beyond the core count introduces context‑switch overhead, slowing the system.

Limited Resources

Performance bottlenecks fall into three categories: CPU, disk, and network. Disk I/O, especially on spinning disks, incurs seek and rotational latency; even with caching, these delays exist. While a thread waits for I/O, the CPU can serve other threads, so having more threads than cores can be beneficial when I/O blocking is significant.

However, modern SSDs eliminate seek and rotational delays, resulting in far fewer I/O blocks; thus fewer threads (closer to core count) often yield better performance. The same reasoning applies to network I/O.

PostgreSQL benchmark data shows TPS growth slowing after about 50 connections; the Oracle test reduced connections from 2,048 to 96, which is still too high unless the server has 16‑32 cores.

Calculation Formula

The following formula, provided by PostgreSQL, can be used for most databases:

Connections = (CPU cores * 2) + Effective disks

Do not count hyper‑threaded cores; if all active data is cached, effective disks are 0. The formula’s behavior on SSDs is not yet fully analyzed.

For a 4‑core i7 server, the recommended pool size is ((4 * 2) + 1) = 9, rounded to 10. Testing shows that exceeding this size quickly degrades response time and TPS.

Note: This formula also applies to many I/O‑bound programs; the author observed the optimal Netty thread count matching the CPU core count.

Axiom

You need a small connection pool and a queue of threads waiting for connections. Setting a pool size equal to the number of concurrent users (e.g., 10,000) is absurd; a pool of 10–20 connections is often sufficient, matching the number of queries the DB can handle concurrently (usually not exceeding 2 * CPU cores).

Please Note

The optimal pool size depends on system characteristics. Mixed workloads with long and short transactions may require separate pools for each. For task‑queue systems, the number of concurrent tasks should drive the pool size, not the other way around.

END