500 PostgreSQL Connections, 470 Idle: Fix This Before Production Slows

~93 words So, you know how this goes... you look at your database one day, and there are, what, five hundred connections open. And the strange thing is, if you actually go and check, most of them aren't really doing anything at all. They're just sort of... sitting there, idle. And the natural reaction, the one everybody has, is to think, well, we probably just need more connections.

But honestly, that's kind of the opposite of what you need. What you're really looking for here is a pool. So, let me walk you through it.

~69 words Let's start by just... looking at what's actually happening, before we change anything. So I run a little count on pg_stat_activity, grouped by state, and, yeah, you can pretty much see the whole story right there. About four hundred and seventy of them are idle. Only twenty-eight or so are actually doing any work.

Five hundred in total. So almost all of it, really, is just sitting there idle.

~100 words Now, this is the part that's really easy to forget. Every single one of those connections, even the idle ones, is a real process running on the machine. It's not a lightweight little socket, it's a whole process. So if I count them up, five hundred, and then sort of...

add up the memory they're each holding, it comes to almost five gigabytes. And that's before a single query has even run. And then on top of that, you've got five hundred processes all sharing sixteen cores, so the scheduler ends up just... thrashing, really, trying to keep everyone moving.

~89 words And, I mean, this is what it actually feels like when it goes wrong. It's, uh... it's usually the middle of the night, the app's just scaled up, and you're sitting there tailing the log, and you start seeing it. Too many clients already. Out of memory. Maybe some client that opened a transaction and then just...

wandered off, still holding a lock. And you check pg_stat_activity, and there's five hundred connections, and again, most of them aren't doing anything. So that... that's the moment the pager goes off.

~86 words So the obvious thing to try, and honestly I think we've all done this at least once, is you just raise max_connections. Push it up to a thousand, restart, and for a second it feels like you've fixed it. But what actually happens is... now you've got twice as many processes on the same memory, and it falls over even sooner.

You start getting out-of-memory errors. So you haven't really added any capacity, you've just, sort of, given yourself twice as many processes to look after.

~79 words The real fix here is a pooler. So something like PgBouncer just sits quietly in front of Postgres. All of your clients, all five hundred of them, connect to the pooler instead, and it keeps a small handful of real connections open to the database, and it just... hands them around as people need them.

So you've got five hundred coming in the front, and maybe thirty-two going out the back. Same amount of work, much, much smaller footprint.

~75 words And the setting that really controls all of this is default_pool_size. So even when you've got five hundred clients all hitting it at once, Postgres, in this example anyway, only ever sees about thirty-two backends. Which is, what, roughly six percent. The clients just quietly share those connections between them, and if anyone has to wait, it's usually only a millisecond or two, but Postgres itself just stays nice and calm through all of it.

~69 words The config, honestly, is smaller than most people expect. You point the databases section at your actual Postgres, and then there are really just a few settings that matter. pool_mode, set to transaction, that's the important one. max_client_conn, set fairly high. And default_pool_size...

I'd start somewhere around two to four times your core count, and then actually measure it, rather than just picking a number and hoping it's right.

~77 words And then you run it, and, this is the step people always seem to skip, you actually prove it worked. So you restart pgbouncer, you point the app at the pooler's port instead of the database's, and then you just... ask it. SHOW POOLS. And there it is, right in front of you.

Five hundred clients connected, only twenty-eight on the server side, and nothing sitting in the queue. So, yeah, that's it quietly doing its job.

~88 words Just a quick word on the modes, because this is the bit that tends to catch people out. Session mode holds onto a connection for the client's whole session, so, honestly, it barely pools at all. Transaction mode, that's really the sweet spot for a web app, it borrows a connection just for the length of a transaction and then gives it straight back.

And then statement mode is more aggressive again, but it's a bit niche. So for a normal web app, you'll almost always want transaction.

~73 words So any time you want to actually see what's going on under the hood, you just ask the pooler directly. SHOW POOLS. And you look at cl_active, which is your connected clients, five hundred of them. But then sv_active, the real backends, that's only twenty-eight. And cl_waiting is zero, which is really the reassuring part, it means nobody's stuck waiting in a queue.

So that... that's what a healthy pool actually looks like.

~58 words And, really, that's the whole payoff. Same five hundred clients, exactly the same load. Before, we had five hundred backends, around five gigs of memory just sitting there idle, and those too-many-clients errors popping up. And after... thirty-two backends, the memory's back, the errors are gone.

Same machine, the whole time. You've just, sort of, stopped wasting it.

~78 words There is one catch, though, and it's genuinely worth knowing going in. In transaction mode, each statement can end up on a different backend connection. So any session-level state, a SET, a session-scoped prepared statement, an advisory lock, it's just... not something you can rely on from one statement to the next.

So the way around it is either to keep that state inside a single transaction, or, for those particular paths, put them on session mode instead.

~86 words So, the usual traps, just briefly. Raising max_connections instead of putting a pooler in, that's the big one to avoid. Using session mode for a web app, when transaction is really what you want. Sizing the pool way too big, sort of 'just to be safe,' instead of sizing it and then measuring.

And then forgetting that your application pool, multiplied by the number of instances, can quietly add up to more than the server pool. So it's really worth counting that one end to end.

~82 words And when something does feel off, the trick is really just... don't guess, ask the pooler. So, SHOW POOLS, and if cl_waiting is slowly creeping up, that's telling you the pool's too small. SHOW CLIENTS and SHOW SERVERS will show you who's actually stuck. And if you're seeing prepared-statement errors, well, the older versions of PgBouncer just don't hold onto them, though the newer ones can, with a setting.

But most of what you need is right there in the admin console.

~67 words So if we put the whole thing together, start to finish... you count your idle connections first, just to see the problem for yourself. You put PgBouncer in front, in transaction mode. You size the pool to your cores. You point the application at the pooler. And then, finally, you verify, and cl_waiting should just be sitting there at zero.

And that, really, is the whole fix.