DataSource and HikariCP: Pool Internals and Tuning

A database connection is a phone line: dialling it goes through the exchange (the TCP handshake), you state your name and password (authentication), then you agree on language and time zone (session initialisation) — 5–20 ms in total. But the sentence you actually want to say (one indexed SQL statement) usually takes under 1 ms. In other words, if every request dials from scratch, nine tenths of your bill is for "getting through", not for "talking". A connection pool does exactly one thing: lay out a batch of already-dialled lines, let anyone borrow one, and hang up when done — and note that "hanging up" does not cut the line, it only marks it idle.

Six terms, one line each (used throughout):

• Connection: an established channel between your app and the database; underneath it sits a TCP socket, which is expensive to build — hence precious
• DataSource: a JDBC-spec interface with a single contract, getConnection() — "give me a usable connection". Whether it is brand new or borrowed is none of the caller's business
• Connection pool: the rental station that implements DataSource; it pre-creates, reuses, health-checks and recycles connections
• HikariCP: the pool Spring Boot ships by default (the name means "light" in Japanese); fast because of lock-free borrowing, proxy wrapping and background housekeeping
• Borrow / return: taking a connection out of the pool is borrowing; calling close() afterwards is returning it. Inside a pool close() does not mean disconnect
• Leak: borrowed but never returned — one thread keeps gripping a connection while everyone else queues, and the pool slowly drains dry

The left side is the real bill for "dial a fresh connection every time"; the right side is what pooling gives you. Everything else in this article answers two questions: why the right side is mandatory, and where exactly to set its knob.

After this article you should be able to answer three questions:

• Why does Spring Boot reach the database without a single line of pool configuration? Who is that "default pool"?
• Is maximumPoolSize better when bigger? If not, why does the official rule of thumb come out as something as small as "cores × 2 + spindles"?
• Production suddenly floods with Connection is not available, request timed out after 30004ms — which metric do you look at first, and which config line?

Turn the knob first and get a feel for it — this is the article's only sandbox; change one parameter and the three numbers on the right move immediately:

"Why not open a new database connection on every request?" Start with the bill. Establishing a genuinely usable connection means doing all of this, in order:

And an ordinary indexed query? Often under 1 ms. So if every request opened a connection, "establishing the connection" would cost more than ten times "doing the actual work" — the core contradiction.

Connection setup is expensive, yet the connection itself is a reusable long-lived channel. The natural optimisation follows: pre-create a batch of connections and recycle them — borrow when needed, return when done. That is the idea of pooling — database connection pools, thread pools, object pools, all the same trick: spread the cost of creating an expensive resource across N reuses.

The previous article left a thread: JdbcTemplate never creates connections itself; it asks a DataSource. DataSource is the standard interface defined by the JDBC spec, and getConnection() is the whole contract — whether the returned connection is brand new or borrowed from a pool is none of the caller's business. That is the beauty of programming to an interface: switch the implementation and not a line of business code changes.

Four implementations dominate:

HikariCP is fast not through black magic but by driving every source of overhead in a high-concurrency pool down to near zero. The key designs:

All of those class names look familiar, yet each guards a different segment, and first-timers always mix them up. Play a round: pick a component, then pick what it actually saved you — a wrong pair explains itself on the spot.

And "the life of a connection" does not start at getConnection() nor end at close() — when it is born, examined and forcibly retired is all down to a background scheduler. Click through this ring; box ③ is the one you control:

With the structure clear, one full "borrow and return" follows naturally:

That one is the pool's point of view. The animation below switches to a single request thread's point of view: the same getConnection() first checks its own thread's fast lane, then the shared stack, creates one only if nothing is free, and queues when the cap is hit — seven steps before you can see where a timeout actually comes from.

1. The business code calls getConnection(): triggered inside JdbcTemplate before it runs SQL
2. Look for an idle connection: ConcurrentBag first tries the current thread's local cache, then the shared area
3. Hit: borrow directly: the PoolEntry is wrapped in a ProxyConnection and returned — you hold a proxy
4. Miss and below the limit: create one: a full TCP + authentication round (the bill from Section 1)
5. At the limit: queue and wait: no longer than connectionTimeout, then SQLTransientConnectionException
6. Business calls close(): return it: the proxy intercepts close() and returns the connection to the pool (not closing it), resetting session state

The line to remember above all: in a connection pool, Connection.close() never means "disconnect" — it means "return". The physical connection is reused by the next request until evicted by maxLifetime / idleTimeout.

Tuning a pool is really tuning the parameters below. Understand what each governs, then talk about values.

Those seven knobs belong to two different worlds: the first four stand on the borrow path, and when they are wrong the application waits for a connection; the last three belong to housekeeping, and when they are wrong you receive a dead connection. Without that split, tuning is guesswork:

So what should maximumPoolSize be? The HikariCP docs cite a counter-intuitive rule of thumb:

Its meaning matters more than the number:

• The bottleneck is usually disk IO and lock waits, not database CPU. More connections only make more threads queue for the disk; a bigger pool just lets more threads queue together
• Connections are "concurrency", not "throughput". On an 8-core box, 16–20 is usually plenty; pushing it to hundreds just burns the database on context switching
• The formula is a starting point, not an answer: the real work is load testing — begin at the formula value and adjust from the metrics

The formula is only a starting point; the feel comes from dragging the number. This console uses the article's own headline knob — slide it from 1 to 200 and the verdict changes all the way along. Watch the far right especially: "no queueing" and "faster" are not the same claim:

A quick check (the first question is light, the second asks you to read a live incident):

Both extremes are dangerous, but they look completely different — the contrast points straight at the fix:

Once you have diagnosed "queueing", the next knob decides how long you are willing to keep queueing. connectionTimeout is the one parameter that stays silent when misconfigured and then fails everywhere at once: too short and ordinary peak jitter gets killed; 0 and threads stand in that queue until the Tomcat pool is empty. Slide it and find the edges:

You cannot tune what you cannot see. HikariCP exposes a set of metrics via Micrometer, queryable through Actuator:

A copy-ready config with every key parameter:

At startup the log states the pool's status clearly:

That listing is someone else's answer. Which lines your own project needs is worth generating rather than copying: tick "datasource" alone for the minimum that connects, then add "logging" and "actuator" and watch how the hikaricp.connections.* metrics from Section 7 become queryable:

When you need read/write splitting or multiple databases, the idea is not "just configure several pools" but a routing data source switching between pools: extend AbstractRoutingDataSource and override determineCurrentLookupKey, keeping the current target in a ThreadLocal; or use dynamic-datasource-spring-boot-starter and annotate a method with @DS("slave"). The core insight is unchanged: each data source sits in front of its own HikariCP pool, and each pool size must be estimated separately.

The second trap (maxLifetime above wait_timeout) shows up as "I received a dead connection". The first one — a leak — is a slow draining: nothing fails on the spot, which is precisely why nobody recognises it in time. Run these six frames together and the "a restart fixes it, then it recurs" fingerprint becomes something you have seen:

The second question reads a live scene (get this one wrong and production will hurt):

The demo below shows how auto-configuration wires the DataSource. Toggle "the user has defined a DataSource" and watch @ConditionalOnMissingBean let auto-configuration step aside for the user's definition — exactly the mechanism that makes the Section 8 config work.

Everything above has been description; this section is hands-on. A connection pool is something you can push buttons on — the five demos below run the real kernel (WASM); click a parameter and it recomputes, so you can watch "idle hit", "create", "queue", "timeout" and "leak" as five genuinely different pictures. Suggested order: run the first to build intuition, then work down the list, and finish with the last two to understand production alerts.

Start with the happy case: the pool holds an idle connection, getConnection() returns at once, and close() hands it back. Notice that the identity of the physical connection in the output never changes — that is reuse.

How to read each parameter:

JdbcTemplate never creates connections; it only asks a DataSource. Switch this demo to "what if you forget to release" and you will see the finally that hand-written JDBC most often omits: the moment an exception branch skips close(), that connection never comes back. This is exactly why try-with-resources exists.

Mapping for reference: one row = query, affected rows = update, batch = batch, ResultSet → object = map, leak = leak. The first four belong to article 27; in this article keep your eye on the last one: identical code, with and without finally / try-with-resources, gives the pool two completely different fates.

You cannot tune what you cannot see. spring-boot-starter-actuator turns those hikaricp.connections.* metrics from section 7 into clickable endpoints, and also demonstrates the risk of exposing everything — /actuator/env will happily print your database password.

The minimal beginner-safe configuration (three stanzas, copy and go):

With that in place you can verify the sandbox numbers directly: curl localhost:8080/actuator/metrics/hikaricp.connections.pending.

An often-missed fact: all statements inside one transaction reuse the same connection, because TransactionSynchronizationManager binds it to a ThreadLocal. So the code below borrows once and returns once:

Change the propagation to REQUIRES_NEW or NOT_SUPPORTED and the number of borrows changes — which means the transaction boundary decides not only whether data rolls back, but also how many connections you hold and for how long.

Enough buttons — type the same things yourself. This console talks to the same in-browser Java kernel and every reply is computed there: start with beans to see which DataSource the container holds, then walk the five stages of a borrow with lab pool:

What beginners fear is not the concepts but the wall of red text. This table quotes pool-related errors in a form you can search verbatim (with the key class names and fragments); just follow the third column.

Most beginners point at the wrong frame for that first row of the table, because the method at the top of the stack looks most guilty. Here is a real stack — do not read the answer, just click the frame you think is responsible:

Goal: run six concurrent tasks against a two-connection pool and see a real timeout exception once. Fully runnable code:

Expected console output (timestamps differ slightly, but the shape must match):

Confirm four things against that output:

• The first two finish almost together = each borrowed one connection (capacity 2)
• Tasks 3 and 4 are late by exactly two seconds = they were queueing until earlier connections came back
• Tasks 5 and 6 throw the very message from row one of the lookup table, and it carries the pool name tiny-pool — always name your pools in production, otherwise several pools failing together are impossible to tell apart
• The trailing idle=2 proves nothing was destroyed; the connections merely went home

Change one item at a time, rerun, and write down what you see:

1. maximumPoolSize from 2 to 6 → you will observe: all six tasks finish around 2 seconds and waiting stays 0. That is the feel of the "just right" cell in the sandbox.
2. maximumPoolSize to 200 with SELECT SLEEP(2) unchanged → you will observe: MySQL's SHOW STATUS LIKE 'Threads_connected' climbs steadily, database CPU rises, and your business is no faster — connections are concurrency, not throughput.
3. Replace try (var conn = ...) with a manual var conn = ds.getConnection(); that never closes → you will observe: after a few rounds tiny-pool - Connection is not available appears, plus Apparent connection leak detected once leakDetectionThreshold is 1000. That is a leak in full: fewer connections each round, fixed by restart, recurring later.
4. Add cfg.setMaxLifetime(60_000) and temporarily set MySQL wait_timeout to 30 seconds → you will observe: after some idleness, occasional Communications link failure. Conclusion: let the pool retire connections before the database does.

Requirement: write a /orders/recent endpoint (JdbcTemplate, 20 orders) with an observation panel you can open in a browser. Acceptance checklist:

• [ ] application.yml spells out six settings — pool-name, maximum-pool-size, connection-timeout, max-lifetime, keepalive-time, leak-detection-threshold — each with a comment saying what it governs (numbers alone do not count)
• [ ] You can point out the three startup lines Starting... / Added connection / Start completed. and state how many connections were pre-warmed
• [ ] With spring-boot-starter-actuator added, GET /actuator/metrics/hikaricp.connections.pending returns 0 while idle
• [ ] Hammer the endpoint with 100 concurrent clients (JMeter / wrk / ab), setting maximum-pool-size to 2, 10 and 50 in turn, recording each time: P99 latency, peak pending, database Threads_connected
• [ ] Write a five-line conclusion from those three runs: where is the knee? Why do more connections not help? Is the bottleneck in the app or the database?
• [ ] Manufacture one deliberate leak (borrow without returning), locate the exact line through the leak-detection-threshold stack warning, and fix it
• [ ] Bonus: chart the P99 of hikaricp.connections.acquire in Grafana and add an alert rule for "pending > 0 for 60 seconds"

Finish this tier and you have moved from "knowing a pool exists" to "operating one with data".