Connection pooling, and why it matters sooner than you think

Opening a database connection is expensive. Not "shouldn't do it in a loop" expensive — measurably, dozens of times the cost of the query itself. And there is a hard ceiling on how many can exist at once. Connection pooling is the answer to both, and it starts mattering at far lower traffic than people expect.

Why a connection is expensive

Opening one is not "send a packet". It is:

• a TCP handshake, and a TLS handshake on top if you use SSL (you should),
• authentication — often a password round trip,
• and on the server, a whole new process. PostgreSQL forks an OS process per connection, with its own memory.

Measured, on a local database with no network in the way:

fresh connect + query:  5.70 ms
reused connection:       0.186 ms

About 31× slower, and that is the best case — same machine, no TLS, no network latency. Add a real network and a TLS handshake and the gap is much wider. If every web request opens and closes a connection, you have added tens of milliseconds of pure overhead to every request, and most of your database's work is forking processes rather than answering queries.

Why there is a hard ceiling

SHOW max_connections;          -- 100 by default

PostgreSQL refuses connection 101. Not slows down — refuses:

FATAL:  sorry, too many clients already

I proved it by opening connections in a loop: it accepted 97 (three are reserved for superusers), then failed hard. And this is not a limit you should just raise. Each connection costs memory, and because each is a process, a few hundred of them create real scheduling and memory-contention overhead on the server. The PostgreSQL wisdom is that a busy server is usually happiest with a connection count in the low hundreds at most, often fewer than people guess.

So you have a squeeze: connections are expensive to make, limited in number, and yet a web app under load wants to run many requests at once. A pool resolves it.

What a pool does

A pool keeps a set of open connections and lends them out:

Request 1 ─┐                    ┌─ conn A ─┐
Request 2 ─┼─→ [ the pool ] ─→ ├─ conn B ─┼─→ PostgreSQL
Request 3 ─┘   (say 10 conns)   └─ conn C ─┘   (only 10 processes)

A request borrows a connection, runs its queries, and returns it — it is not closed, so the next request skips the whole handshake. If every connection is busy, the next request waits for one to free up rather than opening an eleventh.

That waiting is a feature. Ten connections running flat out will clear a queue faster than a hundred connections all fighting for the same CPUs and disk. A pool converts "too many connections" into "a short wait", which the database handles far better.

Application-side pools

The pool lives inside your process.

Node — the pool is the normal API, which is why the last lesson insisted on Pool:

import { Pool } from 'pg';
const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 10,                         // connections in this pool
  idleTimeoutMillis: 30000,        // close a connection idle this long
  connectionTimeoutMillis: 5000,   // wait this long for a free one, then error
});

Python — psycopg has psycopg_pool:

from psycopg_pool import ConnectionPool

pool = ConnectionPool(DSN, min_size=2, max_size=10)

with pool.connection() as conn:            # borrow, and return on exit
    conn.execute("SELECT 1")

min_size keeps a few connections always warm; max_size is the ceiling. The with block returns the connection to the pool — it does not close it.

Sizing: smaller than you think. A common starting formula is roughly ((core count × 2) + effective spindle count) — for a small app, often just 5 to 10. The instinct to set it to 100 is wrong: it recreates the exact problem the pool exists to prevent.

The multiplication trap, which is the whole point of the lesson

Here is what catches people, and it is arithmetic, not theory.

You set max: 10 and feel safe. Then you deploy. In production you run:

• 4 application instances (containers/pods), each with
• 2 worker processes, each with a pool of
• 10 connections.

4 × 2 × 10 = 80 connections

Add a second service that also talks to the database, a few background workers, and a couple of psql sessions from an engineer debugging, and you sail past 100. Your pods start throwing too many clients — under load, in production, not on your laptop where there was one pool of 10.

Your real connection budget is per pool, multiplied by every pool that exists. Write that multiplication down before you set max. The database's max_connections must exceed the sum of every pool across every instance, every worker, and every other service — with headroom.

This is the single most common connection incident, and it is entirely predictable from the multiplication above.

Server-side poolers, for when app pools are not enough

When you have many instances — or serverless functions, where each invocation is its own process and app-side pools cannot be shared — the app-side pool stops being able to coordinate. The answer is a pooler in front of the database that every client connects to:

many clients → [ PgBouncer ] → few real PostgreSQL connections

PgBouncer is the standard. Thousands of clients connect to it; it multiplexes them onto a small number of real connections. It has three modes, and the difference matters:

• Session pooling — a client holds a real connection for its whole session. Safe, least benefit.
• Transaction pooling — a real connection is assigned only for the duration of a transaction, then returned. The common choice, and it gives the big multiplexing win.
• Statement pooling — per statement. Most aggressive, most restrictive.

The catch with transaction pooling, which you must know before enabling it: anything that spans transactions on one connection breaks. Session-level SET, LISTEN/NOTIFY, advisory locks, WITH HOLD cursors, and server-side prepared statements can all land on a different backend next transaction. Many drivers need prepared statements disabled to work through it. It is a real behaviour change, not a drop-in.

Serverless especially needs this. A platform that spins up 500 concurrent function instances will open 500 connections straight into too many clients without a pooler between them; managed options (PgBouncer, or provider-specific poolers) exist precisely for this.

Health, timeouts, and the leak that will find you

Whatever the pool, three things keep it healthy.

A borrow timeout. connectionTimeoutMillis / pool.connection(timeout=...). If the pool is exhausted, a request should fail fast with an error you can see — not hang forever, which looks like a total outage with no message.

Recycle connections. A connection open for days can be silently dead (a network blip, a database failover). Pools validate on borrow and/or set a max lifetime, so a stale connection is quietly replaced instead of handed to a request that then errors.

The leak. This is the one that actually happens: a borrowed connection that is never returned. In Node, a missing client.release() in an error path — which is why the last lesson put it in finally. In Python, not using the with block. Each leak permanently shrinks the pool by one; enough of them and every request hangs on the borrow timeout. A pool that slowly stops working over hours is almost always a leak, and the fix is to guarantee the return with with / finally, never a bare borrow.

A decision guide

Start with the driver's pool. Reach for PgBouncer when the multiplication above threatens max_connections, or when you go serverless. Do not add it before you need it — it is another component to run, and in transaction mode it changes behaviour.

Check your work

Why a fresh connection is expensive. TCP plus a TLS handshake, authentication, and a new server process — measured at about 31× a reused connection, at best.

What PostgreSQL does at the connection limit. Refuses with FATAL: sorry, too many clients already — it does not slow down.

Why raising max_connections is not the fix. Each connection is a process with memory and scheduling cost; hundreds contend and slow the server.

What a pool does with a request when all connections are busy. Makes it wait for one, rather than opening another.

Why the waiting is good. Ten busy connections clear a queue faster than a hundred contending ones.

How to size a pool. Small — often 5 to 10; roughly cores × 2 + spindles.

The multiplication trap. Real connections = pool size × workers × instances × services, and it must stay under max_connections.

Why an app-side pool is not enough for serverless. Each function instance is its own process, so pools cannot be shared; hundreds of instances open hundreds of connections.

What PgBouncer does. Multiplexes many client connections onto a few real ones.

The three PgBouncer modes. Session, transaction (the usual), statement.

What transaction pooling breaks. Anything spanning transactions on one connection — session SET, LISTEN/NOTIFY, advisory locks, some prepared statements.

The three things that keep a pool healthy. A borrow timeout, connection recycling, and no leaks.

What a slow pool death usually is. A connection leak — a borrow that is never returned.

Practice

1. Time 30 fresh connect-and-query cycles against 30 queries on one reused connection. Compute the ratio.
2. Open connections in a loop until PostgreSQL refuses. Note the number and the exact error.
3. Read max_connections and superuser_reserved_connections. Explain why you got fewer than max_connections.
4. Set up a pool of 10 (Node or Python) and run 100 sequential queries. Confirm no new connections open, watching pg_stat_activity.
5. Set the pool to 2 and fire 5 concurrent queries. Watch some wait.
6. Set a borrow timeout of 1s on that pool and fire 10 concurrent slow queries. Confirm the extras fail fast rather than hang.
7. Leak a connection deliberately (borrow without returning) in a loop and watch the pool stop serving.
8. Write down your production multiplication: instances × workers × pool size × services. Compare with max_connections.
9. Count current connections per application with SELECT application_name, count(*) FROM pg_stat_activity GROUP BY 1.
10. Install PgBouncer locally, point it at your database in transaction mode, and connect through it.
11. Through PgBouncer in transaction mode, run a session-level SET in one transaction and read it back in the next. Observe that it did not persist.
12. Decide, for a project you know, whether it needs a server-side pooler yet, and justify the answer with the multiplication.

Official documentation

• PostgreSQL — max_connections — The setting, and the reserved connections.
• psycopg_pool — Connection pools — min_size, max_size, and the connection() context manager.
• node-postgres — Pooling — max, idleTimeoutMillis, connectionTimeoutMillis.
• PgBouncer documentation — The pooling modes and every setting.
• PostgreSQL wiki — Number of database connections — The definitive argument for why fewer connections is faster, with the sizing reasoning.

Next: what an ORM does for you, and what it hides.