On Wed, Apr 25, 2018 at 9:43 AM, Christophe Pettus <x...@thebuild.com> wrote: > >> On Apr 25, 2018, at 07:00, Merlin Moncure <mmonc...@gmail.com> wrote: >> The limitations headaches that I suffer with pgbouncer project (which >> I love and use often) are mainly administrative and performance >> related, not lack of session based server features. > > For me, the most common issue I run into with pgbouncer (after general > administrative overhead of having another moving part) is that it works at > cross purposes with database-based sharding, as well as useful role and > permissions scheme. Since each server connection is specific to a > database/role pair, you are left with some unappealing options to handle that > in a pooling environment.
Would integrated pooling help the sharding case (genuinely curious)? I don't quite have my head around the issue. I've always wanted pgbouncer to be able to do things like round robin queries to non-sharded replica for simple load balancing but it doesn't (yet) have that capability. That type of functionality would not fit into in in-core pooler AIUI. Totally agree that the administrative benefits (user/role/.conf/etc/etc) is a huge win. > The next most common problem are prepared statements breaking, which > certainly qualifies as a session-level feature. Yep. The main workaround today is to disable them. Having said that, it's not that difficult to imagine hooking prepared statement creation to a backend starting up (feature: run X,Y,Z SQL before running user queries). This might be be less effort than, uh, moving backend session state to a shareable object. I'll go further; managing cache memory consumption (say for pl/pgsql cached plans) is a big deal for certain workloads. The only really effective way to deal with that is to manage the server connection count and/or recycle server connections on intervals. Using pgbouncer to control backend count is a very effective way to deal with this problem and allowing virtualized connections to each mange there independent cache would be a step in the opposite direction. I very much like having control so that I have exactly 8 backends for my 8 core server with 8 copies of cache. Advisory locks are a completely separate problem. I suspect they might be used more than you realize, and they operate against a very fundamental subsystem of the database: the locking engine. I'm struggling as to why we would take another approach than 'don't use the non-xact variants of them in a pooling environment'. merlin
