8 ms·
PgBouncer is useful, important, and fraught with peril
- qlkjwenf 3y agoLet's hope PG team will drop thread-based connections for the superior lightweight task-based connections. Then PgBouncer will not be needed.
- jtc331 3y agoPG doesn’t have a thread per connection; it has a process per connection.
- physicles 3y agoMy understanding now is that using pgBouncer falls under the category of YAGNI / “the best part is no part”: don’t use it unless you have a demonstrated need for it, because it adds complexity. About 5 years ago we migrated to postgres and installed pgBouncer because of course you do. Sometime last year I started seeing some weird issues like the article points out, and the more I looked into the gory details, the more shocked I became. (The fact that statement level isolation even exists is… wow) So I did an experiment and deleted it. Things kept working just fine. We’re not living in a microservices world so application-level pooling is enough — I don’t think I’ve ever seen us use half of available connections.
- jgalt212 3y ago> I also think community and industry enthusiasm around Postgres is at an all time high. There are more managed hosting options than ever (Crunchy Data, Render, Fly.io, and on and on), deep extensions like PostgresML, Citus and Timescale, serverless options like Neon, and real-time services like Supabase with Postgres at their center. Please sell me why you'd use one vendor to host your DB and another one to host your app. If they are in different racks (forget about different data centers), that's going to kill you on latency. The only way around that we've seen is to vectorize all your queries.
- jtc331 3y agoIf deploying on different racks is that big of problem for your latency then you’re severely limited in how far you can scale your application just by physical rack space. IME this just isn’t true.
- jtc331 3y agoAnd even if in different data centers (say different AZs), query latency is probably 1ms; in practice that’s quite low. How many queries are you doing in a request?
- jgalt212 3y ago> query latency is probably 1ms > This generally produces single digit millisecond roundtrip latency between AZs in the same Region. https://aws.amazon.com/blogs/architecture/improving-performance-and-reducing-cost-using-availability-zone-affinity/ https://aws.amazon.com/blogs/architecture/improving-performa... So we're both wrong.
- brightball 3y agoCrunchydata deploys in AWS, GCP or Azure if I recall correctly.
- Deadron 3y agoPgBouncer has always left me confused in the world of application level connection pooling. I have never quite understand the value of it if we are already using connection pools in our applications. I don't want to pool connections to another pool.
- taspeotis 3y agoLambdas
- Deadron 3y agoIf you are running aws lambdas I believe you should be using the RDS proxy product(This is a very similar product though).
- mason55 3y agoSuper annoying that RDS Proxy doesn’t support IAM auth against the DB. We moved all our DB users to use IAM auth based on instance roles and then found out that RDS proxy doesn’t support it.
- mscrivo 3y agoApplication level connection pools are not enough if you're using something like k8s and have your "application" running across hundreds of pods, each with their own application level connection pool. pgBouncer helps tremendously in that situation because all those pods will use a single pool. We cut down avg open connections dramatically by doing that from over 1000 to less than 400.
- Deadron 3y agoThis still doesn't really make sense to me. You can't scale an application that relies on a database heavily to this level because your fundamental constraint IS the database. If you are already hitting your max number of connections with a small number of applications there are no benefits to further horizontal scaling. You are just passing the buck around because only a limited number can hit the database at any one time.
- mickeyp 3y agoI mean... if you multiplex disparate statements into the same connection and session then, well... yes, that is fraught with an incredible amount of complexity. That stuff's for OLAP, read-only replica servers and so on. High-throughput "hey I just need this one thing." Your large-scale app probably won't need that by default. You pool connections in pgBouncer (or your application) because they're slow and expensive to set up. If you run a standard N-tier architecture and you have fifty 'backend apps' (say web apps) that talk to a communal PG server, you want pgBouncer to avoid connection churn. If your scale's a lot smaller than that then you don't need pgBouncer and you can get by with your app pooling a handful of conns --- subject to the usual vagaries that come with hand-wavey explanations. Measure, monitor, etc. It's an interesting and illuminating article, but the take-away is don't do weird statement-based transaction multiplexing unless you know exactly why you want that! If you're running into "connection limits" on Heroku or whatever... maybe now is the time to stop letting other people manage your DB and just run it yourself? You're clearly big enough to run up against that. Or, maybe, release your session back to the pool (app/bouncer/whatever) instead of sitting on it. For OLTP stuff like 99% of all web apps, that's key. With 100 connections and a p99 request-response cycle with DB hits being <50ms, you can handle a lot of traffic.
- bilekas 3y ago> If you're running into "connection limits" on Heroku or whatever... maybe now is the time to stop letting other people manage your DB and just run it yourself? You're clearly big enough to run up against that. This is top advice actually. I would usually always suggest this! I've felt a lot other services too that can run better on the local/server deployment.
- sgarland 3y ago> maybe now is the time to stop letting other people manage your DB and just run it yourself > With 100 connections and a p99 request-response cycle with DB hits being <50ms, you can handle a lot of traffic. You would be shocked and appalled at how little many devs know about DBs, or how long something _should_ take. I've had to explain that triple-digit msec latency on a simple SELECT is in fact not normal, and they should fix it. And that's just using it, not running it. There are a massive number of orgs that don't want to have to ever think about things like backup strategies or patching, and so they happily fork money over to a cloud provider to do it for them.
- zzzeek 3y agowhat a great post, we have had a ton of issues with users using pgbouncer and it's not because things are "broken" per se, it's just the situation is very complicated, and pgbouncer's docs are also IMO in need of updating to be more detailed and in a few critical cases less misleading, specifically the prepared statements docs. This blog post refers to this misleading nature at https://jpcamara.com/2023/04/12/pgbouncer-is-useful.html#prepared-statements https://jpcamara.com/2023/04/12/pgbouncer-is-useful.html#pre... . > PgBouncer says it doesn’t support prepared statements in either PREPARE or protocol-level format. What it actually doesn’t support are named prepared statements in any form. IMO that's still not accurate. You can use a named prepared statement just fine in transaction mode. start a transaction (so you aren't in autocommit), use a named statement, works fine. you just can't use it again in another transaction, because it will be "gone" (more accurately, "unmoored" - might be in your session, might be in someone else's session). Making things worse, when the prepared statement is "unmoored", its name can then conflict with another client attempting to use the same name. so to use named prepared statements, you can less ideally name them with random strings to avoid conflicts, or you can DEALLOCATE the prepared statement(s) you used at the end of your transaction. for our users that use asyncpg, we have them use a uuid for prepared statements to avoid these name conflicts (asyncpg added this feature for us here: https://github.com/MagicStack/asyncpg/issues/837 https://github.com/MagicStack/asyncpg/issues/837). however, they can just as well use DEALLOCATE ALL, set this as their `server_reset_query`, and then so that happens in transaction mode, also set `server_reset_query_always`, so that it's called at the end of transactions. Where pgbouncer here IMO entirely misleadingly documents this as "This setting is for working around broken setups that run applications that use session features over a transaction-pooled PgBouncer." - which is why nobody uses it, because pgbouncer claims this is "broken". It's not any more broken than it is to switch out the PostgreSQL session underneath a connection that uses multiple transactions. Pgbouncer can do better here and make this clearer and more accommodating of real world database drivers.
- dkhenry 3y agoThis might be the best explanation I have seen of the benefits and pitfalls of deploying pgbouncer ( or any connection pooler as they are all similar ). The only thing I would add is from a deployment perspective, you should run two layers of pgbouncer. One that runs on the same host as your application ( sidecar container in k8s ), and one global layer that all connections to your DB must pass through. Every large scale application generally gets to this kind of architecture for various reasons, and it helps to just start with it. Some people will argue that connection pooling at the application layer is good enough to replace the sidecar proxy, but from my experience management is simplified by having the pooling done via the sidecar.
- mrits 3y agoMy large scale application certainly didn't run the pooler on the host. I don't see any reason to do this except for not wanting to become familiar with postgres itself.
- nickphx 3y agoI ran the Pooler on the host.. I ran a few hundred hosts, so having a central pool was not ideal.
- dkhenry 3y agoWhat about running PHP, Django, Node, or Ruby based applications ? They all do connection per request so the pooler on host gives you an immediate improvement to connection latency. Even if you are using a language with a built in connection pooler like Go or Java being able to manage your connections external to your application is enough of a benefit to keep the pooler separate.
- mst 3y ago> Postgres doesn’t have a concept of nested transactions It has savepoints and those nest fine. (perl's DBIx::Class can be configured to automatically convert transactions into savepoints if it's already inside another transaction; presumably any other ORM-like thing could also do that for you in theory but whether the one you're currently using -does- is left as an exercise to the reader)
- cryvate1284 3y agoDjango does this too
- samokhvalov 3y agobeware of the issues related to postgres subtransactions https://postgres.ai/blog/20210831-postgresql-subtransactions-considered-harmful https://postgres.ai/blog/20210831-postgresql-subtransactions...
- mst 3y agoSolid list of ways to shoot yourself in the foot with it, even if "use savepoints in moderation" is, for a suitable value of "moderation", sufficient to largely avoid them. (though if you're going to use them, read the article and maybe some of the things it links anyway to calibrate your sense of suitable) And that article also lists a bunch of ORMs that handle subtransactions out of the box, for anybody curious.
- benzible 3y ago> There are more managed hosting options than ever (Crunchy Data, Render, Fly.io, and on and on) From fly.io's docs [1]: > This Is Not Managed Postgres [1] https://fly.io/docs/postgres/getting-started/what-you-should-know/ https://fly.io/docs/postgres/getting-started/what-you-should...
- benzible 3y agoWhy would someone downvote this? Seems pretty relevant.
- deleted 3y ago[deleted]
- sidcool 3y agoIf a Database needs to support more than 500 connections at a given time (due to scale), what's the way around?
- fabian2k 3y agoWhat exactly do you want to scale? But in the end the answer is likely "connection pooling", either integrated in the application or with a dedicated pooler like PgBouncer which this article is about. For Postgres a good way to scale is to just use a bigger server. You can get a lot of very fast storage and lots of CPU cores inside a single server today. And if that isn't enough you're far, far into territory where you can't give generic answers and it depends a lot on your specific use case and what you do with that database.
- sidcool 3y agoI mean let's say I have to support 10000 updates per second and 100000 reads per second. Surely 500 connection pool won't suffice.
- emilsedgh 3y agoI don't think 10K updates per second are really that difficult for the db (Unless you have locking issues). But at that point your reads should be probably be sent to read-only replica's. So you write to a master but all your read-heavy apps and queries run against replicas.
- jtc331 3y agoEach of those reads may only be 1ms, which would translate into only 100 connections needed. So the workload you describe — without more detail — may in fact be just fine with that connection count. But plenty of people also run Postgres with well above 500 max connections.
- lib-dev 3y agoIf you require 500 instances of your app to scale, how do you get away with just a single DB instance?
- ComputerGuru 3y agoOn-topic tangent: reminder or heads-up (depending on if you’ve already seen this) that Postgres is experimenting with thread-based instead of process-based connections (which would pretty much obviate the need for pgBouncer if it works out and becomes the connection model going forward). HN discussion from a few months ago, with lots of commentary relevant to any pgBouncer scenarios: https://news.ycombinator.com/item?id=36393030 https://news.ycombinator.com/item?id=36393030
- timacles 3y agoI follow the PG world very closely and would like to add that Postgres community essentially "soft" turned that down and its not going to be a thing any time soon, like in the next five years at least.
- skybrian 3y agoI’m wondering if they’ve considered less radical solutions? Something like adding a new thread-based front end to Postgres itself while keeping the processes, and then gradually fixing whatever makes that less than seamless.
- artyom 3y agoGreat article. Should be part of the official PgBouncer documentation.
- JelteF 3y agoPgbouncer maintainer here. Overall I think this is a great description of the tradeoffs that PgBouncer brings and how to work around/manage them. I'm actively working on fixing quite a few of the issues in this blog though 1. Named protocol-level prepared statements in transaction mode has a PR that's pretty close to being merged: https://github.com/pgbouncer/pgbouncer/pull/845 https://github.com/pgbouncer/pgbouncer/pull/845 2. SET/RESET tracking in transaction mode. For this we need some changes in the postgres protocol to ask for postgres to tell pgbouncer about setting updates. This is being worked on here: https://www.postgresql.org/message-id/flat/CAGECzQQOOhH1RztuYHgM%3D2FR1eOLAc6gbwLgFJE3Lcrckv362g%40mail.gmail.com#9b27e7ca70994e601dcf16943d0a2733 https://www.postgresql.org/message-id/flat/CAGECzQQOOhH1Rztu... 3. The single threading issue can be worked around by using multiple processes that listen on the same port using so_reuseport=1 https://www.pgbouncer.org/config.html#so_reuseport https://www.pgbouncer.org/config.html#so_reuseport 4. The pg_dump issue can actually be solved already by installing the Citus extension and using track_extra_parameters (otherwise you have to wait for the same postgres protocol addition that's needed for the other SET/RESET commands) https://www.pgbouncer.org/config.html#track_extra_parameters https://www.pgbouncer.org/config.html#track_extra_parameters