Here's a scene that plays out in production more often than anyone likes to admit.
Your app has been running fine for months. Then one day, traffic goes up i.e. a marketing push, a viral post, doesn't matter what. And suddenly your database, the same database that was handling everything fine yesterday, starts choking. CPU on the DB server shoots past 500%. Requests start timing out. Someone on the team says "maybe we need a bigger database."
Nine times out of ten, that's not the problem.
The problem is connections.
What a connection really costs
Most people think of a "database connection" as something abstract. A line in a config file, a string with a username and password in it. It isn't abstract at all. It's real, physical work.
When your app connects to Postgres, two things happen:
- A TCP socket opens, normal networking stuff.
- Postgres spins up a brand new operating system process just to handle that one connection.
Not a thread. A full process, with its own memory, tracked by the OS scheduler like any other program running on the machine.
This has been true since Postgres was born in 1986, and it's still true today. Every single connection is its own little process living on the server.
Why does that matter?
Because creating a process isn't free. The OS has to carve out memory for it, set up bookkeeping, hand it CPU time. Do that once, no big deal. Do it a thousand times a second, and you're basically asking your database server to spend all its energy on paperwork instead of actual work.
The two mistakes everyone makes without realizing it
There are two patterns people fall into without ever deciding to. Neither is really a "choice", they're just what happens when you don't think about connections at all.
Mistake one: share a single connection for everything.
One connection, opened once, reused by the whole app. Sounds efficient. It's a trap.
A connection can only do one thing at a time. So the moment two requests need the database at once, one of them waits. Then three requests, four waits.
You've built a single lane road and you're wondering why there's a traffic jam.
Mistake two: open a new connection for every request.
This fixes the traffic jam, now nobody's waiting on anybody. But remember what a connection costs on the Postgres side.
Every request is now forking a new OS process. Handle a thousand requests a second and you're forking a thousand processes a second. The server's CPU and memory climb with no ceiling, because nothing is telling it to stop.
Eventually it doesn't just slow down, it falls over.
And it's not only the database that suffers. Your own app pays a price too. Every outgoing connection needs a port from the operating system, and ports aren't infinite.
Open enough connections fast enough, and your app runs out of ports before the database even has a chance to crash. You get failures on both ends of the wire.
Neither pattern is "wrong" in some abstract sense - they're both just missing the same one idea:
Nothing is putting a limit on how many connections exist at once.
The fix is almost boring
Once you see the problem clearly, the fix isn't clever. It's obvious.
Don't open a connection for every request. Don't share one connection for everything.
Instead: open a small, fixed number of connections once, ahead of time, and reuse them.
This is a connection pool.
It's a box holding, say, ten already open connections. A request comes in, grabs one, uses it, hands it back. Next request grabs whatever's free.
If all ten are busy, the eleventh request waits a few milliseconds but the database never sees an eleventh connection get created. Its resource usage stays flat no matter how much traffic you throw at it.
That's the entire idea.
A pool doesn't make your database faster. It makes your database's load predictable, which turns out to matter a lot more.
Nobody reads the defaults, and that's how you get burned
Here's where most people trip.
Every database driver ships with pooling built in, turned on by default. So developers assume they're covered. They're not, because the defaults are usually tuned for a toy app, not your production traffic.
There are two numbers that control a pool, no matter what language or driver you're using, even if the names differ slightly:
- How many connections it's allowed to keep sitting idle, warm and ready
- How many connections it's allowed to have open in total at once
The trap: if the first number is small but the second is unlimited, here's what actually happens.
The pool keeps a couple of connections warm. Every request beyond that opens a fresh one, and since there's no room to keep it idle afterward, it gets closed immediately instead of reused.
You end up opening and closing connections constantly, at high speed, without ever noticing, because from the outside it still looks like "pooling."
It's mistake two, wearing a disguise.
The fix is one line of config: set both numbers explicitly, and set them equal.
Whatever your pool can open, let it also keep idle.
No churn, no surprise CPU spikes at 2 AM.
How big should the pool actually be?
The instinct is: bigger pool, more throughput, right?
Wrong, and this is the part that surprises people the first time they benchmark it.
Plot pool size against requests handled per second, and you don't get a line that keeps climbing. You get a curve that rises, peaks, then comes back down.
Too few connections and you're bottlenecked, sure. But too many connections and the database spends more time juggling all of them than actually running queries, more processes fighting for the same CPU, more overhead just keeping track of who's doing what.
Throughput actually drops.
There's a decent starting formula if you don't want to benchmark from scratch:
pool size = number of CPU cores × 2
That's it.
(There used to be an extra term in this formula for disk spindles, back when hard drives were physically spinning platters and more spindles meant more parallel disk operations. SSDs killed that term. Ignore it.)
Treat this as a starting guess, not a law.
It assumes the machine is running Postgres and nothing else - if other things share that server, your real sweet spot will sit lower than the formula suggests.
The only way to know for sure is to actually run load against different pool sizes and watch where throughput peaks. It's not hard to do, and it's the single most useful experiment you can run before shipping to production.
Then you scale out, and the whole thing breaks again
Say you've done everything right. One app, one database, a nicely tuned pool. Life is good.
Then you scale horizontally, you run three copies of your backend instead of one, because that's how you handle more traffic.
And here's the thing nobody warns you about: each of those three copies opens its own pool.
Pool size eight, times three instances, is twenty four connections hitting the database not eight.
Now stretch that to reality.
Big systems don't run three instances. They run hundreds. Each one quietly opening its own pool, each one assuming it's being reasonable, and the database ends up drowning under connection counts nobody actually planned for.
You tuned every individual pool correctly and it still doesn't matter, because pools don't know about each other.
Enter PgBouncer
This is the problem PgBouncer exists to solve, and understanding it is easy once you frame it right:
PgBouncer is a pool of pools.
It's a small proxy that sits between all your app instances and the real database.
To your apps, it looks exactly like Postgres, they connect to it the same way they'd connect to the real thing.
But behind the scenes, PgBouncer keeps only a handful of real connections open to Postgres - say, four and no matter how many app instances are trying to talk to it.
How?
It hands out virtual connections generously to every app instance, but only assigns a real connection the moment there's actual work to do, a query to run.
The instant that work is done, the real connection goes back into PgBouncer's small pool, ready for whoever needs it next.
Hundreds of virtual connections can be juggled by just a handful of real ones underneath, because most of the time, most connections aren't actually doing anything, they're just sitting there between queries.
The database now only ever sees PgBouncer's fixed, small connection count.
It doesn't matter if you're running three instances or three thousand.
The catch: when exactly does it hand the connection back?
This is the one question that decides how PgBouncer behaves, and it comes in three flavors.
Hold it for the whole session.
A connection stays with an app the entire time it's connected, released only when it disconnects.
This behaves exactly like talking to Postgres directly, nothing breaks, everything you're used to still works.
The downside: you barely save anything, because a connection sits reserved even while the app isn't actively querying.
Hold it only for a transaction.
The moment a transaction finishes, committed or rolled back, the connection goes straight back to the pool, even if the app's session is technically still open.
This is where the real savings kick in, and it's what almost every production setup runs.
But there's a real cost: the physical connection underneath you can change between transactions.
Anything that depends on connection level memory can silently stop working:
- A prepared statement
- A
SETcommand - A temp table
- A lock held outside a transaction
The next transaction might land on a completely different real connection.
The rule of thumb: keep anything stateful strictly inside one transaction, and you're fine.
Hold it only for a single statement.
Maximum efficiency, minimum flexibility you basically can't run multi statement transactions reliably anymore.
This mode exists for very specific setups and isn't something most people should reach for.
Transaction mode is the default almost everyone lands on, because it's the point where you get real savings without giving up too much.
You just need your app code to know the rules of the game. No assuming state survives across transaction boundaries.
Do you even need it?
Not always.
If you're running a single app process talking to a single database, your own pool is already doing the job. Here, PgBouncer would just be extra machinery for no real benefit.
It earns its place the moment you have multiple independent processes like multiple instances of one service, or several different services, all connecting to the same database on their own.
That's when connection counts stop being something any one pool can control, and you need one layer sitting above all of them, enforcing the real limit centrally.
The one idea worth remembering
Every problem in this article, and every fix, comes back to the same sentence:
A database connection is real, physical work, and nothing enforces a limit on it unless you build one.
Pooling puts that limit in one process.
PgBouncer puts that limit across every process talking to your database.
Everything else like idle versus max connections, the inverted U curve, transaction mode versus session mode is just the fine print underneath that one idea.