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
