Skip to content
javahacks.dev
Hibernate

HikariCP Pool Size Formula and Common Mistakes to Avoid

Size a HikariCP pool using the cores and spindles formula, Little's Law and measurement, and avoid the common mistakes of oversized pools.

6 min read
Summarize in AI
Flat illustration of an application reaching a database through a JDBC driver, with the Hibernate, OpenJDK, MongoDB, Flyway and MariaDB logos.

The instinct is "more connections, more throughput". For a database it is usually the reverse: beyond a point, more connections make everything slower.

Why small pools work

A database server runs queries on its cores and reads from its storage. If the server has 8 cores and is already busy, 200 concurrent queries do not run faster, they take turns, with context switching, lock contention and cache thrash on top. A smaller pool lets the database finish each query sooner and moves the waiting to the application, where waiting is cheap.

A widely quoted starting rule, from the HikariCP project's "About Pool Sizing" page and the PostgreSQL wiki, is:

Text
connections = (core_count * 2) + effective_spindle_count

For an 8-core database server with an SSD, that gives about 17. Treat it as a starting point for the database host, not a law, and keep the whole fleet in mind.

Little's Law as a check

The number of connections in use on average is the arrival rate times the time each request holds a connection:

Text
connections_in_use = requests_per_second * seconds_holding_a_connection

A service handling 400 requests per second where each request holds a connection for 20 ms needs 400 * 0.02 = 8 connections on average. Add headroom for bursts: 12 to 16. If the same endpoint held the connection for 200 ms (a slow query, or a transaction that also calls an HTTP service), it would need 80, and the better fix is to cut the hold time. The effect of what you do inside a transaction is why keeping transactions short beats growing the pool.

The fleet limit

Each application instance has its own pool, so 20 instances of a service with pool size 20 open 400 connections. PostgreSQL's default max_connections is 100, and each connection costs memory (several megabytes). Compute:

Text
instances * maximumPoolSize + other clients + admin headroom <= max_connections

When autoscaling adds instances, the total grows. Use a pooler such as PgBouncer in front of PostgreSQL, or set a smaller pool per instance.

Common mistakes

  1. Setting the pool to the thread count. A Tomcat with 200 threads does not need 200 connections. Most threads do not hold one at any moment.
  2. Raising the pool to fix timeouts. A connection timeout usually means connections are held too long or leaked, and a larger pool only delays the symptom.
  3. Different values for minimum and maximum. HikariCP's author recommends a fixed-size pool for steady load: minimumIdle equal to maximumPoolSize.
  4. Ignoring other consumers. Batch jobs, migrations and admin tools use connections too.
  5. Copying a number from another system. The right size depends on your queries and hardware.
  6. Holding a connection during remote calls. An open transaction during a call to a payment gateway ties up a connection for seconds.

Measure to confirm

Run a load test at the expected traffic with different sizes (5, 10, 20, 40). Record throughput, latency percentiles, the Micrometer metrics hikaricp.connections.pending and hikaricp.connections.usage, and database CPU and active sessions. The best size is the smallest one after which throughput stops improving and pending connections stay near zero.

With virtual threads

Virtual threads make it easy to start thousands of concurrent requests, and the pool becomes the throttle. The queue inside the pool is cheap, and the connectionTimeout decides how long a request waits. Do not raise the pool to match thread counts. Limiting concurrency to the database is the pool's job, and the sizing is the same.

A worked sizing, start to finish

Take an order service with four instances behind a load balancer, talking to one PostgreSQL primary with 8 cores. Peak traffic is 600 requests per second across the fleet, so 150 per instance. A trace of the busiest endpoint shows 18 ms of connection hold time on average, with a tail at 60 ms.

Text
per instance: 150 req/s * 0.018 s = 2.7 connections in use on average
tail case:    150 req/s * 0.060 s = 9.0 connections

A pool of 10 per instance covers the tail, and the fleet total is 40. That is well under the formula's 17 for the database host if you count only what runs at once, because most requests are not in a query at the same instant, and well under PostgreSQL's default max_connections of 100. If the autoscaler can go to 12 instances, the total becomes 120 and the default limit is gone. That is the number to plan around, not the steady-state count.

The configuration that follows from it

Spring Boot reads the HikariCP settings from spring.datasource.hikari.*:

YAML
spring:
  datasource:
    url: jdbc:postgresql://db:5432/orders
    hikari:
      maximum-pool-size: 10
      minimum-idle: 10
      connection-timeout: 2000
      max-lifetime: 1500000
      leak-detection-threshold: 10000
      pool-name: orders-pool

Each value has a reason. maximum-pool-size and minimum-idle are equal, so the pool never resizes under load. connection-timeout is 2000 ms rather than the 30,000 ms default, because a request that waited 30 seconds for a connection has already failed from the caller's point of view, and a fast error is easier to see and to retry. max-lifetime stays a few minutes below any limit the database or a proxy imposes on connection age, so the pool retires connections before something kills them mid-query. leak-detection-threshold logs a stack trace when a connection is held for more than 10 seconds, which finds the code that forgot to close one.

Watching the pool while it runs

Sizing from a formula is a first guess, and the metrics are the correction. Spring Boot registers the Hikari metrics with Micrometer when Actuator is present. You can read the three that matter without any dashboard:

java
@Component
class PoolReport {

    private final MeterRegistry registry;

    PoolReport(MeterRegistry registry) {
        this.registry = registry;
    }

    @Scheduled(fixedRate = 30_000)
    void log() {
        double active = registry.get("hikaricp.connections.active").gauge().value();
        double pending = registry.get("hikaricp.connections.pending").gauge().value();
        double max = registry.get("hikaricp.connections.max").gauge().value();
        System.out.printf("pool active=%.0f pending=%.0f max=%.0f%n", active, pending, max);
    }
}

Read the numbers this way. If pending is regularly above zero while the database CPU is idle, the pool is too small for the hold time, and you either grow it a little or shorten transactions. If pending is above zero and the database is saturated, a bigger pool will make it worse. If active sits at the maximum and pending is zero, you are right at the edge and a burst will start queueing. The hikaricp.connections.acquire timer shows how long callers wait, and its high percentile is the number your users feel.

A transaction that costs five pool slots

The hold time is the lever you control, and the most common way to inflate it is a remote call inside a transaction:

java
@Transactional
public void checkout(long orderId) {
    Order order = orders.findById(orderId).orElseThrow();
    PaymentResult result = paymentClient.charge(order.total());   // 400 ms over HTTP
    order.markPaid(result.reference());
}

The connection is checked out when the transaction starts and returned when the method ends, so it is held for the 400 ms the payment gateway takes. At 100 checkouts per second that is 40 connections doing nothing. Splitting the method so the charge happens outside the transaction brings the hold back to a few milliseconds:

java
public void checkout(long orderId) {
    Order order = loader.load(orderId);                 // short transaction
    PaymentResult result = paymentClient.charge(order.total());   // no connection held
    marker.markPaid(orderId, result.reference());      // short transaction
}

This trade has a cost: the charge and the update are no longer atomic, so you need an idempotency key on the charge and a way to recover an order that was charged but not marked. That is the usual price of not holding a connection across a network call, and it is better paid in design than in outages.

Where this does not apply

Read replicas, sharded databases and analytics warehouses have different limits, and each pool should be sized for its target. The configuration reference is the HikariCP guide, and thread-pool sizing logic is related in thread pool size. The reference is About Pool Sizing, and the PostgreSQL wiki on connection counts explains the database side.

Share

Keep reading

Get new posts by email

We send one email when a new post is published. No spam.

Unsubscribe in one click. No spam, ever.