alt Hacker NewsThis question will get more interesting responses if it was qualified as:
"Does anyone run Postgres without PgBouncer for non-trivial workloads?"
Because, as we can see from the comments so far, lots of people are going to say you don't need it for your blog that gets 10 hits a month.
I've personally never heard of anyone not using PGbouncer, or some connection pooling proxy, for reasonably concurrent workloads. PG's process-per-connection architecture almost requires it. Otherwise even a small connection storm will wreak havoc on your server.
Replies
Yes. The internal services should be doing stuff in bulk and not require too much parallelism. That leaves you with the number of concurrent users, which in b2b apps can be quite low.
Not all non-trivial workloads are web-scale. There are plenty of on-premise applications out there that have at most hundreds or thousands of concurrent users, and the connections come from a bunch of fat spring boot servers that handle most of the pooling by themselves.
Yes, I've done a variety of typical, nontrivial workloads on Postgres for about a decade and have never used PgBouncer. Though I can imagine use cases where it'd make sense.
Good question.
PGBouncer isn't as useful if you have seriously long-running transactions. It can’t do much of anything with those. Sure, you can give it a pool of 1000 and your Postgres instance a pool of 100, but you’re just moving who is going to say, “sorry, the database can’t handle your request right now.”
It’s not a silver bullet.
If you have more connections than Postgres can handle on the hardware it’s on, but with a bit of buffer it’ll be able to burn them down: great.
If you have long-held connections with many transactions that PGBouncer can interleave: great.
If you have connections whose transactions are longer than a reasonable timeout, well, your optimization princess is in another castle.
The main Question is: why do you allow clients to connect to your database server, it should be limited to a server which could actually serve the data in a format the client can just render without any logic client side.
>I've personally never heard of anyone not using PGbouncer, or some connection pooling proxy,
There is plenty of space for large systems which need a database but don't have a large number of clients.
It's a matter of the scale of your data vs. the scale of your readers and writers.
I feel like there are a lot of use cases where I’d opt for SQLite and a lot of use cases where I’d opt for Postgres + PgBouncer. I’m curious what kinds of features push towards using Postgres alone over SQLite.
Here, a single database, 300 transactions/s, between 10 - 20 TB dataset size. HA managed by VIP (keepalived).