Why we built yet another Postgres connection pooler

(pgdog.dev)

72 points by levkk4 hours ago

4 comments

petters2 hours ago
> Since connection poolers reuse connections between clients, the connection state of one client “leaks” into the connection state of another.Wow this is very bad. This actually happens in typical Postgres setups?
- vizzier2 hours ago
 by definition connection poolers re-use connections so it it can happen with any connection pooling setup, PG or no.in pgbouncer the connection is reset via a customisable command [0] which should reset the connection to a clean state.[0] <a href="https://www.pgbouncer.org/config.html#server_reset_query" rel="nofollow">https://www.pgbouncer.org/config.html#server_reset_query</a>
- llimllib2 hours ago
 Yes, as a consequence of how aggressively transparent to the postgres wire protocol pgbouncer wants to be. This article does a good job explaining it: <a href="https://www.augusteo.com/blog/how-pgbouncer-works" rel="nofollow">https://www.augusteo.com/blog/how-pgbouncer-works</a>
- McGlockenshire1 hour ago
 You'll see this kind of fun in other databases that support "persistent connections." When you start up, you have absolutely no idea what the state of the database is. If a previous process errored out, you might find yourself in the middle of a broken transaction for example. Did the last session do some weird SET magic to make things work? Did it create temporary tables? Well guess what, it's all still there!
inigyou34 minutes ago
Doesn't this NOTIFY performance fix mean that it isn't transactional any more?
- levkk22 minutes ago
 From the strictest CAP theorem definition, that's correct, it is not. But, it's pretty close. I know that in the database world, that's not a good answer, but in practice, it will deliver the vast majority of messages, so maybe that's good enough? We'll see.We show that it's possible to come close without breaking the DB or the app, but I suspect, it's not quite yet at the level you'd expect from a _durable_ work queue, e.g., Kafka. Not going to replace that one anytime soon.
jauntywundrkind1 hour ago
Clickhouse also just put out a fun article on scaling pgbouncer too, talking about scaling out so_reuseport while not having to shard so harshly (a major limitation pgdog here is addressing via rewrite), <a href="https://clickhouse.com/blog/pgbouncer-clickhouse-managed-postgres" rel="nofollow">https://clickhouse.com/blog/pgbouncer-clickhouse-managed-pos...</a> <a href="https://news.ycombinator.com/item?id=48814152">https://news.ycombinator.com/item?id=48814152</a>
- merb1 hour ago
 Well tbf pgdog looks extremely amazing on paper and goes way beyond multi threading.The notify/listen fix and automatic query routing to read replicas and auto sharding might bringt Postgres finally closer to vitess
mmakeev3 hours ago
we moved our django app behind pgbouncer transaction pooling a few days ago and the surprise wasn't SET so much as queryset.iterator(). it relies on server side cursors, which don't survive being pooled, so we had to disable it everywhere and let it fall back to client side. also had to move statement_timeout out of the app's connection options into the pooler's own connect query, since libpq startup params just get silently ignored behind it.