This 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.
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.
Here, a single database, 300 transactions/s, between 10 - 20 TB dataset size. HA managed by VIP (keepalived).
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.
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 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.
All of my personal apps use Postgres because:
- types are lovely. We love types. SQLite’s default of non-strict typing is, to me, bananas.
- SELECT DISTINCT ON is my ride-or-die
- most importantly, I’m very comfortable in Postgres and the setup cost is basically zero (like SQLite) because Claude does it.
The setup cost is basically zero anyway. Add apt repo, apt-get install the correct version. Easy to run different versions at the same time too. Upgrading is annoying.
Concurrency, data types, scalability, centralization, or replication push for Postgres. The push against PgBouncer is that you don't need it, unless you do. If you have an app-level connection pool, you probably don't need PgBouncer.
>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.