Re: Built-in connection pooling

From: Konstantin Knizhnik <k(dot)knizhnik(at)postgrespro(dot)ru>
To: Merlin Moncure <mmoncure(at)gmail(dot)com>, Robert Haas <robertmhaas(at)gmail(dot)com>
Cc: Christophe Pettus <xof(at)thebuild(dot)com>, Heikki Linnakangas <hlinnaka(at)iki(dot)fi>, Nikolay Samokhvalov <samokhvalov(at)gmail(dot)com>, PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Re: Built-in connection pooling
Date: 2018-05-08 11:33:48
Message-ID: 0aeb5ecd-d3bf-8a0b-5a57-f4df36e250b3@postgrespro.ru
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Robert Haas 2018-05-08 11:52:07 Re: Having query cache in core
Previous Message Andrew Gierth 2018-05-08 11:23:13 Re: Bug Report: Error caused due to wrong ordering of filters