Article title is pure click-bait. PgBouncer adds complexity. If you need it, you need it. If you don't need it, then you added complexity for nothing.
> since neither IBM nor Oracle is a service that any self-respecting person not part of an enterprise sales cycle would actually use
Author needs a serious ego check. There are legitimate engineering reasons to pick IBM Cloud (like if you need to support Z mainframes, which you will sometimes need if you sell to those enterprise folk) as well as Oracle Cloud (they built datacenters in cities that are not served by other cloud providers and can thus offer the lowest latency). These reasons may not be common, but they're certainly legitimate.
PgBouncer is entirely optional and it's not always the right choice. If you have a classical app (non serverless) and you can maintain a connection pool from your app, then I recommend avoiding pgbouncer.
The benefits of pgbouncer mostly come from irregular client connections (too many, too much churn). If you don't have that problem, go direct to postgres.
I'm exploring replacing pgbouncer with an alternative (maybe home grown) at the moment. Mostly for multi-tenancy and HA reasons. Pgbouncer has been good for us, but it's limited in how we can deploy it in a multi-tenant environment.
And the only comment in this whole thread, who argues for that architectural alternative...is the ONLY one in the whole thread down voted. HN continue to excel in technical chops...
Clickbait post, already made the rounds on in other sites and was mercilessly torn to shreds. The answer is that millions do in production. PgBouncer is only needed for stateless backends, and even then, only under specific circumstances.
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.
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.
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.
Python: absolutely necessary due to the amount of processes and various deployments to run an application once it grows.
Java: never felt the need even on quite big apps. As it's much easier to share a connection pool locally, it's not as many single connections across the whole app.
Your high level buckets are languages but the constraints you list are usage patterns.
You can build applications in any language that have many short-lived transactions in single connections or a few huge, blocking one, or anything in between.
The former case is a good one for PGBouncer (it can interleave transactions) and the latter isn’t (it can’t do much about your three minute long BEGIN…COMMIT). Neither has to do with the language.
They addressed this though? I suspect you are unfamiliar with the GIL in python. The reason for a language distinction is because, as OP says: "As it's much easier to share a connection pool locally".
This may change vaguely soon, but right now you typically scale python apps by starting multiple python processes, while for Java you can just add threads. Python processes can't share a thread pool among all of them, while Java can.
If you have a connection pooler in your application, and the DB is only used for this application, you don't need an external pool like PgBouncer. That is probably a pretty common scenario, and typical web frameworks include a connection pool anyway.
An additional complication is that more aggressive pooling methods have side effects that you must know and prevent in your application. They're not safe to use out of the box.
The need for a connection pool is a side effect of the heavy process-based PostgreSQL connections. And I would suspect that this will change at some point in the not so near future, so that users don't have to think about this part this much.
If you don’t use serverless but instead a few (vertically scaling) servers, and your ORM / query builder supports pooling (all node libraries I’ve used have a pooler)…
Having a setup with just a simple docker deploy, running a monolith, not using pgbouncer so you can use LISTEN/NOTIFY to implement your own job queue:
I’d argue this is not the right question. Obviously people use Postgres without PgBouncer. If you include non-production in the mix (CI/CD), most connections probably avoid it.
But because PgBouncer is so common, the question should probably be, why isn’t connection pooling part of Postgres out of the box? I think this might be the more interesting question.
I wouldn't do it without pgbouncer. Asking for trouble when one day connections exceeds. It's just that you start with "oh I'll manage the pool from my app" and then you're stuck with either putting things into the app or tuning the pool for the other side-programs you need from the app.
Every single connection to Postgres is a new process, which requires a fork and new memory allocation (at least 10MB plus whatever you need for your query).
PgBouncer opens a pool of connections and then reuses them each time a client asks for a connection. This reduces latency (no more fork) and overhead (reuse memory).
If you handle database requests naively, every request to the database may open its own connection. This is a super simple approach but will exceed the amount of connections the database can or wants to handle concurrently.
One solution is to increase your app complexity and introduce a layer that manages connection pooling or queuing.
Or you can just keep your app naive and simple and put pgbouncer transparently in front of your DB. Even for multiple apps, so instead of every app increasing in complexity, reimplementing connection handling, you just have pgbouncer.
What pgbouncer does is indeed core functionality. Compare Postgres to MySQL and sql server, where analogous standalone connection pools are rarely used. The fundamental reason pgbouncer needs to exist is Postgres’ utterly retrograde design. Other examples: xid wraparound, conflict with recovery, lack of undo space.
I mean… my self hosted Postgres with its ca 15 active connections certainly doesn't use PgBouncer, and it doesn't need to. But that was presumably not the intended scope of the question?
Then again, people forget you can just run your own Postgres (or anything really).
of course, the vast, vast majority of PostgreSQL users outside of managed cloud hosting are not using pgbouncer. pgbouncer introduces complexities into the database conversation (transaction-level pooling interacting with the prepared statement cache is a long recurring nightmare for us at sqlalchemy) that often not worth the complexity for small local installations.
this article seems to be talking about commercial cloud managed PG services, which yes, those absolutely need to support connection pooling and of course they're going to use pgbouncer.
DataDirect is not free but I guess we are discussing technical merits here. For them pooling happens in the connectivity layer and exposes proper pool controls such as minimum and maximum pool size, connection lifetime behavior, and optional connection state reset.
More importantly, it has capabilities that PgBouncer is not designed to provide. It can maintain alternate PostgreSQL servers, retry connections, randomize connection attempts across primary/alternate servers, and has explicit failover modes. The application retains a real PostgreSQL session while the driver handles connection reuse and failure handling.
That matters because of PgBouncer big technical compromise that is transaction pooling.
This breaks the assumption that one client connection is the PostgreSQL backend session.
Consequently, several session scoped PostgreSQL features do not work normally in transaction pooling. PgBouncer own compatibility table, lists some of the limitations:
https://www.pgbouncer.org/features.html
DataDirect does not need to solve that particular problem because its architecture does not perform that same transaction level back end swapping.
> since neither IBM nor Oracle is a service that any self-respecting person not part of an enterprise sales cycle would actually use
Author needs a serious ego check. There are legitimate engineering reasons to pick IBM Cloud (like if you need to support Z mainframes, which you will sometimes need if you sell to those enterprise folk) as well as Oracle Cloud (they built datacenters in cities that are not served by other cloud providers and can thus offer the lowest latency). These reasons may not be common, but they're certainly legitimate.
PgBouncer is entirely optional and it's not always the right choice. If you have a classical app (non serverless) and you can maintain a connection pool from your app, then I recommend avoiding pgbouncer.
The benefits of pgbouncer mostly come from irregular client connections (too many, too much churn). If you don't have that problem, go direct to postgres.
I'm exploring replacing pgbouncer with an alternative (maybe home grown) at the moment. Mostly for multi-tenancy and HA reasons. Pgbouncer has been good for us, but it's limited in how we can deploy it in a multi-tenant environment.
https://news.ycombinator.com/item?id=49319988
https://news.ycombinator.com/item?id=49320460
"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.
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.
- 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.
Java: never felt the need even on quite big apps. As it's much easier to share a connection pool locally, it's not as many single connections across the whole app.
You can build applications in any language that have many short-lived transactions in single connections or a few huge, blocking one, or anything in between.
The former case is a good one for PGBouncer (it can interleave transactions) and the latter isn’t (it can’t do much about your three minute long BEGIN…COMMIT). Neither has to do with the language.
This may change vaguely soon, but right now you typically scale python apps by starting multiple python processes, while for Java you can just add threads. Python processes can't share a thread pool among all of them, while Java can.
An additional complication is that more aggressive pooling methods have side effects that you must know and prevent in your application. They're not safe to use out of the box.
The need for a connection pool is a side effect of the heavy process-based PostgreSQL connections. And I would suspect that this will change at some point in the not so near future, so that users don't have to think about this part this much.
Having a setup with just a simple docker deploy, running a monolith, not using pgbouncer so you can use LISTEN/NOTIFY to implement your own job queue:
https://www.dbos.dev/blog/postgres-listen-notify-scalability
This gives me warm fuzzy feelings, also making me relatively cloud-agnostic in the process, even though devops is not my strong point.
Most projects I do don’t need something more complex or vendor locked-in than this.
But because PgBouncer is so common, the question should probably be, why isn’t connection pooling part of Postgres out of the box? I think this might be the more interesting question.
In my case, with Go, I have always relied on http://github.com/jackc/pgx pool, which works quite well, especially with the binary protocol.
PgBouncer opens a pool of connections and then reuses them each time a client asks for a connection. This reduces latency (no more fork) and overhead (reuse memory).
One solution is to increase your app complexity and introduce a layer that manages connection pooling or queuing.
Or you can just keep your app naive and simple and put pgbouncer transparently in front of your DB. Even for multiple apps, so instead of every app increasing in complexity, reimplementing connection handling, you just have pgbouncer.
Then again, people forget you can just run your own Postgres (or anything really).
this article seems to be talking about commercial cloud managed PG services, which yes, those absolutely need to support connection pooling and of course they're going to use pgbouncer.
https://docs.progress.com/bundle/datadirect-postgresql-odbc-...
More importantly, it has capabilities that PgBouncer is not designed to provide. It can maintain alternate PostgreSQL servers, retry connections, randomize connection attempts across primary/alternate servers, and has explicit failover modes. The application retains a real PostgreSQL session while the driver handles connection reuse and failure handling.
That matters because of PgBouncer big technical compromise that is transaction pooling. This breaks the assumption that one client connection is the PostgreSQL backend session.
Consequently, several session scoped PostgreSQL features do not work normally in transaction pooling. PgBouncer own compatibility table, lists some of the limitations: https://www.pgbouncer.org/features.html
DataDirect does not need to solve that particular problem because its architecture does not perform that same transaction level back end swapping.