Re: Built-in connection pooling

On 25.04.2018 20:02, Merlin Moncure wrote:
>
> 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.

Yes, pgbpouncer is not intended to balance workload.
You should use ha-proxy or pg-pool. libpq now allow tp specify multiple
URLs, but unfortunately right now libpq is not able to perform load
balancing.
I  do not understand how it is related with integrating connection pooling.
Such pooler definitely shound be external if you want to scatter queries
between different nodes.

>> 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).

Sorry, I do not completely understand your idea.
Yes, it is somehow possible to simulate session semantic by prepending
all session specific commands (mostly setting GUCs) to each SQL statements.
But it doesn't work for prepared statements: the idea of prepared
statements is that compilation of statement should be done only once.

> 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.

Database performance is mostly limited by disk, so optimal number of
backends may be different from number of cores.
But certainly possibility to launch "optimal" number of backends is one
of the advantages of builtin session pooling.

>
> 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

--
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company