Understanding HikariCP Connection Pool Sizing: Principles, Experiments, and Practical Guidelines

The content is a translation of a HikariCP wiki article that discusses how to size a database connection pool effectively.

It begins by noting that configuring connection pools is a common source of pitfalls and introduces a scenario with 10,000 concurrent users, questioning the appropriate pool size.

Performance tests from an Oracle Real World Performance Group video are presented: with a middleware pool of 2048 connections, each request waited 33 ms in the pool queue and 77 ms for SQL execution; reducing the pool to 1024 connections cut SQL execution time dramatically while keeping throughput similar; further reducing to 96 connections yielded even lower wait times and higher throughput.

The article explains why a smaller pool can be faster, citing CPU core limits, context‑switch overhead, and the fact that threads often block on I/O (disk or network), allowing the OS to schedule other work.

It outlines the three primary resource bottlenecks—CPU, disk, and network—and argues that the optimal number of connections depends on the amount of blocking I/O, especially differing between SSDs and spinning disks.

Connection count = ((core count * 2) + effective disk count) Core count excludes hyper‑threads; effective disk count is zero when data is fully cached and rises as cache hit rate drops.

Applying the formula, a 4‑core i7 server would have an optimal pool size of about 9–10 connections; exceeding this leads to increased latency and reduced TPS.

The article concludes with practical advice: use a small connection pool (often far less than the number of concurrent users), let the remaining business threads wait in a queue, and consider separate pools for long‑running and short‑running transactions.