Re: Built-in connection pooling - Mailing list pgsql-hackers

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 their Odysseus  (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