Connection Pooling for Databases

For four lessons you've been making the query cheaper — an index, a replica, a denormalized column, a materialized view. Now scale the app: you go from 2 servers to 50 to handle the traffic. Each server has a healthy connection pool of 20 (you learned that in Connection Pools: Reuse Beats Re-open in Container 1). And your database falls over.

Not because the queries are slow. Because 50 servers × 20 connections = 1,000 connections, and your database can serve maybe 100. The bottleneck was never the query. It was the connection the query rides in on.

Container 1 taught you the app-side pool — reuse connections instead of re-opening them, on one server. This lesson is the other half: what the database server can actually afford, why it's a shockingly small number, and the piece of infrastructure that lets thousands of clients share a few dozen real connections. It starts with one fact that surprises almost everyone.

Here's the fact. In Postgres, a client connection is not a lightweight socket or a thread. It's a whole operating-system process — fork()ed when you connect, with its own memory, its own slot in the OS scheduler, its own everything. I opened ten idle connections and measured them:

~14 megabytes each, held whether the connection is running a query or sitting completely idle. This is why connections are precious in a way that sockets aren't. A thousand idle HTTP keep-alive sockets cost you almost nothing. A thousand idle Postgres connections cost you a thousand processes and ~14 GB of RAM doing nothing — plus the scheduler and lock-table overhead of managing them all, which slows down every query, including the ones actually working.

(This is a design choice — Postgres uses process-per-connection for isolation and robustness; other engines use threads and pay a different price. But for you, on Postgres, the consequence is concrete: connections are expensive server resources, and there is a hard limit on them.)

That hard limit is max_connections, and past it Postgres doesn't slow down — it refuses. I set it low and opened connections one by one:

Every new client past the ceiling gets rejected outright — your app throws connection errors under exactly the load you most needed it to survive. And the naive fix — just raise max_connections to 10,000 — is a trap: that's 10,000 processes × ~14 MB ≈ 140 GB of RAM for idle connections alone, plus contention that degrades everyone. You cannot buy your way out with a bigger number.

And here's why your app pool (C1 L12) doesn't save you either. A pool on one server caps that server's connections beautifully. But you have fifty servers, each with its own pool, none of them aware of the others. Fifty well-behaved pools of 20 still demand 1,000 connections from a database that can serve 100. The app pool solves the client's problem. It does nothing about the server's ceiling. You need something that sees all the clients at once.

That something is an external connection pooler — PgBouncer is the classic one — and it sits between your fleet of app servers and the database. Thousands of clients connect to it (cheap: PgBouncer is not process-per-connection). It, in turn, keeps a small set of real connections to Postgres and hands them out on demand.

The magic is when it hands one back. In its best mode — transaction pooling — a client is given a real backend connection only for the length of one transaction (BEGIN … COMMIT), and the instant that transaction ends, the backend is reclaimed and lent to whoever's next.

Why that's transformative: at any given instant, only a tiny fraction of your thousands of clients are actually mid-transaction. Everyone else is thinking, rendering, waiting on the user. So you only need enough real connections to cover the ones transacting right now. I measured it — 100 concurrent clients through PgBouncer with a pool size of 5:

One hundred clients. Five real backend connections. Ninety-five clients waiting microseconds for their turn. The database sees a tiny, stable, safe connection count no matter how many app servers or clients exist. That is how you scale reads (and writes) past the connection wall — not by raising the ceiling, but by needing far fewer connections under it.

Transaction pooling sounds free. It isn't, and the price is subtle and important. Because a backend is reclaimed after every transaction, your next statement may run on a different physical backend than your last one. So anything that quietly assumes "I'm talking to the same connection the whole time" is now unsafe:

• a session SET (SET search_path, SET timezone, a SET LOCAL-that-should-persist)
• LISTEN / NOTIFY (they're bound to one backend)
• session-level advisory locks
• WITH HOLD cursors, plain temp tables

…all of these can silently break, because the backend that held your state got lent to somebody else. And here's the genuinely dangerous part, which I reproduced:

It passes every test on your laptop. At low traffic the pool keeps handing you the same idle backend, so session state appears to survive. Then under production load the two statements land on different backends and your flag vanishes — a heisenbug that only appears when you're busiest. Test under concurrency, or don't rely on session state in transaction mode.

There's one claim about transaction pooling that every article repeats: "transaction mode breaks prepared statements." For years it was true — a PREPARE created a statement on one backend, and a later EXECUTE on a different backend failed with "prepared statement does not exist."

So I tested it, on the current PgBouncer (1.25.2), the way you should test anything before you build on it:

They work. As of PgBouncer 1.21 (2023), the pooler transparently tracks prepared statements and re-prepares them on whichever backend a client lands on. The most-repeated warning about transaction pooling has been obsolete for two years — and if you'd taken it as gospel, you'd have avoided prepared statements (and their performance win) for no reason.

The staff-level habit isn't memorizing which features break. It's this: the field moves, so measure the version you're actually running instead of trusting a blog post from 2019. The list of what transaction pooling breaks has shrunk over time; check where it stands for your PgBouncer.

PgBouncer offers three pool modes, and they're one dial: how long does a client keep a real connection? Shorter means more multiplexing, but less of a stable session.

Transaction is the default sweet spot, and for good reason: it gives you enormous multiplexing, and the things it breaks are things a typical stateless web request doesn't use anyway (you rarely LISTEN or hold a session-level lock inside a single HTTP handler). Use session mode only when you genuinely need a stable session — some ORMs, some session-pinned features — and accept that you've given up most of the scaling.

And a note that saves confusion: you often run both. An app-side pool (C1 L12) to avoid re-opening TCP connections to PgBouncer, and PgBouncer in front of the database to multiplex the whole fleet onto a few backends. They solve different halves of the same problem.

Below is the whole thing, live. Start in direct mode and drag the clients past 100 — watch the database reject everyone over the ceiling, each a wasted 14 MB process. Switch to session pooling and see it barely help: a backend held per session means most clients just queue.

Then switch to transaction pooling and drag the clients to 500, to 1,000 — and watch the real backend count refuse to climb, sitting at a handful while the multiplexing ratio soars past 50:1. That flat line, no matter how many clients you throw at it, is the entire point of a database connection pooler.

"Connection pooling is the pool in my app." That's the client side (C1 L12). This lesson is the server side: the database runs a process per connection and caps at max_connections, and an external pooler multiplexes many clients onto few backends. You often need both.

"Just raise max_connections." Each connection is a ~14 MB process (measured). 10,000 of them is ~140 GB of idle RAM plus contention that slows every query. The ceiling is low for a real reason.

"An idle connection is basically free." Measured: 13.9 MB each, held whether or not it's doing anything.

"A bigger app pool fixes the database overload." A bigger app pool per server, across 50 servers, demands more connections from the database, not fewer. Without a pooler in front, adding app servers can exhaust the connection ceiling.

"Transaction pooling breaks prepared statements." Obsolete. True before PgBouncer 1.21; measured false on 1.25 (600/600 work). What still breaks: LISTEN/NOTIFY, session locks, held cursors, persistent SET.

"Session state works — I tested it." At low load the pool reuses one backend, so it looks like it works; under concurrency it lands on a different backend and vanishes. Test under load, or don't rely on it in transaction mode.

• ⭐⭐⭐ A database connection is a whole OS process, not a socket. Measured on Postgres: ~13.9 MB per idle connection. That's why connections are scarce and max_connections is low — 10,000 would be ~140 GB of idle RAM plus crippling contention.
• ⭐⭐ max_connections is a hard wall: past it, clients are rejected (too many clients already), not slowed. And your app-side pool (C1 L12) can't fix it — 50 servers × 20-connection pools still demand 1,000 connections the database can't serve.
• ⭐⭐⭐ An external pooler multiplexes: it lends a real backend only for one BEGIN…COMMIT. Measured: 100 clients served by 5 real backends (SHOW POOLS: cl_active=5, cl_waiting=95). The database's connection count stays tiny and stable no matter how many clients exist.
• ⭐ The bill: in transaction mode the backend isn't yours between transactions, so LISTEN/NOTIFY, session locks, held cursors and persistent SET break — and worst of all, session state looks fine at low load and vanishes under concurrency.
• ⭐ Check your version, not the blogs. "Transaction mode breaks prepared statements" has been false since PgBouncer 1.21 (measured: 600/600 work). The field moves; test the version you run.
• Three modes, one dial: session (~1:1, safe) · transaction (50:1+, the sweet spot) · statement (max, breaks multi-statement txns). Most web apps want transaction, and often run an app pool and PgBouncer.

Next: Query Optimization in Practice. You've made the query cheaper and the connection cheaper. Now you'll hunt the single most common way applications make a fast database slow — issuing a thousand tiny queries where one would do.