Database Connection Pooling Explained
Opening a database connection is surprisingly expensive. The application must open a TCP connection, often negotiate TLS, authenticate, and wait for the database to set up a session; PostgreSQL even starts a new server process for every connection. Doing that for every request would waste milliseconds and quickly overwhelm the database.
Connection pooling avoids this by opening a set of connections once and lending them out again and again. This tutorial explains how a pool works, the settings that matter, how to size it, and why connection limits become a real design concern in microservices. It pairs well with Scalability and Database Replication.
1. What Is Database Connection Pooling?
A connection pool is a managed set of open, reusable database connections. Application threads borrow a connection from the pool, run their queries, and return it, instead of opening and closing a physical connection each time.
The pool limits how many connections exist, replaces broken or old ones, and makes threads wait briefly when every connection is busy. In Java, HikariCP is the most widely used pool and the default in Spring Boot; Apache DBCP and c3p0 are older alternatives. Outside the application, tools such as PgBouncer and ProxySQL pool connections at the network level.
2. Connection Pool Architecture
The pool is a buffer between many application threads and a database that can handle only a limited number of connections efficiently. Its maximum size is effectively a cap on how much concurrent work the application sends to the database.
3. How Connection Pooling Works
Follow one request in an order service using a pool of ten connections:
- Start-up: the pool opens its minimum number of idle connections in advance.
- Borrow: a request thread asks the pool for a connection and receives an idle one within microseconds, already authenticated.
- Use: the thread runs its queries inside a transaction.
- Return: closing the connection in application code does not close it physically; the pool resets its state and marks it idle again.
- Wait or fail: if all ten connections are busy, the next thread waits. If none is returned within the connection timeout, it receives an error instead of hanging forever.
- Maintain: in the background the pool retires connections that were idle too long or reached their maximum lifetime, and validates connections so that broken ones are never handed out.
4. Pool Settings, Sizing and Types of Pooling
Connection life cycle
A pooled connection is created once and reused many times. Retiring connections on a schedule protects against network devices or databases that silently drop long-lived connections.
Settings that matter
- Maximum pool size: the most important setting; the upper limit on open connections. HikariCP defaults to 10.
- Minimum idle: how many idle connections to keep ready. HikariCP recommends leaving it equal to the maximum for a fixed-size pool.
- Connection timeout: how long a thread waits for a free connection before failing (HikariCP default: 30 seconds).
- Idle timeout and maximum lifetime: when idle or old connections are retired (HikariCP defaults: 10 and 30 minutes). Keep the lifetime shorter than any database or firewall connection limit.
- Leak detection threshold: logs a warning when a connection is held too long, which usually means code forgot to close it.
Sizing the pool
Bigger is not better. A database has a limited number of CPU cores, so thousands of connections only add context switching and lock contention. HikariCP's sizing guide suggests starting from connections = (CPU cores × 2) + effective disk spindles, then tuning with load tests. Many busy services run well with pools of 10 to 30 connections.
Application-side pools in microservices
Each service instance has its own pool. When services scale out, the total quickly exceeds what the database allows, which is a common cause of "too many connections" errors after an autoscaling event.
External poolers
A proxy such as PgBouncer sits between the applications and the database. Applications open cheap connections to the proxy, and the proxy multiplexes them over a small number of real database connections.
Session vs transaction pooling
PgBouncer supports three modes. Session mode keeps a server connection for a client's whole session. Transaction mode lends it only for one transaction, which saves the most connections but breaks session-level features such as SET commands, advisory locks and LISTEN. Statement mode is stricter still and disallows multi-statement transactions.
5. Advantages and Disadvantages
Advantages
- Removes connection set-up cost from every request, cutting latency.
- Protects the database by capping concurrent connections.
- Fails fast with a clear timeout instead of piling up requests.
- Replaces broken connections automatically.
Disadvantages
- A pool that is too small makes threads wait; one that is too large overloads the database.
- Leaked connections (never returned) slowly exhaust the pool.
- Per-instance pools multiply as services scale out.
- External poolers add a hop and, in transaction mode, restrict session features.
6. Application Pool vs External Pooler
| Aspect | Application-side pool | External pooler |
|---|---|---|
| Where it runs | Inside each application instance | Separate proxy between apps and database |
| Examples | HikariCP, Apache DBCP, c3p0 | PgBouncer, ProxySQL, Amazon RDS Proxy |
| Database connections | Instances × pool size | Fixed, set on the proxy |
| Extra network hop | No | Yes |
| Best for | A modest number of instances | Many instances, serverless functions, bursty scaling |
| Aspect | No pooling | Connection pool |
|---|---|---|
| Per-request cost | TCP + TLS + authentication + session set-up | Borrow an open connection |
| Database connections | One per concurrent request, unbounded | Capped at the pool size |
| Behaviour under load spikes | Database can be overwhelmed | Requests wait, then time out cleanly |
7. Real-World Use Cases
- Web applications: every Spring Boot service using JDBC runs a HikariCP pool by default.
- Autoscaled microservices: PgBouncer or a managed proxy keeps total connections steady while pods scale out.
- Serverless functions: short-lived functions use a proxy so that thousands of invocations do not open thousands of connections.
- Read replicas: separate pools for the primary and the replicas route writes and reads independently.
8. Best Practices
- Size from the database outward: divide the database's connection budget across all instances, then load test.
- Always return connections: use try-with-resources or framework-managed transactions, and enable leak detection.
- Keep transactions short: never hold a connection while calling another service or waiting for user input.
- Set a sensible connection timeout so that overload produces quick, visible errors.
- Monitor active, idle and pending connections and the time spent waiting for a connection.
- Secure connections: use TLS and least-privilege database users, and keep credentials in a secret store.
9. Summary
- A connection pool reuses open database connections instead of opening one per request.
- Threads borrow and return connections; when all are busy, they wait up to a timeout.
- Maximum pool size is the key setting, and smaller pools often perform better.
- In microservices, total connections equal instances × pool size, so plan the database budget.
- External poolers such as PgBouncer share a few real connections among many clients.
Next, move on to messaging with Asynchronous Communication.
Database Connection Pooling: Top Interview Questions
These are the connection pooling questions most often asked in system design interviews, with short answers you can explain in your own words.
Q1. Why is opening a database connection expensive?
It needs a TCP connection, often a TLS handshake, authentication and session set-up on the server; PostgreSQL also starts a new backend process per connection. That costs milliseconds and server memory, which a pool pays only once.
Q2. What happens when every connection in the pool is busy?
New requests wait in a queue for a connection to be returned. If none becomes free within the connection timeout, the request fails with an error. This protects the database from overload and makes the bottleneck visible.
Q3. Is a larger connection pool always faster?
No. A database can only run as many queries in parallel as its CPU and disks allow. Extra connections add context switching and lock contention. Start small, often 10 to 30 connections, and tune with load tests.
Q4. Why do microservices often run out of database connections?
Each instance has its own pool, so total connections equal instances times pool size. Scaling a service from 5 to 20 pods can push the total past the database limit. Smaller per-instance pools or an external pooler solve this.
Q5. What is the difference between session and transaction pooling in PgBouncer?
Session pooling assigns a server connection to a client for its whole session. Transaction pooling lends it only for one transaction, which serves far more clients with fewer connections but breaks session-level features such as SET, advisory locks and LISTEN.