▲ 105 points
back
68 comments
(I work on the postgres proxy layer at Neon)
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.
Also some people with use cases that shouldn't need PgBouncer end up thinking they do because something else is misconfigured. This was brought up in a parent of https://news.ycombinator.com/item?id=49019695; FastAPI recommends dep-injecting transactions into your HTTP handlers. Ties up all your connections and creates idle xact spam. There are valid reasons to use PgBouncer, this isn't one.
Even on serverless platforms like Heroku, I've been fine giving each worker a pool such that max_workers * pool_size < max_connections.
And the only comment in this whole thread, who argues for a similar architectural alternative...is the ONLY one in the whole thread down voted. HN continue to excel in technical chops...
Totally agree. Postgres core should include connection pooling by default, and PgBouncer is probably the natural path to get there.
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
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.
It's an ironic claim because the Oracle cloud has great support for the Oracle Database, which doesn't require "bouncers" or equivalent. Its support for server-side connection pooling, client side load balancing (SCAN) and horizontal scaling means it offers exactly what the author wants - a single URL that just magically works and scales to any amount of work or connections cheaply.
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.
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.
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.
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.
> since neither IBM nor Oracle is a service that any self-respecting person not part of an enterprise sales cycle would actually use
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.
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 use the Ecto connection pool when using Elixir/Phoenix.
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.
It's a little more nuanced than that. At my company we run Kubernetes workloads where we have 20-30 or sometimes more pods connecting to a single Postgres instance, each pod handling hundreds of concurrent requests. If each pod hangs on to pooled connections for more than a few seconds, then you end up with quite a few Postgres processes and a lot of memory usage and process churn; we mitigate that by having a relatively short TTL on the pool, so idle ones get reaped relatively quickly. But any idle connection not used by a pod can't be used by a different pod. A connection pooler lets all the pods share more connections to Postgres, reducing in less wastage.
> 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.
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.
I see many comments comparing PgBouncer with application connection poolers without addressing the conceptual difference between them. Here it is:
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.
Yes. Sometimes PostgreSQL is overkill for a low traffic, simple application. PgBouncer would be even more overkill and add unnecessary complexity.
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:
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.
I guarantee you the vast majority of RDS instances aren't using RDS Proxy. For one, it's rather expensive.
For sure. I mean: many – if not most – people?
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.
Yes, I don't always need it. Depends on the service architecture.
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.
Yes. I've never even heard of PgBouncer.
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.
A whole bunch of Cloudflare clients run Postgres (self hosted) without pgbouncer (they use hyperdrive).
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 currently run without pgbouncer, but I have at most 200 connections. 50 of them are just replication. I didn't want the complexity.
Yes
I’m curius about pgcat, is there anyone here using it ?
Yes.
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).
The answer is: yes.
I use postgres. I've never heard of pgbouncer. So the answer is an easy Yes.
Saved you a click.
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.
pgdog.
Are you kidding me? Have you heard about Data Direct?
https://docs.progress.com/bundle/datadirect-postgresql-odbc-...
Yes, except for serverless backend, you normally don't need it.
We run asyncpg directly against Neon Postgres without PgBouncer —
works well for low-to-medium concurrency. Neon's serverless connection
pooling handles a lot of what PgBouncer would, so the overhead didn't
seem worth it for our use case.
Transaction mode has one real footgun: SET statements are session-scoped, but in transaction mode your session gets reassigned between transactions. Easy to miss until you're chasing a mysterious search_path bug at 2am.
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.
The overhead of process-per-connection in Postgres is still the biggest hurdle for mobile-heavy workloads where you might have thousands of intermittent clients. Brandur is right that modern hardware makes the context switching less of a bottleneck, but the memory pressure from large work_mem settings on each backend process remains a real risk. I'm curious if anyone here has successfully moved to a pure built-in connection pool in v14+ without seeing a regression in latency during traffic spikes.