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:
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.
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 a similar architectural alternative...is the ONLY one in the whole thread down voted. HN continue to excel in technical chops...
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.
pgbouncer runs as a single threaded service, so it ends up getting bottlenecked by some heavy clients on typical server CPUs with low clock speeds.
Sharding/peering pgbouncer works, but it's not perfect.
More significantly for us is that Neon has millions of databases in each region, and thousands get created/destroyed every hour. Managing the pgbouncer config and shared connection pools will be extremely tricky to balance. We have a bunch of logic for this in the proxy I maintain, but not in pgbouncer.
The main intent is to incorporate a pooler directly into our existing proxy service to avoid needing double proxy services. We just need to find the right pooler implementation (and one that works in async Rust)
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.
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.
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.
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.
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.
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.
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.
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.
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, but how are you getting into that situation to begin with, you have some monolithic web service that's doing heavy CPU work inside the web handlers?
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 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.
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.
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).
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.
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.
62 comments
[ 0.21 ms ] story [ 29.0 ms ] threadhttps://docs.progress.com/bundle/datadirect-postgresql-odbc-...
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.
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.
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
Even on serverless platforms like Heroku, I've been fine giving each worker a pool such that max_workers * pool_size < max_connections.
Sharding/peering pgbouncer works, but it's not perfect.
More significantly for us is that Neon has millions of databases in each region, and thousands get created/destroyed every hour. Managing the pgbouncer config and shared connection pools will be extremely tricky to balance. We have a bunch of logic for this in the proxy I maintain, but not in pgbouncer.
The main intent is to incorporate a pooler directly into our existing proxy service to avoid needing double proxy services. We just need to find the right pooler implementation (and one that works in async Rust)
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.
You aren't running large scale xact/s on a single node.
"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.
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.
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.
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.
Then again, people forget you can just run your own Postgres (or anything really).
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.
If everyone needs it, is it really a non-core function?
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.