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.
Even on serverless platforms like Heroku, I've been fine giving each worker a pool such that max_workers * pool_size < max_connections.
"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.
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.
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.
> 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.
https://docs.oracle.com/en/database/oracle/oracle-database/2...
Yet he says simultaneously that Postgres hasn't improved in a decade, but also no self-respecting person would use a database that fixes all the problems he identified. Right!
Disclosure: work part time in the Oracle DB group. Things I say here are unvetted, personal opinions.
At a small SaaS we were put off by the difficulty getting OracleDB's dev edition installed and working at all. While MySQL (then independent) and Pg were bare bones in comparison, they were very quick to get started, covered what was needed, and no risk of price spikes or time consuming audits. When pain points were encountered with MySQL and Pg there were plenty of flexible options to add into the mix.
Later I saw a competitor being crushed by licensing for Informix when they didn't need more than ~10% of its features.
I think it's very relevant to consider that "enterprise" adjacent databases may not support the licensing you need at scale - but that doesn't mean they don't have great engineering and research teams that are solving really difficult challenges, and those challenges may be highly relevant to your workflows. Go into things with an open mind, if not an open wallet!
IMO startups can get edge by exploiting this information asymmetry. Spending a bit more to solve all your DB problems and buy productivity is a no brainer as they have VC funding but not enough time. A single bad DB outage can be the difference between beating a competitor or losing to them. Ditto for slowly shipping a feature because your senior dev is trying to implement their own message queue engine or other random thing that comes out of the box in other RDBMS engines.
The costs depend what you compare it to. People tend to overestimate it. Cost multipler in Azure is very roughly about 4x, it seems (caveat: am not a cloud pricing expert, comparisons may vary wildly). That doesn't include the cost of bouncers and other hacks that increase the Postgres cost, so it's artificially generous to PG.
If you want better features on the Postgres side then you might look at AlloyDB in Google Cloud which is only 2x cheaper on compute but where storage is actually ~3x more expensive!
The extra money buys you a lot. Not only far more features but you can provision a smaller database because the Oracle DB burst scales in response to load. You are only charged for the extra you use so you can provision for normal load without padding extra for emergencies or peaks. It's a genuine cluster that scales up writes more or less indefinitely without sharding if you design your schema right, that's synchronous multi-write master scaling too so its simple for apps. You don't face OpenAI style problems where the single Postgres master reaches its limits and the whole thing breaks requiring app redesigns. And you aren't just paying for an idle replica: all the capacity you buy can be used for queries. It also uses more efficient algorithms e.g. better MVCC with no autovacuuming problems. And a gazillion other things.
So I think you can easily argue that value delivered is much greater than 2x-4x. The capability gap is much larger than 4x. Especially if you're the sort of startup where a bored dev might start citing Postgres' limitations to justify inventing their own DB infra, or where you hit its scaling limits and have to rearchitect - if that happens you'll never recover the cost difference, Oracle will always be cheaper.
This happens because clouds don't charge databases at licensing+labor value+margin, prices are set at what the market will bear.
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.
Sadly, I’m deeply familiar. What made Evan Phoenix’s Rubinius work so exciting in ~2012 was getting rid of the GIL and seeing what was “fixed” by parallelism and what was not.
You make a good point. I didn’t read the parent comment that way but you’re right about how you scale python with the GIL vs JVM.
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.
We have Kubernetes here too for Java monoliths and we have 8 Pods serving more than that. Since Java has connection pooling, the overhead has never been enough to justify overhead of Pgbouncer for this Java app.
But this is only true if you have no more than a few running instances of your application. So it feels like the cases where you must have Postgres (over an alternative like SQLite) but can't justify PgBouncer are very narrow.
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.
1. Most application connection poolers follow a first-in-first-out (FIFO) algorithm, which is simple enough to implement and is enough to make sure the application always has a connection available to connect to the database. It optimizes low latency, and works great from the point of view from the application. The problem is that it has few mechanisms to remove redundant connections, since the application is constantly keeping them all "warm".
2. PgBouncer and very few external poolers follow the inverse idea – last-in-first-out (LIFO), and they optimize for reducing the number of connections that reach Postgres, thus improving its throughput. The idea might seem crazy at first – the last connection used is the first one to be picked up again – but this algorithm automatically removes excess connections, which will get cold and get closed.
When starting a new application, option (1) is enough, but as it scales up enough, at some time it is recommended to use (2), since having hundreds of open connections to Postgres is bad for performance if you can use PgBouncer or similar to cut it by 90%. Postgres' process-per-connection design works much better when there are fewer connections reaching it.
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.
With PgBouncer addressing one of its biggest historical pain points - prepared statement support in transaction mode, in our managed Postgres offering, we’re increasingly seeing customers use the PgBouncer connection string by default for mose use-cases without running into any hiccups. That wouldn’t necessarily have been the case a few years ago.
PgBouncer is also battle-tested, widely validated, and offers a (surprising) level of configurability. You could also run a peered setup and make it multi-threaded, which is something I didn’t expect when I first came to know about it. https://news.ycombinator.com/item?id=48872874
Heh, snarky.
I guess (almost) everyone uses PgBouncer because they want to use a setup that will need the scaling needs of most users from the get-go, to avoid wasting support time.
Personally, I run without it due to a fairly small scale - I don't need more than about 128-256 connections max and for me a dedicated connection pooler (other than what's sometimes used app-side) would just add complexity.
At the same time, one could totally reasonably make the argument that if almost everyone uses it, then it SHOULD quite possibly be a built in feature, instead of a separate component - such a tighter integration would most likely bring the overall complexity down.
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.
Saved you a click.
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.
psycopg3 prepares statements by default now, which breaks in transaction mode unless you explicitly opt out.
For apps with a persistent server process and an in-app pool the external bouncer is mostly ceremony. The math changes with serverless: no persistent process means no persistent pool, so a dedicated pooler starts pulling its weight.