Why Smaller Database Connection Pools Boost Performance: Lessons from HikariCP

Introduction

This article is a translation of a HikariCP wiki post that dispels common misconceptions about database connection pool sizing and demonstrates why a much smaller pool often yields better performance.

Why Ask for a Small Pool?

When a website handles roughly 10,000 concurrent users (about 20,000 TPS), the intuitive question is "How large should the database connection pool be?" The correct question, however, is "How small should it be?"

Oracle Real‑World Performance Test

A video from Oracle Real World Performance Group runs a stress test with 9,600 concurrent threads, each sleeping 550 ms between database accesses. The middleware pool was initially set to 2,048 connections.

Results:

Average wait in the pool queue: 33 ms

SQL execution time after acquiring a connection: 77 ms

Database CPU near 95 %

Numerous buffer busy‑wait events

Reducing the pool to 1,024 connections (same load) kept the wait time similar but cut SQL execution time, while throughput remained unchanged.

Further reducing the pool to 96 connections lowered the average queue wait to 1 ms and SQL time to 2 ms, eliminating most wait events and increasing throughput. Response time dropped from ~100 ms to about 3 ms.

Fundamental Reasoning

Even on a single‑core CPU, many threads can appear to run simultaneously due to time‑slice scheduling, but only one thread executes at any instant. Adding threads beyond the number of CPU cores eventually incurs context‑switch overhead, degrading performance.

Limited Resources

Performance bottlenecks fall into three categories: CPU, disk, and network. Memory bandwidth is typically much higher, so it is often ignored. Disk I/O involves seek and rotational latency, causing threads to block. While blocked, the CPU can serve other threads, so having more threads than cores can be beneficial when I/O blocking is significant.

With modern SSDs, seek latency disappears, reducing blocking; therefore, fewer threads—closer to the core count—are optimal. The same logic applies to network I/O.

Sizing Formula

Based on PostgreSQL observations, a practical formula for many databases is:

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

"Effective disk count" excludes disks that are fully cached; it approaches the actual number of disks as cache hit rates drop. Hyper‑threading cores are not counted.

Example: a 4‑core i7 server yields (4 × 2) + 1 = 9 connections; rounding to 10 is reasonable. Tests show that exceeding this number quickly increases response latency and reduces TPS.

Practical Guidance

For 10,000 concurrent users, a 10,000‑size pool is absurd; even 1,000 is excessive. A pool of around ten connections, with the remaining business threads waiting in a queue, is ideal. The pool size should match the number of queries the database can handle concurrently, typically not exceeding twice the CPU core count.

Complex workloads mixing long and short transactions may require separate pools for each type. Systems that limit the number of simultaneous tasks should align the task concurrency with the pool size rather than the opposite.

Conclusion

Use a small connection pool and let many threads wait; this reduces wait events, improves throughput, and dramatically lowers response times.