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:
- TCP 1 RTT, TLS another RTT plus CPU (see networking for backend engineers).
- Authentication with SCRAM-SHA-256: a few more exchanges.
- A backend process: the postmaster forks one process per connection, costing a few MB of memory (an estimate). That’s why
max_connectionsdefaults 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_USEin 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:
- 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. - 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.
- 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): useSET LOCALinside the transaction. - Session advisory locks,
LISTEN/NOTIFY, temp tables that outlive a transaction,WITH HOLDcursors. - 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 likeprepared 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
maximumPoolSizewith the number of virtual threads; connections are still sized by Little’s Law. - A short
connectionTimeoutturns 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
synchronizedpins its carrier thread; older drivers that wrap I/O insynchronizedcan 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
connectionTimeoutplus a few ms;waiting=0because 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
- Little’s Law, average: 3,000 × 0.008 = 24 busy connections across the system.
- 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.
- 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.
- 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. - 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. 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.- Monitor:
pendingandusagep99 during load tests. Highpendingwith 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,keepaliveTimeandconnectionTimeoutfrom 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.