Connection Pooling

What is a connection pool?

A connection pool is a cache of open database connections that the application borrows from and returns to. Instead of opening a fresh connection for every query, a request checks one out, runs its statements and gives it back. The next request reuses the same socket, already authenticated and ready.

The reason this matters is that a database connection is not cheap. On a local test database it may feel instant, but in production every new connection pays a fixed setup cost before it does any useful work. A pool pays that cost once per connection instead of once per request, which is why it is the first piece of infrastructure almost every backend adds.

The pool is also a safety mechanism. Because it has a hard maximum, it cannot accidentally open ten thousand connections during a traffic spike. Callers that arrive when the pool is full wait in a queue, which is slow but survivable, rather than overwhelming the database, which is not.

A pool in a few lines

In Node, the pg driver ships a pool that is ready to use. Creating one is a single constructor call, and every query you run through it borrows and returns a connection automatically.

import { Pool } from "pg";

export const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 10,
  idleTimeoutMillis: 30_000,
  connectionTimeoutMillis: 5_000,
});

const { rows } = await pool.query("SELECT id, email FROM users WHERE id = $1", [id]);

That is the whole idea. The pool opens connections lazily as demand arrives, keeps them open between queries and closes them when they have been idle too long. The application never sees a socket; it sees a query method.

The same pattern exists in every ecosystem. Java has HikariCP, Python has SQLAlchemy’s pool and asyncpg’s pool, Go has database/sql with SetMaxOpenConns, and every ORM wraps one of these. The names differ, but the settings are the same handful: a maximum, a minimum, an idle timeout and a checkout timeout.

Why connections are expensive

The cost is a stack of steps, each of which involves the network or the database server doing real work.

• TCP handshake — a round trip to establish the socket. On a remote database with a 30 ms RTT, that alone is 30 ms.
• TLS negotiation — if the connection is encrypted, several more round trips to agree on keys. Another 50 to 100 ms is common.
• Authentication — the client proves its identity, often with a password hash that the server must compute. SCRAM authentication deliberately does expensive work.
• Backend process — this is the Postgres-specific cost. Postgres forks a new operating-system process for every connection, each with its own memory. MySQL uses a thread, which is lighter but still not free.
• Session setup — search paths, time zone, application name and other settings must be applied.

Add it up and a new connection can cost tens of milliseconds before the first query. If a page issues ten queries, opening a connection per query would dominate the request. Worse, the process-per-connection model means idle connections still consume memory, so a few hundred of them can genuinely hurt the server.

A pool turns all of that into a one-time cost. The connection is created once, used for thousands of queries and closed when the pool decides it is too old or too idle.

The lifecycle of a pooled connection

Every checkout follows the same four steps, and understanding them explains almost every pool behaviour you will debug.

Acquire. The caller asks the pool for a connection. If one is idle, it is handed over immediately. If all are busy but the pool is below max, a new one is opened. If the pool is at max, the caller waits in a FIFO queue until something is released. That wait is the signal that the pool is saturated.

Use. The caller runs one or more queries on the connection. This is where the connection is actually earning its keep. It is also where mistakes happen: a transaction left open, a client never released, a query with no timeout.

Release. The caller returns the connection to the pool. This must happen in a finally block, because an exception between acquire and release leaks the connection permanently. A leaked connection is invisible until the pool drains and every request starts timing out.

Reap. Connections that sit idle longer than idleTimeoutMillis, or that exceed a maximum lifetime, are closed. This keeps the pool from hoarding resources the database could use elsewhere, and it is how stale connections from before a restart get retired.

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

Many drivers let you skip the explicit checkout for simple queries — pool.query() acquires and releases for you — which removes the most common source of leaks. Use the explicit form only when you need several statements on the same connection.

Checkout, query, release, safely

The three-line pattern above is the shape of every safe pool interaction, but production code needs a little more care around the edges.

Wrap the whole checkout in a helper so no caller can forget the release. The helper owns the try/finally and the error handling, and the rest of the codebase just awaits a function.

export async function withClient<T>(
  fn: (client: PoolClient) => Promise<T>,
): Promise<T> {
  const client = await pool.connect();
  try {
    return await fn(client);
  } finally {
    client.release();
  }
}

await withClient((client) =>
  client.query("UPDATE jobs SET status = 'done' WHERE id = $1", [id]),
);

Consider what happens when the connection itself is broken. A query can fail because the SQL is wrong, which is the caller’s problem, or because the socket died, which is the pool’s problem. The second case deserves a retry, but only if the operation is safe to repeat. A SELECT is; an INSERT without an idempotency key is not.

async function queryWithRetry(text: string, params: unknown[], retries = 2) {
  for (let attempt = 0; ; attempt++) {
    try {
      return await pool.query(text, params);
    } catch (err) {
      const isConnectionError = (err as { code?: string }).code === "ECONNRESET";
      if (!isConnectionError || attempt >= retries) throw err;
    }
  }
}

Finally, set a statement timeout on the connection so a single runaway query cannot hold a pool slot forever. On Postgres, statement_timeout cancels the query server-side; without it, the client waits as long as the database does.

Sizing the pool

The temptation is to set max high so nothing ever waits. This is the opposite of what you want. A database has a limited number of CPU cores and a limited amount of memory, and it cannot run more queries in parallel than it has capacity for. Extra connections do not add throughput; they add context switching, lock contention and memory pressure.

The classic rule of thumb comes from the PostgreSQL wiki:

connections = (cores × 2) + effective_spindle_count

For a four-core database with SSD storage, that is roughly eight to ten connections. It sounds alarmingly small, and it is correct: a well-indexed query completes in a millisecond or two, so a handful of connections can serve thousands of requests per second. The formula is a starting point, not a law — measure and adjust.

The second half of the calculation is the one people forget. Each application instance runs its own pool, so the database budget must be divided by the instance count:

pool max per instance = database budget / number of instances

Ten instances with a pool of twenty ask the database for two hundred connections, which is almost certainly past max_connections. Either reduce the per-instance pool or put a pooler in front.

Finally, check the database’s own limits. Postgres defaults max_connections to 100, and reserved connections for superusers and replication reduce what is actually available. Ask for more than that and you get errors, not graceful degradation.

The pool-per-instance fan-out problem

This is the single most common connection failure in modern deployments, and it happens gradually.

A service starts on one instance with a pool of twenty. It works. Traffic grows, so the service scales to five instances — and now the database sees one hundred connections, exactly at the limit. Scale to ten and it is two hundred, well over. Nothing in the application changed; the fan-out did.

The pattern repeats in Kubernetes, in serverless, and anywhere processes multiply. A pool bounds connections per process, not per system. The fix is either a small pool per instance sized to the fleet, or a server-side pooler that presents one small pool to the database regardless of how many clients connect.

10 instances × pool max 20 = 200 database connections
                ↓
     database max_connections = 100
                ↓
        "too many clients already"

A worked sizing example

Numbers make the trade-offs concrete. Suppose the database is a four-core managed instance with SSD storage and the default max_connections of 100, and the service runs on eight application instances.

The rule of thumb gives a budget of about ten connections that the database can genuinely use in parallel. Spread over eight instances, that is a pool of one or two per instance — far smaller than the ten people usually set, and often correct for a fast, well-indexed workload. If that feels too tight, the answer is not to raise the pool; it is to put a pooler in front so the eight small pools share a controlled set of backends.

database budget      ≈ 10 connections
app instances        =  8
pool max per instance = 10 / 8 ≈ 1

too small to be useful → add PgBouncer

PgBouncer default_pool_size = 10
app pool max (per instance) = 5   # clients may wait; backends stay bounded

The key insight is that the application pool and the database budget are different numbers. The app pool controls how many requests each instance can run at once; the pooler controls how many server connections actually exist. Setting the app pool a little larger than the per-instance share lets an instance burst while the pooler keeps the database safe.

Always leave headroom. Managed databases reserve some connections for administration and replication, and a failover briefly needs more. Targeting 70 to 80 percent of max_connections leaves room for migrations, monitoring and a bad deploy.

Keeping connections healthy

Long-lived connections can go stale. A database restart, a failover, a network partition or a firewall idle timeout can kill the socket without the application noticing. The next query on that connection fails, and without handling it the failure is confusing.

Three settings manage this:

• idleTimeoutMillis closes connections that have been unused for a while. Short values keep the server tidy but risk re-opening connections during quiet periods; thirty seconds is a common balance.
• A maximum lifetime retires connections after a fixed age, regardless of use. This spreads reconnections out instead of letting every connection die at once during a failover.
• Validation runs a cheap check, such as SELECT 1, before handing a connection out, so a dead socket is discarded rather than passed to a caller.

Even with all three, handle connection errors explicitly. An idle client that errors must be removed from the pool, and the driver usually emits an error event for exactly this:

pool.on("error", (err) => {
  console.error("unexpected idle client error", err);
});

Ignoring that event turns a recoverable blip into an unhandled exception that can take the process down.

Transactions need one connection

A transaction is bound to a single connection. Every BEGIN, statement and COMMIT must run on the same client, because the transaction state lives in that backend process. This is where pooling and transactions interact, and where naive code breaks.

The failure mode is subtle: if you run BEGIN through pool.query(), the pool may hand you a different connection for the next statement, and the COMMIT will either fail or commit nothing. The rule is simple — check out a client for the whole transaction and release it only after the commit or rollback.

const client = await pool.connect();
try {
  await client.query("BEGIN");
  await client.query("UPDATE accounts SET balance_cents = balance_cents - $1 WHERE id = $2", [100, from]);
  await client.query("UPDATE accounts SET balance_cents = balance_cents + $1 WHERE id = $2", [100, to]);
  await client.query("COMMIT");
} catch (err) {
  await client.query("ROLLBACK");
  throw err;
} finally {
  client.release();
}

Because a transaction holds a connection for its whole duration, long transactions shrink the effective pool for everyone else. Keep them short, avoid network calls inside them, and set a statement_timeout so a runaway query cannot pin a connection indefinitely.

Prepared statements and the pool

Prepared statements are a performance feature: the database parses and plans a query once, then reuses the plan. They are also where pooling gets subtle, because a prepared statement lives on a specific backend connection.

With an in-process pool this is mostly fine. Drivers such as pg prepare a statement on whichever connection is currently checked out, and the plan is reused only when that same connection serves the query again. Nothing breaks, but the benefit is uneven and the driver must manage a growing cache of named statements.

The trouble starts when you combine named prepared statements with a transaction-mode pooler. The pooler may send your query to a different backend than the one that prepared the statement, so the server does not recognise the name and returns an error. This is the single most common PgBouncer surprise.

The fixes are straightforward:

• Disable server-side prepared statements in the driver when using transaction pooling, and let it send unnamed statements. Many drivers have a flag for exactly this.
• Or use session pooling, which keeps a client on one backend and makes prepared statements safe again.
• Or set max_prepared_statements on modern PgBouncer, which proxies the prepare protocol correctly.

The general principle is that anything stored on the server session is fragile under transaction pooling. Prepared statements, SET variables, temporary tables and advisory locks all belong to a connection, and a pooler is free to give you a different one next time.

Serverless and connection exhaustion

Serverless functions are the worst case for connection pooling. Each invocation may run in a fresh, short-lived container with no shared memory, so it cannot reuse a pool built by another invocation. Under load, hundreds of concurrent functions each open a connection, and the database sees a connection storm it cannot survive.

The answer is a server-side pooler between the functions and the database. PgBouncer, or a managed equivalent such as RDS Proxy or a provider’s built-in pooler, keeps a small set of real connections and multiplexes the many short-lived client connections onto them.

1000 concurrent functions ──► PgBouncer ──► 20 Postgres connections
        (clients)             (pooler)         (backends)

A pool inside a function is still worth having, but it must be tiny — often a single connection — and configured to close quickly, because a container that keeps connections open while idle is wasting the database’s budget. The pooler is what makes the numbers work.

PgBouncer and transaction pooling

PgBouncer is the standard external pooler for Postgres. It speaks the Postgres wire protocol, so applications connect to it exactly as they would to the database. It has three pooling modes, and the choice has real consequences.

Session pooling assigns a server connection for the client’s entire session. It is the most compatible mode — SET, LISTEN, advisory locks and prepared statements all behave normally — but it multiplexes the least, because a client holds its connection even while idle.

Transaction pooling assigns a server connection only for the duration of a transaction and returns it to the pool at commit. A thousand clients can share twenty backends, which is why it is the default choice for web applications. The trade-off is that session state does not survive between transactions: a SET may land on a different backend next time, advisory locks held across statements break, and server-side prepared statements can collide.

Statement pooling returns the connection after every statement. It multiplexes the most and is the most restrictive; multi-statement transactions are not allowed. It is rarely what you want.

[databases]
shop = host=127.0.0.1 port=5432 dbname=shop

[pgbouncer]
pool_mode = transaction
default_pool_size = 20
max_client_conn = 1000
server_idle_timeout = 60

If you use transaction mode, audit your ORM and your queries. Use SET LOCAL inside a transaction instead of SET, avoid holding advisory locks across statements, and configure the driver to disable server-side prepared statements or use unnamed ones.

Monitoring pool health

A pool has four numbers worth watching, and together they tell you whether it is correctly sized.

• Wait time for a connection. The most direct signal of saturation. If callers regularly wait, the pool is too small for the load, or something is holding connections too long.
• Active versus idle connections. A pool that is always at max with all connections active is undersized. A pool that is mostly idle is oversized and wasting the database’s memory.
• Total versus maximum. How close the pool is to its ceiling. Consistently near max means the next spike will cause queueing.
• Errors and timeouts. Connection failures, validation failures and connectionTimeoutMillis expiries. A rising count points at the network, the database or a stale-connection problem.

On the database side, pg_stat_activity shows every connection and its state, which is the fastest way to see whether idle connections are piling up. Combine the two views: application metrics tell you how the pool is behaving, and database metrics tell you what it is doing to the server.

SELECT state, count(*)
FROM pg_stat_activity
WHERE datname = current_database()
GROUP BY state
ORDER BY count(*) DESC;

A healthy application pool shows a small number of active connections and a few idle ones. A growing pile of idle in transaction rows is the dangerous pattern: those connections are checked out but doing nothing, often because a transaction was opened and never committed. They hold pool slots and, on Postgres, can block vacuum. Treat a rising count of them as a bug, not a tuning problem.

ORM pools and the pooler

ORMs do not remove the need for pooling; they hide it. Prisma, TypeORM, Drizzle and Knex all maintain their own pool and expose a connectionLimit or pool option. That is convenient, but it means the same sizing arithmetic applies, and the same fan-out problem exists.

The mistake is running an ORM pool and a pooler without coordinating them. The ORM pool decides how many connections one instance wants; the pooler decides how many the database allows. If the ORM pool is large and the pooler’s default_pool_size is small, the ORM will hold connections the pooler cannot serve, and requests will queue at the pooler instead of the ORM. Set both deliberately, and prefer a modest ORM pool behind a pooler over a large one without.

export const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 10,
  idleTimeoutMillis: 30_000,
  connectionTimeoutMillis: 5_000,
});

A pool is not a cache

It is worth naming the difference, because the two are often confused. A cache stores results so you can avoid running a query. A pool stores connections so you can run queries cheaply. Adding a pool does nothing to reduce the number of queries; it only makes each one cheaper to start.

If a page runs twenty queries, a pool makes those twenty queries fast, but it does not make them disappear. The next layer of optimisation is caching the expensive results, which the Caching guide covers. The two combine well: a pool keeps the remaining queries cheap while a cache removes the ones you can avoid entirely.

The distinction also explains a common disappointment. Teams add a pool, see latency drop, then wonder why throughput is unchanged under heavy load. The pool removed the connection overhead, but the database is still doing all the work. Only a cache, a better index or fewer queries changes that.

Testing with a pool

Tests should exercise the same pooling path as production, because the bugs you care about — a leaked client, a transaction on the wrong connection — only appear through the pool.

Use a single shared pool for the test suite and close it once at the end. Opening a pool per test file is slow and can trip the database’s connection limit when tests run in parallel.

import { afterAll } from "vitest";
import { pool } from "../src/db.js";

afterAll(async () => {
  await pool.end();
});

test("findUser returns null for a missing id", async () => {
  const { rows } = await pool.query("SELECT * FROM users WHERE id = $1", ["nope"]);
  expect(rows).toHaveLength(0);
});

Point the tests at a throwaway database or a transaction that rolls back, and assert on pool behaviour where it matters: a test that checks pool.totalCount and pool.idleCount before and after an operation will catch a leaked client that a normal assertion would miss. On Postgres, pg_stat_activity can confirm the connection count returned to baseline.

Best practices

• Always use a pool; never open a connection per request.
• Size the pool from the database’s capacity, then divide by the number of instances.
• Set a connectionTimeoutMillis so callers fail fast instead of hanging.
• Set an idle timeout and a maximum lifetime so stale connections are retired.
• Release connections in a finally block, and handle the pool’s error event.
• Hold one connection for a whole transaction and keep transactions short.
• Put a pooler in front of Postgres once instances multiply or functions are serverless.
• Use transaction pooling for web apps and audit session-scoped features.
• Watch wait time, active versus idle counts and timeouts, not just query latency.
• Coordinate the ORM pool size with the pooler’s pool size.

Common mistakes

• Creating a Client per request instead of using a pool.
• Setting max to hundreds and calling it tuning.
• Forgetting to release a client and slowly draining the pool.
• Running BEGIN and COMMIT through pool.query() on different connections.
• Holding a transaction open across an HTTP call or user interaction.
• Leaving connectionTimeoutMillis at zero so requests wait forever.
• Ignoring the pool’s error event and crashing on a dead idle client.
• Running an in-process pool on serverless functions with no pooler in front.
• Assuming PgBouncer transaction mode supports prepared statements and session state.
• Watching query latency while pool wait time climbs unnoticed.

Where to go next

Pooling is inseparable from the database it serves, so the PostgreSQL guide covers max_connections, PgBouncer and the process-per-connection model in depth. Before tuning the pool, check whether the query needs to run at all — the Caching guide shows how to remove load at the source. If you run workers, Batch Processing explains how to keep a fleet of them from exhausting the database, and Node.js is worth revisiting for how the event loop and asynchronous I/O interact with a pool.