undefined | Better HN

0 pointsDiggsey4y ago0 comments

You may be right that it's easy to set up, but pgbouncer doesn't help with this problem most of the time. It's a problem that needs to be solved within postgres.

There are three pooling modes:

- Session pooling. Doesn't help with this issue since it doesn't reduce the total number of required connections.

- Transaction pooling / statement pooling. Breaks too many things to be usable. (eg. prepared statements...)

See the table at https://www.pgbouncer.org/features.html for what features cannot be used with transaction pooling.

0 comments

megous4y ago

Personally I don't expect this to be ever improved in PostgreSQL (ie. change from process per connection model to something else), so I design my multi-user apps so that everything works fine with session pooling (quick short sessions/connections to pgbouncer) and connections that wait for NOTIFY get made directly to postgresql server, and are also limited in number.

And it works fine on low-resourced machines that I tend to use for everything.

DiggseyOP4y ago

Switching to a threaded model would be a lot of work, but there is a simpler solution that retains most of the benefits: using a process-per-connection model for active connections only, and allowing a single process to have multiple idle connections.

I follow the mailing list because I'm interested in this exact issue. Konstantin Knizhnik sent a patch implementing a built-in connection pooler in early 2019 that uses a similar approach to what I just described. The work on that has continued to this day, and I'm hopeful that it will eventually be merged.

megous4y ago

But how's that different from pgbouncer?

EDIT: I see, it would have session state restore, not just DISCARD like pgbouncer.

j / k navigate · click thread line to collapse

0 comments

megous4y ago

And it works fine on low-resourced machines that I tend to use for everything.

DiggseyOP4y ago

megous4y ago

But how's that different from pgbouncer?

EDIT: I see, it would have session state restore, not just DISCARD like pgbouncer.

j / k navigate · click thread line to collapse