Skip to content
Velloreth
Programming 6 min read

Connection Pooling Explained for Developers

Opening a connection is expensive, so a pool reuses them. Learn the sizing, the exhaustion, the timeout, and the serverless trap.

By

Why a pool exists

Opening a connection is expensive.

A TCP handshake is one round trip.

A TLS handshake is two more, if the connection is encrypted.

An authentication is another round trip, and the database may hash a password.

A session setup allocates memory and a backend process on the database.

The total is tens of milliseconds, which is more than the query itself for a simple request.

A pool pays that cost once and reuses the connection, which is why it is essential for any application with more than a trivial load.

How a pool works

Application                    Pool                    Database
    │                            │                         │
    ├─ acquire ─────────────────>│                         │
    │                            ├─ (reuse an idle one) ──>│
    │<─ connection ──────────────┤                         │
    │                            │                         │
    ├─ query ────────────────────────────────────────────> │
    │<─ result ───────────────────────────────────────────┤
    │                            │                         │
    ├─ release ─────────────────>│                         │
    │                            │  (returned to the pool) │

acquire takes a connection, and release returns it.

If no connection is available, the request waits until one is released or a timeout expires.

Sizing

A pool that is too small queues requests, and the latency rises under load.

A pool that is too large overwhelms the database, because each connection consumes memory and a backend process.

The counter-intuitive part: a larger pool is often slower. A database with 4 CPU cores cannot run 100 queries in parallel; it runs 4 at a time and the rest wait, with the added cost of context switching.

A common starting point:

pool_size = (cpu_cores × 2) + effective_spindles

For a 4-core database with an SSD, that is around 9.

The honest answer is to measure. Start small, increase until the throughput stops improving, and stop there.

The total across all application instances matters. Ten instances with a pool of 20 is 200 connections, which may exceed the database’s limit.

Exhaustion

The symptom: a request that is fast in isolation becomes slow under load, and eventually times out.

The cause: every connection is in use, and the request is waiting.

The diagnosis: log the pool’s active and idle counts, and the wait time to acquire.

The fixes:

  • Increase the pool size, if the database can take it.
  • Reduce the query time, so a connection is held for less time.
  • Fix a leak, if the pool shrinks over time.
  • Add a read replica, so the read queries do not compete with the writes.

A pool exhaustion is often a symptom of a slow query, not of a small pool. A query that takes 5 seconds holds a connection for 5 seconds, and a hundred concurrent requests need a hundred connections.

Leaks

A connection that is not released shrinks the pool until it is exhausted.

// Leak: an error skips the release
const client = await pool.connect();
const result = await client.query('SELECT ...');  // throws
client.release();  // never reached

The fix is a finally block:

const client = await pool.connect();
try {
  return await client.query('SELECT ...');
} finally {
  client.release();
}

A transaction is the other common leak. A transaction that is not committed or rolled back holds the connection and its locks.

A pool that shrinks over hours is the signature of a leak, and the fix is to find the path that skips the release.

Timeouts

A connection timeout is how long to wait for a connection from the pool.

A query timeout is how long a query may run.

An idle timeout closes a connection that has been idle for too long, which the database may do anyway.

A statement timeout on the database side is the backstop, and it prevents a runaway query from holding a connection forever.

Set all of them. A missing timeout means a request can hang indefinitely, which is worse than a fast failure.

Serverless

A serverless function breaks the pool model.

Each instance opens its own pool, and a burst of traffic creates a burst of instances, each with its own connections. A hundred instances with a pool of 10 is a thousand connections, which exceeds the database’s limit.

The fixes:

  • An external pooler such as PgBouncer or a managed proxy, which multiplexes many client connections onto a few database connections.
  • A pool size of 1 per instance, and rely on the pooler.
  • A connection proxy from the database provider, such as RDS Proxy.

The pooler’s transaction mode is the common configuration: a client connection is bound to a database connection only for the duration of a transaction, which allows a high multiplexing ratio.

The limitation: a session-level feature such as a prepared statement or a temporary table does not work in transaction mode, because the client may be on a different database connection for the next transaction.

Monitoring a pool

The pool is one of the few components where a metric predicts a failure before it happens, and it is worth instrumenting for that reason alone. The metrics that matter are the active connection count, the idle count, the number of requests waiting for a connection, and the wait time to acquire. A rising wait time is the earliest signal that the pool is becoming a bottleneck, and it appears before the latency of the requests themselves rises, which gives time to act. The second signal is a pool that never returns to its idle baseline, which suggests a leak rather than a load spike. The third is the database’s own connection count, which shows whether the application’s pools together are approaching the server’s limit, and that is the number that causes an outage when it is exceeded.

Practical guidance

  • Use a pool for any non-trivial load.
  • Size it small, and measure.
  • Count the total across all instances.
  • Release in a finally block.
  • Set a connection, query, and statement timeout.
  • Log the pool’s active and idle counts.
  • Treat exhaustion as a slow-query symptom.
  • Use an external pooler for a serverless function.
  • Remember transaction mode breaks a session feature.

For the related guides, see database-indexing-explained and caching-strategies-explained. For the broader context, load-testing-basics and docker-basics-guide.

Quotable summary

Opening a database connection costs a TCP handshake, an authentication, and a session setup, so a pool reuses them and pays the cost once rather than per request. A pool has a maximum size and a request that cannot get a connection waits or fails, and a pool that is too small queues requests while a pool that is too large overwhelms the database, because a database with four cores cannot run a hundred queries in parallel. A connection must be returned to the pool in a finally block, or it leaks and the pool is exhausted, and a pool that shrinks over hours is the signature of a leak. A serverless function needs an external pooler, because each instance would otherwise open its own connections and a burst of instances would exhaust the database’s limit.

Frequently asked questions

What is a connection pool?
A cache of open database connections that are reused across requests, so the cost of opening a connection is paid once rather than per request.
How large should a connection pool be?
Smaller than you think. A common starting point is the number of CPU cores times two, plus the disk spindles, and it should be measured rather than guessed.
What happens when the pool is exhausted?
A request waits for a connection until a timeout, and then fails. The symptom is a slow request that eventually times out under load.
What is a connection leak?
A connection that is not returned to the pool, usually because an error path skips the release. The pool shrinks until it is exhausted.
Why is a connection pool a problem in a serverless function?
Each instance opens its own pool, so a burst of instances opens a burst of connections and exhausts the database's limit. An external pooler fixes it.
What is a pooler such as PgBouncer?
A proxy that sits between the application and the database and multiplexes many client connections onto a few database connections.