Re: Built-in connection pooling

On 18.04.2018 13:36, Heikki Linnakangas wrote:
> On 18/04/18 06:10, Konstantin Knizhnik wrote:
>> But there are still use cases which can not be covered y external
>> connection pooler.
>
> Can you name some? I understand that the existing external connection
> poolers all have their limitations. But are there some fundamental
> issues that can *only* be addressed by a built-in implementation?

Well, may be I missed something, but i do not know how to efficiently
support
1. Temporary tables
2. Prepared statements
3. Sessoin GUCs
with any external connection pooler (with pooling level other than session).

The problem with GUCs seems to be the easiest from this thee: we can
just keep list of GUC assignments and prepend it to each statement. But
it is not so efficient and can cause some problems (for example there
are some statements, which can not be executed in multistatement context).

Prepared statement problem can be fixed either by implementing shared
plan cache, either by autoprepare (I have proposed patch for it).

But  concerning temporary table I do not know any acceptable solution.

>
> For the record, I think an internal connection pool might be a good
> idea. It would presumably be simpler to set up than an external one,
> for example. But it depends a lot on the implementation. If we had an
> internal connection pool, I would expect it to be very transparent to
> the user, be simple to set up, and not have annoying limitations with
> prepared statements, temporary tables, etc. that the existing external
> ones have.
>
> However, I suspect that dealing with *all* of the issues is going to
> be hard and tedious. And if there are any significant gaps, things
> that don't work correctly with the pooler, the patch will almost
> certainly be rejected.
>
> I'd recommend that you put your effort in improving the existing
> external connection poolers. Which one is closest to suit your needs?
> What's missing?

Yandex team is following this approach with theirOdysseus (multithreaded
version of pgbouncer with many of pgbouncer issues fixed).
But it will not work for 1C which needs to keeps sessions (with
temporary tables, e.t.c) for large number of clients which never closes
connections.

>
> There are probably things we could do in the server, to help external
> connection poolers. For example, some kind of a proxy authentication,
> where the connection pooler could ask the backend to do authentication
> on its behalf, so that you wouldn't need to re-implement the
> server-side authentication code in the external pooler. Things like that.

As far as I know most of DBMSes have some kind of internal connection
pooling.
Oracle, for example, you can create dedicated and non-dedicated backends.
I wonder why we do not want to have something similar in Postgres.
Any external connection pooler will be less convenient for users than
internal pooler.
It may be more flexible, more error protected, more scalable, .... But
still it is an extra entity which adds extra overhead and can also be
bottleneck or SPoF.

--
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company