On 05.05.2018 00:54, Merlin Moncure wrote:
On Fri, May 4, 2018 at 2:25 PM, Robert Haas <robertmh...@gmail.com> wrote:
On Fri, May 4, 2018 at 11:22 AM, Merlin Moncure <mmonc...@gmail.com> wrote:
If we are breaking 1:1 backend:session relationship, what controls
would we have to manage resource consumption?
I mean, if you have a large number of sessions open, it's going to
take more memory in any design. If there are multiple sessions per
backend, there may be some possibility to save memory by allocating it
per-backend rather than per-session; it shouldn't be any worse than if
you didn't have pooling in the first place.
It is absolutely worse, or at least can be. plpgsql plan caches can
be GUC dependent due to search_path; you might get a different plan
depending on which tables resolve into the function. You might
rightfully regard this as an edge case but there are other 'leakages',
for example, sessions with different planner settings obviously ought
not to share backend plans. Point being, there are many
interdependent things in the session that will make it difficult to
share some portions but not others.
Right now, in my built-in connection pool implementation there is shared
prepared statements cache for all sessions in one backend,
but actually each session has its own set of prepared statements. I just
append session identifier to prepared statement name to make it unique.
So there is no problem with different execution plans for different
clients caused by specific GUC settings (like enable_seqscan or
max_parallel_workers_per_gather).
But the primary reason for such behavior is to avoid prepared statements
name conflicts between different clients.
From my point of view, there are very few cases when using
client-specific plans has any sense.
In most cases, requirement is quite opposite: I want to be able to
prepare execution plan (using missed in Postgres hints, GUCs, adjusted
statistic,...) which will be used by all clients.
The most natural and convenient way to achieve it is to use shared plan
cache.
But shared plan cache is a different story, not directly related with
connection pooling.\
Point being, there are many interdependent things in the session that will make
it difficult to share some portions but not others.
I do not see so much such things... Yes, GUCs can affect behavior within
session. But GUCs are now supported: each session can have its own set
of GUCs.
Prepared plans may depend on GUCs, but them are also private for each
session now. What else?
And in any case, with external connection pooler you are not able to use
session semantic at all: GUCs, prepared statements, temporary table,
advisory locks,...
with built-in connection pooler you can use sessions but with some
restrictions (lack of advisory locks, for example). It is better than
nothing, isn't it?
--
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
