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.
1. Typical backend with a DB, serving either a web/mobile app or other services within a company. Each job had a pool, so there's a steady number of connections to the DB that didn't exceed what it can support, with some headroom for monitoring, cronjobs, and emergency access. Xacts were usually short. Was able to handle high QPS, and when it hit the limits, it was something on the DB rather than backend CPU starvation, so adding more backends and thus connections wouldn't have helped. Even though some of these were on Heroku which has a rather low DB connection limit.
2. Data pipeline that used Postgres queries as sort of a map-reduce. Not ideal but I think not that uncommon. Each machine had a local DB with mostly temp tables, plus there were some shared DBs. Each running stage in the pipeline needed one connection per CPU core because queries were sharded that way to utilize all cores.
3. Another data pipeline that used batch workers that did their bookkeeping in a DB. This was infrequent enough access that I had each one opening a connection right before using it then closing it after. PgBouncer would make sense there, but we were fine even if every worker opened a connection at the same time. That's partially because many of those workers were GPU instances, so there weren't terribly many of them.
I'm actually wondering who is in situation #1 and needs PgBouncer, and why exactly. The scenario I have in my head is you're doing heavy CPU work directly in your web workers, and thus you need more workers than you have DB connections available, which seems like it's more monolithic than it should be.
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.
If you do have a large number of clients, PgBouncer only means you have a single shared pool of connections rather than each replica having its own smaller pool. You already have a load-balancer for the web clients, so the latter is maybe fine. Of course this doesn't work if you have more replicas than available DB connections.
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.
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.
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 can go either way on this topic, however for the sake of engineering lets invert this problem a little bit and take it upstream. When your entire system is thread based (unique thread assigned to a given request) then even with pgBouncer you end up standing in line to wait for the connection. Most IO heavy application servers now just have the threads waiting for DB connections cause you just scaled (increased number of instances) the application servers for the load. This thread waiting could have been done on web server level as well, allowing one to manage DB connections in application server instead of adding pgBouncer.
tldr; a lot of times pgBouncer is just a duct taped solution to upstream problem. You can easily have web scale (?) application without pgBouncer if you application logic allows it and you pick a applicable design choice.
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.
"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.