3 ms·
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
by Diggsey 5y ago
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 https://www.pgbouncer.org/features.html for what features cannot be used with transaction pooling.
- megous 5y agoPersonally 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.
- Diggsey 5y agoSwitching 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.
- megous 5y agoBut how's that different from pgbouncer? EDIT: I see, it would have session state restore, not just DISCARD like pgbouncer.