Skip to content
notes.vinhnq.com

Search

Database & ORMSenior20 min read

Connection pooling and HikariCP: internals, settings and sizing

Review 13 cards
On this page

Many “the DB is slow” incidents in Spring apps are really waiting for a connection: the pool runs dry because connections are held too long, or too many instances open connections to one database. This post explains how HikariCP hands out connections, sets each parameter for a reason, and sizes the pool with numbers. The experiments ran on JDK 21.0.13, HikariCP 7.0.2 and H2 2.3.232; defaults are taken from the HikariCP 7.0.2 source.

1. Why connection pools exist

Key idea: creating a DB connection is expensive, using one is cheap. A pool opens a few connections up front, lends them out and takes them back, so each request only pays the cheap part.

A new PostgreSQL connection goes through:

  1. TCP 1 RTT, TLS another RTT plus CPU (see networking for backend engineers).
  2. Authentication with SCRAM-SHA-256: a few more exchanges.
  3. A backend process: the postmaster forks one process per connection, costing a few MB of memory (an estimate). That’s why max_connections defaults to only 100.

Measured on my machine with H2 running a TCP server on loopback (no TLS, no fork):

Approach Time per operation (200 runs)
Open a new connection + SELECT 1 + close 1.09–1.18 ms
Borrow from HikariCP + SELECT 1 + return 0.042–0.048 ms

About 25× even on loopback. PostgreSQL over a real network with TLS differs even more, but I didn’t measure it.

2. HikariCP internals: how getConnection waits

Key idea: HikariCP keeps connections in a ConcurrentBag. Getting one is a fast lock-free attempt first; if none is free, you queue for someone to return one, with a deadline.

flowchart TD
  A[getConnection] --> B{Thread-local list<br/>has a free connection?}
  B -- yes, CAS succeeds --> R[Check it is alive if idle over 500 ms]
  B -- no --> C{Scan the shared list<br/>for a free connection?}
  C -- yes --> R
  C -- no --> D[Ask for another connection<br/>if total is below max]
  D --> E[Wait on SynchronousQueue<br/>up to connectionTimeout]
  E -- someone returns one or a new one is ready --> R
  E -- time is up --> X[SQLTransientConnectionException]
  R --> OK[Return a proxy connection to the app]
  • Thread-local list: each thread first tries the connections it used recently; usually a hit, with no contention.
  • CAS (compare-and-set): flips the state NOT_IN_USE → IN_USE in one atomic instruction, no lock.
  • Handoff: when a connection is returned and someone is waiting, HikariCP passes it straight through a fair SynchronousQueue (first come, first served).
  • Liveness check: a connection idle for more than 500 ms is checked with isValid(); if dead it’s closed and another is fetched within the remaining time.

Pool exhaustion in the experiment (pool of 2, both held, connectionTimeout=500):

SQLTransientConnectionException after 512 ms
E1 - Connection is not available, request timed out after 505ms (total=2, active=2, idle=0, waiting=0)

Five runs gave 512–522 ms. active=2 equal to total=2 means every connection is borrowed. The next question is always “who is holding them, and for how long”.

The borrow method of ConcurrentBag in HikariCP 7.0.2
public T borrow(long timeout, final TimeUnit timeUnit) throws InterruptedException {
   // 1. try connections this thread used recently (threadLocalList), CAS NOT_IN_USE -> IN_USE
   // 2. scan sharedList, CAS each entry
   final var waiting = waiters.incrementAndGet();
   try {
      listener.addBagItem(waiting);                          // 3. ask for a new one (if below max)
      timeout = timeUnit.toNanos(timeout);
      do {                                                   // 4. wait for someone to return one
         final var start = currentTime();
         final T bagEntry = handoffQueue.poll(timeout, NANOSECONDS);
         if (bagEntry == null || bagEntry.compareAndSet(STATE_NOT_IN_USE, STATE_IN_USE)) return bagEntry;
         timeout -= elapsedNanos(start);
      } while (timeout > 10_000);
      return null;                                           // timed out -> the pool throws
   } finally {
      waiters.decrementAndGet();
   }
}

(Steps 1–2 abbreviated.) New connections are created on a separate executor, not on the waiting thread, so a database that is slow to connect can’t push you past connectionTimeout.

Note the liveness check: if a connection has “died silently” behind NAT, isValid() may wait up to validationTimeout (5 seconds by default) before giving up. That’s why maxLifetime should replace connections before a box in the middle forgets them.

3. The settings that matter

Key idea: most settings answer three questions: how long to wait for a connection, how long a connection lives, and how to find out who holds one too long.

Setting (Spring: spring.datasource.hikari.*) 7.0.2 default Meaning and how to set it
maximumPoolSize 10 Upper bound on connections. Pick it as in section 4
minimumIdle same as maximumPoolSize Leave it: a fixed pool doesn’t create connections during a spike
connectionTimeout 30 s (min 250 ms) Max wait to borrow. Make it shorter than the request timeout (e.g. 1–3 s) to fail fast
idleTimeout 10 min Only matters when minimumIdle < maximumPoolSize
maxLifetime 30 min (min 30 s) Max age; up to 25% is subtracted at random so connections don’t all expire together
keepaliveTime 2 min (min 30 s, below maxLifetime) Periodically “touches” idle connections so the DB and network don’t close them
leakDetectionThreshold 0 = off (min 2 s) Logs the stack trace where a connection was borrowed and not returned in time
validationTimeout 5 s Max time to check that a connection is alive
Leak detection: the real log when a connection is held past the threshold

4. Sizing the pool

Key idea: the pool only needs enough connections for the ones busy at the same time. The DB can only run so much in parallel given its cores and disks; a bigger pool just moves the queue inside the DB.

Three numbers to pick it:

  1. Starting point (HikariCP’s “About Pool Sizing” wiki, originally from PostgreSQL): (DB cores × 2) + effective spindles. An 8-core DB on SSD: about 17 connections in total, not per instance.
  2. Little’s Law (items in a system = arrival rate × time spent inside, see back-of-the-envelope estimation): 2,000 transactions/second × 5 ms held = 10 busy connections on average.
  3. The DB ceiling: instances × maximumPoolSize + jobs, migrations, admin < max_connections. 20 pods × 10 = 200 already exceeds PostgreSQL’s default of 100.

The experiment matches Little’s Law: pool of 10, 200 tasks each holding a connection for about 55 ms. The ceiling is 10 / 0.055 s ≈ 180 tasks/second, so 200 tasks need about 1.1 seconds; I measured 1,112–1,167 ms.

5. PgBouncer transaction pooling and its traps

Key idea: PgBouncer is a pool sitting between the applications and PostgreSQL. In transaction mode a real connection belongs to a client only for one transaction, so thousands of app connections share a few dozen DB connections. In exchange, anything tied to the session can no longer be trusted.

Mode A real connection belongs to the client for Use when
session The whole session You need every session feature
transaction One transaction Many instances, short transactions: the most common
statement One statement Rare; multi-statement transactions are forbidden

Your next transaction may run on a different connection, so these break:

  • Session-level SET (search_path, timezone, statement_timeout): use SET LOCAL inside the transaction.
  • Session advisory locks, LISTEN/NOTIFY, temp tables that outlive a transaction, WITH HOLD cursors.
  • Server-side prepared statements: pgjdbc switches to named prepared statements after prepareThreshold (default 5) executions, and a later transaction on another connection fails with something like prepared statement "S_1" does not exist.

6. @Transactional holding a connection during a remote call

Key idea: Spring takes a connection when the transaction begins and returns it only on commit or rollback. Everything that runs inside, HTTP calls included, adds to the hold time.

@Transactional
public void pay(long orderId) {
    Order o = orders.findById(orderId).orElseThrow();
    PaymentResult r = gateway.charge(o);     // HTTP 50 ms–30 s, the connection is still held
    o.markPaid(r.reference());
}

Experiment (5 runs): 200 tasks on virtual threads, pool of 10, each task runs SELECT 1 and a 50 ms “remote call”.

Where the 50 ms call sits connectionTimeout Succeeded / failed Wall time Max waiting threads
While holding the connection 30 s 200 / 0 1,112–1,167 ms 192
While holding the connection 500 ms 98 / 102 524–557 ms 192–193
Before borrowing the connection 30 s 200 / 0 54–62 ms 0–1
Before borrowing the connection 500 ms 200 / 0 55–63 ms 0–2

Same work: moving the call out of the hold window is about 20× faster with no errors. The fix: make the remote call first, then open a short transaction to write the result (TransactionTemplate, or a separate bean so the @Transactional proxy applies). If the two steps must stay consistent, use an intermediate state, an idempotency key or an outbox, see distributed transactions, sagas and the outbox.

7. Virtual threads and pools

Key idea: virtual threads make threads nearly free, so the ceiling is no longer Tomcat’s 200 threads but the number of connections. The pool becomes the main back-pressure point, and connectionTimeout decides whether overload turns into waiting or into errors.

  • Don’t grow maximumPoolSize with the number of virtual threads; connections are still sized by Little’s Law.
  • A short connectionTimeout turns overload into fast failures (table above: 102 errors after about 500 ms) instead of thousands of requests hanging for 30 seconds.
  • Pinning: on JDK 21–23, a virtual thread blocking on I/O inside synchronized pins its carrier thread; older drivers that wrap I/O in synchronized can exhaust carriers. JDK 24 (JEP 491) removes this limitation.

Thread pools, virtual threads and Semaphore are covered in ExecutorService and virtual threads; the epoll layer underneath in the I/O model and epoll.

8. HTTP client pools and thread pools

Key idea: HTTP clients have pools too, with their own default limits, often smaller than you’d think. When you call one host a lot, the per-route (per-host) limit is the real ceiling.

Client (version checked) Defaults Notes
Apache HttpClient 5 (PoolingHttpClientConnectionManager, 5.5.1) 25 total, 5 per route The 6th concurrent request to one host waits for a lease; set connectionRequestTimeout
OkHttp (ConnectionPool, 4.12.0) Up to 5 idle connections, kept 5 minutes Concurrency is limited by the Dispatcher
JDK HttpClient (JDK 21) Unbounded HTTP/1.1 pool, idle connections kept 30 seconds HTTP/2 multiplexes everything on one connection per host

Tomcat’s 200 threads with a 10-connection Hikari pool: when the DB is slow, up to 190 threads wait, which is fine with a short connectionTimeout and dangerous with 30 seconds. Create an HTTP client once and share it (web and HTTP); timeouts and bulkheads are in Timeouts, retries and circuit breakers.

9. Metrics to watch

Key idea: three questions: is anyone waiting for a connection, how long is a connection held once borrowed, and are borrows timing out. HikariCP publishes them via Micrometer (Actuator; Prometheus turns dots into underscores).

Metric Meaning Alert when
hikaricp.connections.pending Threads waiting for a connection Above 0 for several minutes
hikaricp.connections.active / .idle / .max Borrowed, idle, ceiling active stuck at max
hikaricp.connections.acquire Time spent waiting to borrow p99 rising: pool or DB congested
hikaricp.connections.usage Time a connection is held p99 rising: long transactions, remote calls inside transactions
hikaricp.connections.timeout Failed borrows Anything above 0
hikaricp.connections.creation Time to create a connection Created too often: maxLifetime too short or the DB is cutting connections

usage is the most ignored, even though by Little’s Law it decides busy connections together with throughput: a rising usage p99 warns that the pool will run dry before pending does.

Exercises

Exercise 1: Reproduce pool exhaustion and connectionTimeout

Write a JDK 21 program with HikariCP and H2: (a) a pool of 2, hold both connections and borrow a third with connectionTimeout=500, measure the time and read the error message; (b) a pool of 10 with 200 virtual threads, comparing “50 ms remote call while holding the connection” with “before borrowing”, and record the maximum pending count.

Solution to exercise 1: code and results

Classpath: HikariCP-7.0.2.jar, h2-2.3.232.jar, slf4j-api-1.7.36.jar, slf4j-simple-1.7.36.jar. Compile with javac -cp $CP PoolLab.java, run java -cp $CP:. PoolLab.

import com.zaxxer.hikari.*;
import java.sql.*;
import java.util.concurrent.*;
import java.util.concurrent.atomic.*;

public class PoolLab {
    static HikariDataSource pool(String name, int size, long timeoutMs) {
        HikariConfig c = new HikariConfig();
        c.setPoolName(name);
        c.setJdbcUrl("jdbc:h2:mem:lab;DB_CLOSE_DELAY=-1");
        c.setUsername("sa");
        c.setMaximumPoolSize(size);
        c.setConnectionTimeout(timeoutMs);
        return new HikariDataSource(c);
    }

    public static void main(String[] args) throws Exception {
        // (a) abbreviated: pool("E1", 2, 500), two threads hold a connection for 3 s, the main thread calls getConnection()
        //     and prints the exception name, the wait time and e.getMessage()
        // (b) holding the connection during the remote call or not
        for (boolean hold : new boolean[]{true, false}) for (long timeout : new long[]{30_000, 500}) {
            try (HikariDataSource ds = pool("E2", 10, timeout)) {
                ds.getConnection().close(); Thread.sleep(300);           // warm up, let the pool fill to 10
                AtomicInteger ok = new AtomicInteger(), failed = new AtomicInteger(), maxPending = new AtomicInteger();
                HikariPoolMXBean mx = ds.getHikariPoolMXBean();
                ScheduledExecutorService sampler = Executors.newSingleThreadScheduledExecutor();
                sampler.scheduleAtFixedRate(() -> maxPending.accumulateAndGet(mx.getThreadsAwaitingConnection(), Math::max), 0, 5, TimeUnit.MILLISECONDS);
                long t = System.nanoTime();
                try (ExecutorService ex = Executors.newVirtualThreadPerTaskExecutor()) {
                    for (int i = 0; i < 200; i++) ex.submit(() -> {
                        try {
                            if (!hold) Thread.sleep(50);                   // remote call before borrowing
                            try (Connection c = ds.getConnection(); Statement st = c.createStatement()) {
                                st.executeQuery("SELECT 1").close();
                                if (hold) Thread.sleep(50);                // remote call while holding
                            }
                            ok.incrementAndGet();
                        } catch (SQLException e) { failed.incrementAndGet(); }
                          catch (InterruptedException e) { Thread.currentThread().interrupt(); }
                    });
                }
                sampler.shutdownNow();
                System.out.printf("hold=%-5s timeout=%5d: ok=%3d failed=%3d wall=%4.0f ms maxPending=%d%n",
                        hold, timeout, ok.get(), failed.get(), (System.nanoTime() - t) / 1e6, maxPending.get());
            }
        }
    }
}

One run (the others fall within the ranges in sections 2 and 6):

SQLTransientConnectionException after 512 ms
E1 - Connection is not available, request timed out after 505ms (total=2, active=2, idle=0, waiting=0)
hold=true  timeout=30000: ok=200 failed=  0 wall=1112 ms maxPending=192
hold=true  timeout=  500: ok= 98 failed=102 wall= 524 ms maxPending=192
hold=false timeout=30000: ok=200 failed=  0 wall=  54 ms maxPending=0
hold=false timeout=  500: ok=200 failed=  0 wall=  55 ms maxPending=0

Reading the results:

  • (a) The error arrives after connectionTimeout plus a few ms; waiting=0 because the thread that just timed out has already left the queue.
  • (b) While holding, 10 connections serve about 20 rounds × 55 ms ≈ 1.1 s, exactly Little’s Law. With a 500 ms timeout, only tasks served in the first 9–10 rounds succeed (98); the rest fail. With the call made first, 200 virtual threads sleep 50 ms in parallel and each task then holds a connection for tens of microseconds, so nobody waits.

Not run: real PostgreSQL over a network with TLS, PgBouncer (Docker isn’t running on this machine).

Exercise 2: Size the pool for a system

An order service runs 6 instances, autoscaling up to 12. PostgreSQL has 8 cores, SSD, max_connections = 200. At peak the whole system does 3,000 transactions/second, each holding a connection 8 ms on average, 30 ms at p99. A batch job needs 10 connections, and 10 must be reserved for admin and migrations. Choose maximumPoolSize and connectionTimeout.

Solution to exercise 2
  1. Little’s Law, average: 3,000 × 0.008 = 24 busy connections across the system.
  2. When slow: if many transactions hit p99 at once, 3,000 × 0.03 = 90. That’s the ceiling when the DB is already slow; granting all 90 would only slow it further.
  3. Formula: 8 × 2 + 1 = 17 connections working efficiently at once. 24 is a bit above that: the DB is close to saturation at peak, so shorten hold times (8 ms is long for OLTP) before considering a bigger pool.
  4. Per instance: at 12 instances (the worst case for connection count), 24 / 12 = 2 busy per instance; at 6 instances, 4. Choose maximumPoolSize = 6: enough for 4 busy on average plus headroom at 6 instances, and 72 connections at 12.
  5. Check the ceiling: 12 × 6 + 10 (batch) + 10 (admin) = 92 < 200. There’s room for rolling deploys (old and new instances alive together) without hitting max_connections.
  6. connectionTimeout: with an API promise of p99 ≤ 1 second, use about 500 ms–1 s: running out of connections fails fast with a 503 the client can retry, instead of holding a thread for 30 seconds.
  7. Monitor: pending and usage p99 during load tests. High pending with spare DB CPU: raise it to 8; high DB CPU: don’t raise it, optimize queries.

If you later need 50 instances, 50 × 6 = 300 exceeds the ceiling: put PgBouncer in transaction mode in front of PostgreSQL instead of shrinking the pool to 2–3.

Remember

  • List the expensive steps of opening a PostgreSQL connection and why a pool is faster.
  • Set maxLifetime, keepaliveTime and connectionTimeout from the DB and network timeouts.
  • Size a pool with the formula and Little’s Law, checking instances × pool size against max_connections.
  • Know the traps of PgBouncer transaction mode, remote calls inside @Transactional, and open-in-view.

Interview questions

Junior

Why does an application need a connection pool?

Because opening a DB connection is expensive (TCP, TLS, authentication, a server process), while reusing an existing one is almost free.

A pool also caps connections to the DB, so under rising load the app queues instead of knocking the database over. Measured on loopback with H2: about 1.1 ms to open a new one, about 0.045 ms to borrow from the pool.

The app logs “Connection is not available, request timed out after 30000ms”. What does it mean?

The pool had no free connection and the thread waited the full connectionTimeout (30 seconds by default) without anyone returning one.

Compare active with total in the message, then look for long holds: long transactions, remote calls inside transactions, connection leaks, or a slow DB.

Mid

What are maxLifetime and keepaliveTime for?

maxLifetime replaces a connection before the DB or a network device closes it; keepaliveTime periodically touches idle connections so they don’t look idle.

maxLifetime must be a few seconds shorter than every limit on the path (MySQL wait_timeout, firewalls, NAT). HikariCP subtracts up to 25% at random so connections don’t all expire together.

Why is calling HTTP inside @Transactional dangerous?

The connection is held for the whole HTTP call, so hold time grows with the other side’s latency and the pool runs dry fast.

Experiment: holding 50 ms longer slowed 200 tasks from 55 ms to 1,100 ms; with a 500 ms timeout, half of them failed. Make the remote call first, then open a short transaction to write.

What does PgBouncer transaction mode break?

Anything tied to the session: session-level SET, session advisory locks, LISTEN/NOTIFY, temp tables, WITH HOLD cursors, and server-side prepared statements unless configured.

Use SET LOCAL, PgBouncer 1.21+ with max_prepared_statements, or prepareThreshold=0 in pgjdbc.

Senior

How do you size a connection pool?

Start from Little’s Law (throughput × hold time) and the DB’s (cores × 2) + disks formula, divide by the number of instances, and check the total stays below max_connections.

Then load test, watching pending, usage and DB CPU. Grow only when threads wait and the DB has headroom; if the DB is saturated, shorten hold times. With too many instances, use PgBouncer.

Does pool configuration change when you move to virtual threads?

The pool size doesn’t need to grow, but connectionTimeout must be short and concurrency explicitly bounded, because the pool becomes the main back-pressure point.

Previously Tomcat’s 200 threads limited how many could wait; with virtual threads thousands may wait for a connection. On JDK 21–23, also watch for pinning when a driver wraps I/O in synchronized.

After a slow-DB incident the pool doesn’t recover: pending stays high even though the DB is fine. What do you suspect?

A backlog and retries keeping load high, connections that died silently behind NAT and are slow to validate, or a leak holding connections.

Look at usage p99 (abnormally long holds), timeout, leak detection logs, and pg_stat_activity (idle in transaction). Cut retries, use a short connectionTimeout to drain the queue, and a maxLifetime shorter than the network timeouts so dead connections get replaced.

Useful phrases for interviews

  • “The pool pays the handshake cost once and lends connections out.”
  • “Pool exhaustion showed up as connection timeouts, not as slow queries.”
  • “Hold time, not pool size, decided our throughput.”
  • “Borrow late and return early.”
  • “Leak detection pointed at a code path that never closed the connection.”
  • “With virtual threads, the pool is the back-pressure point.”
  • “Transaction pooling breaks anything that relies on session state.”
  • “We kept headroom under max_connections for rolling deploys.”
  • “Once the database is saturated, more connections only add contention.”
  • “Pending threads stayed above zero for ten minutes, so we paged.”

Self-compiled notes from official documentation and hands-on practice.