Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Why we built yet another Postgres connection pooler

pgdog.dev

Post reply on HN
Print view View original

41–50 of 74 posts

Re: Why we built yet another Postgres connection pooler

#41

Post by rst »

levkk wrote: post #18
mmakeev wrote: post #2

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…

Handling cursors is tough - they are very much session-level objects, so even if we, say, pinned your client while it uses that cursor, which would work, that would decrease the performance of connection pooling overall. So, what's better, breaking your app initially so you know to remove that feature, or letting it work silently while the connection pool isn't 100% in transaction mode? Tough call.

Maybe don't reassign the sesssion to a different client so long as there's a cursor open (the way that most poolers have a mode that won't reassign a session that has a transaction open)?

Re: Why we built yet another Postgres connection pooler

#42

Post by [deleted] »

samlambert wrote: post #33
khurs wrote: post #32

Earlier quoted context omitted.

Looked it up, says 'Will be released as an open source project' but announcement was in July 2025 and no public repo yet which is strange, so time will tell if they will stick to this. https://planetscale.com/blog/planetscale-for-postgres#vitess... https://planetscale.com/neki ... Supabase one is open from start: https://github.com/multigres/multigres https://multigres.com/

We are already moving Neki customers into production. Nobody else is even close. PlanetScale doesn't spend our days yapping about what we are going to do. We just do it.

[deleted]

Re: Why we built yet another Postgres connection pooler

#46

Post by mmakeev »

levkk wrote: post #18
mmakeev wrote: post #2

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…

Handling cursors is tough - they are very much session-level objects, so even if we, say, pinned your client while it uses that cursor, which would work, that would decrease the performance of connection pooling overall. So, what's better, breaking your app initially so you know to remove that feature, or letting it work silently while the connection pool isn't 100% in transaction mode? Tough call.

[dead]

Re: Why we built yet another Postgres connection pooler

#47

Post by mmakeev »

HackerThemAll wrote: post #27
mmakeev wrote: post #2

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…

Using server-side cursors is a sign of bad design. You use the PostgreSQL server's resources as a cache when you're fetching and processing stuff row by row, which is very bad. Just get the data you requested in the first place in one go and do your stuff in your app. Or process all data on the server and then get the processed data in one batch as well.

two different things are getting called "cursor" here. queryset.iterator() isn't server-side row-by-row processing, it's one set-based query streamed to the client in chunks so a big export doesn't materialize in memory all at once, "get it in one go" is exactly the OOM it avoids.

the honest knock on the streaming kind isn't "bad design", it's that it holds a portal open server-side, which is why it can't survive transaction pooling. so the pooler-friendly fix isn't one giant fetch, it's keyset pagination (where id > last limit n), stateless and constant memory, which is what we moved those paths to.

Re: Why we built yet another Postgres connection pooler

#48

Post by levkk »

rubenvanwyk wrote: post #44

What really interests me most is the sharding and the possibility of using this for multitenancy - is the hooks / plugin architecture sufficient so you can run a small sidecar to add shards or tenants to the TOML file dynamically? Would be a game changer.

Yes. The plugin architecture supports this currently. That being said, you can use any number of sharding functions we support to do this too.

Re: Why we built yet another Postgres connection pooler

#50

Post by darkwater »

danudey wrote: post #11
petters wrote: post #3

> 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?

It's not unique to postgres, as others have said; the same thing can happen with e.g. MySQL poolers/proxies/etc., since the behavior of the connection can be changed dynamically and it persists for the lifetime of the connection. Example: legacy client A connects to MySQL via the bouncer and says 'I want all of our conversations to use latin-1, not utf-8'. This changes the character set that MySQL parses queries with…

> 3. Use a bouncer which can either disallow these changes or detect and revert them after the original client has disconnected. I'm not sure if this exists for MySQL at least.

I believe ProxySQL does exactly that:

* https://proxysql.com/documentation/mysql-prepared-statements...

* https://proxysql.com/documentation/multiplexing/

Post reply on HN