00:00

Database Connection Pooling Explained

Last updated:

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

Database connection pool with waiting threads Five request threads need the database. The connection pool holds three open connections, all currently borrowed by threads 1, 2 and 3. Threads 4 and 5 wait in a queue for a connection to be returned, up to the configured connection timeout. The database sees only the three pooled connections. Thread 1working Thread 2working Thread 3working Thread 4waiting Thread 5waiting CONNECTION POOL · MAX 3 Connection 1borrowed by thread 1 Connection 2borrowed by thread 2 Connection 3borrowed by thread 3 Wait queuethreads 4, 5 (≤ timeout) Database3 openconnections
Threads borrow an already-open connection instead of opening a new one. When all connections are busy, extra threads wait (up to the connection timeout), and the database never sees more connections than the pool size.

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:

  1. Start-up: the pool opens its minimum number of idle connections in advance.
  2. Borrow: a request thread asks the pool for a connection and receives an idle one within microseconds, already authenticated.
  3. Use: the thread runs its queries inside a transaction.
  4. Return: closing the connection in application code does not close it physically; the pool resets its state and marks it idle again.
  5. 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.
  6. 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.

Life cycle of a pooled connection A connection is created once, which costs a TCP connection, TLS handshake and authentication. It then cycles between idle in the pool and in use by a thread many times. Eventually it is retired after its idle timeout or maximum lifetime, and the pool creates a replacement. CreateTCP + TLS + auth Idlein the pool In useby one thread Retired borrow return idle timeout or max lifetime reached
A connection is opened once and then reused many times between "idle" and "in use". The pool retires it after an idle timeout or maximum lifetime and opens a fresh one.

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.

Connection math with many service instances Twenty instances of a service each run a pool of ten connections, so they can open two hundred connections in total, which is more than the database limit of one hundred connections. pod 1pool 10 pod 2pool 10 pod 3pool 10 pod 4pool 10 … pod 20pool 10 20 × 10 = 200possible connections Databasemax_connections = 100
Every instance has its own pool, so connections multiply: 20 pods × 10 connections = 200, double a PostgreSQL default of max_connections = 100.

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.

External connection pooler Many application pods, each with a small pool, connect to PgBouncer. PgBouncer keeps only twenty real connections open to PostgreSQL and hands them to clients as transactions start and finish. App pod 1many clients App pod 2many clients App pod 20many clients PgBouncerpool of 20server connections PostgreSQL20 connections
An external pooler such as PgBouncer accepts many client connections but keeps only a few real database connections, sharing them between clients.

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.

PgBouncer session and transaction pooling modes Top: in session mode, client A keeps one server connection for its whole session, even while idle. Bottom: in transaction mode, clients A, B and C take turns using one server connection, each only for the duration of a transaction. SESSION MODE Client A server connection held for the whole session (even when idle) TRANSACTION MODE Client A: 1 tx Client B: 1 tx Client C: 1 tx 1 serverconnection
Session mode ties a server connection to one client until it disconnects. Transaction mode lends the connection only for one transaction, so a few connections can serve many clients.

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

AspectApplication-side poolExternal pooler
Where it runsInside each application instanceSeparate proxy between apps and database
ExamplesHikariCP, Apache DBCP, c3p0PgBouncer, ProxySQL, Amazon RDS Proxy
Database connectionsInstances × pool sizeFixed, set on the proxy
Extra network hopNoYes
Best forA modest number of instancesMany instances, serverless functions, bursty scaling
AspectNo poolingConnection pool
Per-request costTCP + TLS + authentication + session set-upBorrow an open connection
Database connectionsOne per concurrent request, unboundedCapped at the pool size
Behaviour under load spikesDatabase can be overwhelmedRequests 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.