Re: Built-in connection pooling

On 05.05.2018 00:54, Merlin Moncure wrote:
> On Fri, May 4, 2018 at 2:25 PM, Robert Haas <robertmhaas@gmail.com> wrote:
>> On Fri, May 4, 2018 at 11:22 AM, Merlin Moncure <mmoncure@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
butnot 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