My LibraryBuy PDF
Part 5/Data and Databases

Connection Pooling, and Why It Bites

Every request to your API is timing out. The pager went off at 3:12am. You open the database dashboard braced for a fire: CPU pegged, disk melting, a lock storm rolling through. Instead the database looks asleep. CPU at 4%. Latency on the queries that do run is fine. The only thing in the Postgres log is the occasional FATAL: sorry, too many clients already.

The database is bored. Your app is on the floor.

That is a pool problem, and it is nasty precisely because it hides from the one dashboard you thought to check. The bottleneck is not the database. It is the thin layer of plumbing between your code and the database, the part nobody drew on the architecture diagram. This article is about that layer: what a connection actually costs, why you reuse them, how reuse goes wrong, and why the whole thing got ten times harder the moment everyone moved to serverless.

A connection is not free

Here is the thing that surprises people. Opening a fresh Postgres connection is not like calling a function. It is a small negotiation with several round trips, and on the server side it spawns an entire operating-system process.

Walk through what happens when your code says “connect”:

  1. TCP handshake. SYN, SYN-ACK, ACK. One full round trip before a single byte of anything useful.
  2. TLS handshake. If you are on the public internet or talking to a managed database (you are, and you should be), add another one or two round trips to set up encryption.
  3. Authentication. Modern Postgres uses SCRAM-SHA-256 by default, a challenge-response exchange that is itself a couple more round trips. The server proves it knows your password hash, you prove you know your password, nobody sends the password in the clear.
  4. A backend process is forked. This is the expensive part people forget. Postgres uses one OS process per connection. The postmaster forks a fresh backend, which allocates its own memory (a few megabytes of baseline, more once it starts sorting or hashing), and that process lives for as long as the connection does.
Without a pool: every query pays the tollTCP1 round tripTLS1 to 2 tripsSCRAM auth2 trips + forkyour SQLtens to hundreds of ms before any SQL runs, repeated for every requestWith a pool: pay once, then borrowconnect onceTCP + TLS + authborrowmicrosecondsyour SQLreturn× every query
Without a pool, every query pays the full connection toll first. With a pool, that toll is paid once and each query just borrows a warm connection.

Add it up. On a fast local network that whole dance might cost single-digit milliseconds. Over real network distance, or against a managed database an availability zone away, it is easily 50 to 200 milliseconds. Every request. Before your query does one useful thing.

Do that per request and you have built a service where the connection setup dwarfs the actual work. So you don’t. You open a handful of connections once, keep them open, and hand them out. That is a connection pool.

How a pool works

A pool is a small box of already-open connections plus a rule for sharing them. Three verbs:

  • Checkout. A request needs the database, so it asks the pool for a connection. If a free one exists, it gets it immediately. If not, it waits.
  • Use. The request runs its queries on that connection.
  • Release. The request is done, so it hands the connection back. The connection stays open, warm, ready for the next caller.
pool (max 4)conn 1 · in useconn 2 · in useconn 3 · idleconn 4 · idlerequestscheckout →← releasequeuewaiting…waiting…until a conn frees
A pool holds N warm connections. Requests check one out, run their queries, and release it back. When all N are busy, the next request waits in a queue.

In Node with node-postgres, the pool is a Pool object you create once and share across your whole process:

import { Pool } from 'pg';

// Create ONE pool for the lifetime of the process. Not per request.
const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 10,                      // at most 10 open connections
  idleTimeoutMillis: 30_000,    // close a connection idle for 30s
  connectionTimeoutMillis: 5000 // give up waiting for a free one after 5s
});

For a single query, the pool has a helper that checks out, runs, and releases for you in one call:

// pool.query() borrows a connection, runs the query, and returns it. No leak possible.
const { rows } = await pool.query(
  'SELECT id, email FROM users WHERE id = $1',
  [userId]
);

You only reach for an explicit connection when you need several statements to run on the same connection, which is exactly what a transaction requires. That is where the danger starts, so hold that thought.

Sizing: smaller than your gut says

Now the question everyone gets wrong: how big should the pool be?

The instinct is “more is better.” More connections, more parallelism, more throughput. It feels obvious. It is also wrong, and the reason is worth understanding because it is the same reason the whole system falls over under load.

Your database runs on a fixed number of CPU cores and a fixed number of disks. A machine with 4 cores can physically execute exactly 4 things at once. Give it a pool of 100 connections all trying to run queries, and it does not do 100 things in parallel. It does 4 things and time-slices the other 96, paying for context switches, cache misses, and lock contention on the way. Past the point where you have saturated the hardware, every extra connection makes the database slower, not faster.

Throughput vs pool sizeTPSsweet spot ≈ cores × 2queue in front: finedatabase thrashing: bad0pool size →
Throughput climbs with pool size until the database hardware is saturated, then falls as extra connections add context switching and lock contention instead of work.

There is a starting formula that has held up for years, originally from the Postgres community and popular via the HikariCP pool for Java:

connections ≈ (core_count × 2) + effective_number_of_disks

On a typical 4-core cloud instance backed by SSD, that lands you around 9 or 10. Not 100. Not 50. Around ten. That number surprises people every time.

So what happens to the eleventh request that arrives while all ten connections are busy? It waits. And here is the counterintuitive payoff: a queue in front of a small pool beats a big pool. Waiting a few milliseconds for a connection is cheap. Making the database thrash through 100 concurrent backends is not. The queue is a feature. It is the thing that keeps the database in the fast part of that curve.

Pool exhaustion: the outage from the top

Back to 3:12am. Here is the mechanism behind that bored database and those timeouts everywhere.

Your pool has 10 connections. A query starts running slow. Maybe someone shipped a query with a missing index and it now takes 8 seconds instead of 8 milliseconds. Maybe a report is holding a lock. Whatever the cause, that query is holding its connection the entire time it runs.

Requests keep arriving. Each one needs a connection. The slow queries pile up and hold their connections, free connections run out, and every new request joins the queue. The queue grows. Requests at the back wait past connectionTimeoutMillis and fail. From the outside, everything is timing out, including endpoints that have nothing to do with the slow query. The health check fails. The load balancer starts pulling instances. It looks like total system collapse.

And the database? Running 10 queries, mostly idle, wondering what the fuss is about.

pool (max 4)slow query · 8sbusybusybusy0 freequeue growsreq · waitingreq · waitingreq · waitingreq · waitingpast timeout504 timeout504 timeoutDatabase CPU: 4%. The bottleneck is the pool, not the database.no obvious error in the Postgres log, just timeouts in your app
One slow query holds its connection while requests pile into the queue. When they wait past the timeout they fail, so the symptom is app-side timeouts, not a database error.

That is the shape of it: one slow spot converts into a total outage because the pool is a shared, limited resource. This is why a single un-indexed query on a rarely-hit endpoint can take down your entire API.

The leak that never comes back

The slow-query version at least recovers when the query finishes. The nastier version does not recover at all: a leaked connection. You check a connection out and never release it. It is gone from the pool forever. Do that under load and the pool bleeds one connection per leak until there are none left, and then you are exhausted permanently until a restart.

The classic way to leak is an early return or a throw between checkout and release:

// BUG: if the balance check throws, client.release() never runs. Leaked forever.
async function charge(userId, amount) {
  const client = await pool.connect();

  const { rows } = await client.query(
    'SELECT balance FROM accounts WHERE id = $1',
    [userId]
  );

  if (rows[0].balance < amount) {
    throw new Error('insufficient funds'); // <-- jumps out, connection never returned
  }

  await client.query(
    'UPDATE accounts SET balance = balance - $1 WHERE id = $2',
    [amount, userId]
  );

  client.release();
}

Every insufficient-funds error permanently removes one connection from the pool. Ten of those and a ten-connection pool is dead. The endpoint that finally times out might be one that never touches charge at all, which is what makes this so hard to trace.

The fix is boring and non-negotiable. Release in a finally. A finally block runs whether the code succeeds, throws, or returns early:

async function charge(userId, amount) {
  const client = await pool.connect();
  try {
    const { rows } = await client.query(
      'SELECT balance FROM accounts WHERE id = $1',
      [userId]
    );
    if (rows[0].balance < amount) throw new Error('insufficient funds');
    await client.query(
      'UPDATE accounts SET balance = balance - $1 WHERE id = $2',
      [amount, userId]
    );
  } finally {
    client.release(); // runs on success, on throw, on early return. Always.
  }
}

A leaked transaction is the same bug wearing a hat. You run BEGIN, something throws before COMMIT or ROLLBACK, and now that connection is not just checked out, it is stuck holding an open transaction (idle in transaction), which can also hold locks that block other queries. Same fix: the BEGIN, the work, and the COMMIT/ROLLBACK all live inside a try/finally that releases the client.

Feel it: a pool under load

Sliders below drive a tiny simulator. Set the pool size, the request rate, and how long each query takes. Watch utilisation, the queue, and timeouts. There is a capacity ceiling hiding in here: roughly pool ÷ query-time. Nudge the request rate just under it and everything hums. Push it just over and the queue never drains, latency climbs, and requests start timing out. Find the peak, then break it.

interactivePool load simulator

Notice the ceiling is pool ÷ query-time, not the pool size alone. A pool of 10 running 200ms queries tops out near 50 requests a second no matter how much traffic you throw at it. The two ways to raise that ceiling are a bigger pool (until the database thrashes) or faster queries. Faster queries is almost always the better lever.

The serverless problem

Everything above assumes one long-lived process holding one shared pool. Serverless breaks that assumption, and it breaks it hard.

On a platform like Lambda or a serverless function runtime, each concurrent request can run in its own isolated instance. There is no shared process to hold a shared pool. If instance A opens a pool, instance B cannot see it. So each instance opens its own connections, and under load the platform might spin up hundreds or thousands of instances at once.

A thousand instances, each opening even a single connection, is a thousand connections against a database that accepts 100. You hit too many clients already almost instantly, and there is no pool size you can set on your side that fixes it, because the fan-out is happening above your pool.

function instances~10,000 connectionsfnfnfnfnfnfnfnfnfnfnpoolertransactionmodePostgresmax_connections100~20 real
Hundreds of serverless instances each want connections. A pooler in front collapses thousands of client connections into a small, safe set of real database connections.

The fix is to put a pooler in front of the database, a dedicated process whose whole job is to accept a huge number of client connections and multiplex them onto a small number of real database connections. The client connections are cheap because the pooler is not forking a Postgres backend for each one. The mainstream options, all evergreen and actively maintained as of mid-2026:

  • PgBouncer. The classic, tiny, battle-tested C pooler. Still the default choice for most people.
  • pgcat. A newer Rust pooler that adds load balancing and sharding on top of the same idea.
  • Supavisor. An Elixir pooler built by Supabase specifically for the serverless and edge case, designed to hold hundreds of thousands of client connections.

Transaction mode is the trick, and the trap

A pooler is only interesting if it can share a handful of backends among far more clients than there are backends. It does that with transaction-mode pooling.

In the default session mode, a client that connects gets a real backend for the entire duration of its session, and holds it even while it sits idle between queries. That is safe but barely better than connecting directly. In transaction mode, the pooler only assigns a real backend to a client for the length of a single transaction. The instant the transaction commits, that backend goes back into the shared set for someone else. A client that is idle between transactions holds nothing.

Session mode: one backend, one clientA · queryA · idle, still heldA · queryA · idle, still heldclients B and C wait for a whole backend of their ownTransaction mode: one backend, handed off at each commitA · txnB · txnC · txnA · txnB · txnC · txnA · txnno idle time wasted; three clients share one backend
In session mode one client owns a backend the whole time, idle gaps included. In transaction mode the backend is handed off at each commit, so a few backends serve many clients.

That packing is what lets one backend serve dozens of clients. It is also where the trap is. Because a connection you use for one query might be a totally different backend on your next query, anything that lives on the connection between transactions is unsafe in transaction mode:

  • Session variables. SET search_path, SET timezone, and friends set state on one backend that the next query may not touch.
  • LISTEN/NOTIFY. These are session-scoped and simply do not work.
  • Session-level advisory locks. You take the lock on one backend and can never reliably release it.
  • WITH HOLD cursors and other cross-transaction session state.
  • Prepared statements, historically. A prepared statement was created on one backend and vanishes when your next query lands on another.

The prepared-statement limitation used to be a real thorn, because most drivers and ORMs lean on prepared statements for both speed and safety. That has largely been fixed. PgBouncer added protocol-level prepared-statement support in transaction mode in version 1.21, and recent releases enable it by default via max_prepared_statements. Supavisor and pgcat handle it too. It is no longer the dealbreaker it was a couple of years ago, but you still cannot rely on any other session state surviving between queries.

In practice, pointing your app at a transaction-mode pooler is a connection-string change plus keeping your own pool small, because the pooler is doing the real pooling now:

# App connects to the pooler on 6543, not straight to Postgres on 5432.
DATABASE_URL="postgres://user:[email protected]:6543/app"
// Behind an external pooler, your in-process pool should be tiny.
const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 2, // the pooler multiplexes; you don't need a big local pool
});

When you cannot hold a socket at all

Edge runtimes raise the stakes again. Some of them cannot open a raw TCP socket to Postgres at all, so even a pooler is out of reach over the normal wire protocol. The answer there is to stop speaking the TCP protocol and speak HTTP instead.

An HTTP data proxy sits in front of the database and accepts queries over ordinary fetch requests. It holds the real connection pool itself, on the server side, near the database. Your edge function just makes a POST with some SQL and gets JSON back, no long-lived socket required. The Neon serverless driver (HTTP for one-shot queries, WebSockets when you need a real transaction), Prisma Accelerate, and Cloudflare Hyperdrive all work this way. Hyperdrive additionally runs a transaction-mode pool close to your database and can cache read queries at the edge.

The trade is latency shape. A single query over HTTP is roughly three round trips instead of the eight-ish a full TCP-plus-TLS-plus-auth setup costs, so for the one-shot queries typical of edge handlers it is often faster, not slower. For a chatty multi-statement transaction, HTTP one-shot mode does not fit, which is why these drivers fall back to WebSockets for the interactive case.

Idle timeouts and dead connections

One last failure mode, because it produces confusing errors. Connections do not live forever, and sometimes they die without telling anyone.

A connection that sits idle long enough gets closed. Your own pool does this on purpose via idleTimeoutMillis (10 seconds by default in node-postgres, which is aggressive; 30 seconds is a calmer choice) to avoid holding backends you are not using. Fine, expected.

The ugly one is a connection killed underneath you. A firewall, a NAT gateway, a load balancer, or the database itself can drop a TCP connection that has been idle for a few minutes, and it does not always send a clean close. Your pool still thinks the connection is good. It hands it to the next request, the request tries to use a dead socket, and you get Connection terminated unexpectedly or ECONNRESET out of nowhere, on a query that is perfectly correct.

Defenses, strongest first:

  • Recycle connections proactively. Set a maximum lifetime or a maximum number of uses (maxLifetimeSeconds, maxUses) so the pool retires connections before some middlebox decides to kill them.
  • Keep them warm. TCP keepalives stop an idle connection from looking abandoned to the network in between.
  • Handle the error and retry. Attach pool.on('error', ...) so a dying idle connection does not crash the process, and make first-query-after-idle failures retryable.

What to actually do

No tricks, just the short list that keeps you out of the 3am version of this:

  • Create one pool per process at startup, never inside a handler. Behind an external pooler, keep that local pool tiny.
  • Size the pool small, near cores × 2, and remember max_connections is shared across every instance you run.
  • Prefer pool.query() for single statements. When you check out a client for a transaction, release it in a finally, every time.
  • Set connectionTimeoutMillis so a starved request fails fast instead of hanging.
  • On serverless or edge, put a transaction-mode pooler or an HTTP data proxy in front, and know that transaction mode voids session state.
  • Add statement_timeout and idle_in_transaction_session_timeout server-side as backstops, and monitor how many requests are waiting on the pool. That waiting count is the number that goes up before everything else goes down.

The database being idle while your service dies is the signature of this whole class of bug. Once you have seen it, you will recognise it in four seconds flat, and you will know to look at the boring plumbing instead of the database. For the wider picture of how one Node process juggles all these requests on a single thread, that is its own story.

Summary

  • Opening a database connection is expensive: TCP, TLS, and a SCRAM auth exchange, plus Postgres forking a whole backend process per connection. You reuse connections instead of reopening them.
  • A pool keeps N warm connections and lends them out: checkout, use, release. pool.query() does all three for you; an explicit client for a transaction must be released in a finally.
  • Small pools win. The database only runs as many queries at once as it has cores, so throughput peaks near cores × 2 and falls beyond it. A queue in front of a small pool beats a big pool that thrashes.
  • Exhaustion is the classic outage: one slow query or one leaked connection starves the pool, and the symptom is timeouts everywhere while the database sits near-idle, with no obvious database error.
  • Serverless fans out to thousands of instances, each wanting connections, which blows past max_connections. The fix is an external pooler (PgBouncer, pgcat, Supavisor) in transaction mode, or an HTTP data proxy for edge runtimes that cannot hold a socket.
  • Transaction mode multiplexes many clients onto few backends but voids session state: SET, LISTEN/NOTIFY, session advisory locks, and (historically) prepared statements. Modern poolers handle prepared statements again; the rest you still cannot rely on.
  • Connections also die quietly from idle timeouts and middleboxes. Recycle them on a lifetime, keep them warm, handle the error, and set statement_timeout plus idle_in_transaction_session_timeout as server-side backstops.