HikariCP Tuning: The Math behind Connection Pools

When a backend application starts throwing TimeoutException or experiencing high latency under load, a very common developer instinct is to increase the database connection pool size. If maximumPoolSize=10 is failing, surely maximumPoolSize=100 or 500 will fix the problem, right?

Wrong. In almost all production scenarios, increasing your connection pool size will actively degrade your database performance.

In this deep dive, we will explore the underlying physics of database connections, understand why the "100 connections" rule of thumb is a myth, and learn the mathematical formula required to correctly size a HikariCP pool.

The 10,000 Connections Myth

Let's imagine a single PostgreSQL database server with a 4-core CPU and a fast SSD.

If you configure your Spring Boot service to hold 500 connections in its HikariCP pool, and you have 4 instances of that service running in Kubernetes, your database now has 2,000 open connections.

Context switching is not free. When the OS CPU scheduler halts one thread to allow another to execute, it must save the current thread's state, flush the CPU cache, and load the new thread's state. When you have thousands of active connections fighting for 4 cores, the CPU spends more time switching between tasks than actually executing the queries.

The result? Throughput drops off a cliff, and latency skyrockets.

How Database Connections Actually Work

To understand how to size a pool, we have to look at how a database processes a query. It typically involves two resources:

1. CPU: Parsing the SQL, planning the execution, and crunching the data.
2. Disk I/O: Reading from or writing to the storage subsystem.

While one thread is blocked waiting for Disk I/O (e.g., waiting for the SSD to return a block of data), the CPU is idle. During this tiny fraction of a millisecond, the OS can switch to another thread to execute a different query.

Therefore, the maximum number of connections you need is exactly: The number of CPU cores + The number of threads that can be blocked on I/O simultaneously.

The Golden Formula

The PostgreSQL community, alongside the creators of HikariCP, heavily advocates for the following formula to determine connection pool size:

Let's break this down:

• core_count * 2: This accounts for the CPU cores and provides enough padding for threads that are temporarily blocked on I/O or network wait states.
• effective_spindle_count: In the era of spinning hard drives, this was the number of physical disks in a RAID array (e.g., 4 or 8). In the modern era of SSDs, SSDs don't have "spindles", but they do have multiple flash channels. However, the database sequential I/O engine still processes them sequentially. For a standard enterprise SSD, treat this value as 0. For highly specialized, massive NVMe arrays, it might be 1 or 2.

Practical Example: Tuning for an 8-Core Database

Imagine you are running AWS RDS PostgreSQL on an db.m6g.2xlarge instance, which has 8 vCPUs.

Using the formula: connections = ((8 * 2) + 0) = 16

16 connections. That is the optimal connection pool size for maximum throughput.

If you have two microservice instances connecting to this database, each instance should configure a maximumPoolSize of 8.

It sounds shockingly low, but benchmarking consistently proves that a smaller pool vastly outperforms a larger pool by eliminating lock contention and context switching overhead. A 4-core machine with 10 connections will process 10,000 queries per second much faster than the same machine with 500 connections.

Real-World Configuration

Here is how you would configure HikariCP in a standard Spring Boot application to adhere to these limits.

spring:
  datasource:
    url: jdbc:postgresql://production-db.internal:5432/app
    username: app_user
    password: ${DB_PASSWORD}
    hikari:
      # Hard cap on total connections.
      # Remember: Divide your calculated total pool size by the number of instances.
      maximum-pool-size: 8
      
      # How long a thread will wait for a connection before throwing an exception.
      # 5000ms is a safe default. If queries take longer than this, you have bigger issues.
      connection-timeout: 5000
      
      # How long a connection can sit idle in the pool before being retired.
      idle-timeout: 300000
      
      # Maximum lifetime of a connection. Should be slightly shorter than any database-level timeouts.
      max-lifetime: 900000
      
      # Name the pool for easier debugging in JMX/Prometheus
      pool-name: AppHikariPool

The "But I Need More Concurrent Users" Fallacy

A common counter-argument is: "My web server handles 5,000 concurrent requests per second. I can't service them with only 16 connections!"

This is a misunderstanding of how connection pooling works. A thread only holds a connection for the exact duration of the SQL execution. If a query takes 2 milliseconds to execute, a single connection can process 500 queries per second.

16 connections * 500 queries/second = 8,000 queries per second.

You do not need a 1:1 mapping of web requests to database connections. The web threads will simply queue up for a fraction of a millisecond, grab a connection, execute the query, and instantly release it back to the pool.

Summary

When tuning HikariCP (or any database connection pool), remember that less is more.

1. Calculate your pool size using ((core_count * 2) + effective_spindle_count).
2. Divide that number by the total number of application instances connecting to the database.
3. Fix long-running queries and transactions instead of inflating the pool size to mask the latency.

By respecting the physical limits of your database hardware, you ensure that every query is executed as fast as physically possible, without wasting CPU cycles on context switching.