Re: Built-in connection pooling

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.

> However, I think that's probably worrying about the wrong end of the
> problem first. IMHO, what we ought to start by doing is considering
> what a good architecture for this would be, and how to solve the
> general problem of per-backend session state. If we figure that out,
> then we could worry about optimizing whatever needs optimizing, e.g.
> memory usage.

Exactly -- being able to manage down resource consumption by
controlling session count is a major feature that ought not to be
overlooked. So I'm kind of signalling that if given a choice between
that (funneling a large pool of connections down to a smaller number
of backends) and externalized shared sessions I'd rather have the
funnel; it solves a number of very important problems with respect to
server robustness. So I'm challenging (in a friendly, curious way) if
breaking session:backend 1:1 is really a good idea. Maybe a
connection pooler implementation can do both of those things or it's
unfair to expect an implementation to do both of them.

merlin