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

21–30 of 74 posts

Re: Why we built yet another Postgres connection pooler

#21

Post by drdexebtjl »

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…

> 4. Use separate bouncers for each application that might be different (extension of #1)

I wonder if clients send something equivalent to a User-Agent, such that the connection pooler could assign them to different pools automatically.

Re: Why we built yet another Postgres connection pooler

#22

Post by tpetry »

drdexebtjl wrote: post #21
danudey wrote: post #11

Earlier quoted context omitted.

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…

> 4. Use separate bouncers for each application that might be different (extension of #1) I wonder if clients send something equivalent to a User-Agent, such that the connection pooler could assign them to different pools automatically.

This exists, its called a user. You just use different database users for this.

Re: Why we built yet another Postgres connection pooler

#23

Post by tpetry »

khurs wrote: post #13
merb wrote: post #8

Earlier quoted context omitted.

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

>might bringt Postgres finally closer to vitess Supabase are launching a Vitess for Postgresql, they have hired the original creator of Vitess for it https://supabase.com/blog/multigres-vitess-for-postgres

And Planetscale (who is the current primary maintainer of vitess) is developing a Vitess for PostgreSQL.

There will be a couple of production-grade PG vitess solutions the next months.

Re: Why we built yet another Postgres connection pooler

#24

Post by nijave »

drdexebtjl wrote: post #21
danudey wrote: post #11

Earlier quoted context omitted.

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…

> 4. Use separate bouncers for each application that might be different (extension of #1) I wonder if clients send something equivalent to a User-Agent, such that the connection pooler could assign them to different pools automatically.

Can also use different database names since pgbouncer lets you remap that

Re: Why we built yet another Postgres connection pooler

#27

Post by HackerThemAll »

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.

Re: Why we built yet another Postgres connection pooler

#29

Post by [deleted] »

tpetry wrote: post #23
khurs wrote: post #13

Earlier quoted context omitted.

>might bringt Postgres finally closer to vitess Supabase are launching a Vitess for Postgresql, they have hired the original creator of Vitess for it https://supabase.com/blog/multigres-vitess-for-postgres

And Planetscale (who is the current primary maintainer of vitess) is developing a Vitess for PostgreSQL. There will be a couple of production-grade PG vitess solutions the next months.

[deleted]

Re: Why we built yet another Postgres connection pooler

#30

Post by levkk »

27183 wrote: post #28

It's awesome to see AGPL instead of the horrible BSL variants that have been going around.

Thanks! We try to be very open and explicit about why we chose AGPL. Personally, I like it because it's an extension of GPL, which is a huge reason why I was able to self-teach programming. Just trying to give back.
Post reply on HN