Re: Built-in connection pooling

On Wed, Apr 25, 2018 at 9:43 AM, Christophe Pettus <xof(at)thebuild(dot)com> wrote:
>
>> On Apr 25, 2018, at 07:00, Merlin Moncure <mmoncure(at)gmail(dot)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