How Database Connection Pooling Works
IntermediateOpening a database connection is expensive: a TCP handshake, authentication, and session setup can take milliseconds each — an eternity if you do it per request. A connection pool keeps a set of open connections ready and hands them out on demand, returning them to the pool when done. This turns a costly setup into a cheap borrow-and-return, and it caps how many connections hit the database at once. HikariCP is the fast, default pool in Spring Boot.
Think of a connection pool as a fleet of taxis
Instead of building a new car every time someone needs a ride (opening a connection), a taxi company keeps a fleet idling at a rank (the pool). A passenger takes an available taxi, uses it, and returns it for the next person. If all taxis are out, new passengers wait briefly (or are told none are free — a timeout). The fleet size also limits congestion on the roads (the database), preventing gridlock.
Step by Step
Key Concepts
Maximum Pool Size
The cap on concurrent open connections. Bigger is not better — it must fit what the database can handle. A common rule is a small multiple of CPU cores on the database, not one per app thread.
HikariCP
The high-performance JDBC connection pool that is the default in Spring Boot. It is fast because it minimises locking and overhead on the borrow/return path.
Connection vs Idle Timeout
Connection timeout is how long a caller waits for a free connection before failing. Idle timeout retires connections that sit unused. Max lifetime recycles connections periodically to avoid stale ones.
Connection Leak
A borrowed connection never returned to the pool (a missing close in an error path). Leaks exhaust the pool and cause requests to hang or time out — leak detection flags them.
Key Facts
- A too-large pool can overload the database and actually reduce throughput; sizing the pool to the database capacity is more important than making it big.
- Calling close() on a pooled connection returns it to the pool — always close in a finally block or try-with-resources to avoid leaks.
- Under load, symptoms of an undersized pool are requests queueing and connection-timeout errors, not database slowness.
Real-World Applications
Sizing a pool for a service
A service with 4 instances sharing a database with a 100-connection limit should keep each pool small (e.g., 10–20) so all instances combined stay within the database ceiling.
Diagnosing timeouts under load
When p99 latency spikes and logs show connection-timeout errors, the fix is often not a bigger pool but fixing a slow query holding connections too long, or a leak from an unclosed connection.
Frequently Asked Questions
Why is connection pooling necessary?
Opening a database connection involves a TCP handshake, authentication, and session setup — milliseconds of overhead. Doing that per request would cripple throughput. A pool opens connections once and reuses them, turning an expensive operation into a cheap borrow-and-return, and it caps how many connections hit the database.
How do I choose the maximum pool size?
Size it to what the database can handle, not to your app thread count. A small pool (often a low multiple of the database CPU cores) usually gives the best throughput; oversized pools cause database contention. Remember to account for all app instances sharing the same database.
What causes a connection leak?
A connection borrowed from the pool but never returned — typically a missing close() in an error path. Leaks slowly drain the pool until no connections are free and requests hang. Use try-with-resources and enable HikariCP leak detection to catch them.
What happens when the pool is exhausted?
New requests wait for a connection to be returned, up to the connection-timeout limit. If none frees up in time, the caller gets a timeout exception. Frequent timeouts usually indicate slow queries holding connections, a leak, or an undersized pool.