Re: Built-in connection pooling

On 20.04.2018 11:16, Tatsuo Ishii wrote:
>> On 20.04.2018 01:58, Tatsuo Ishii wrote:
>>>> I think there's plenty things that don't really make sense solving
>>>> outside of postgres:
>>>> - additional added hop / context switches due to external pooler
>>> This is only applied to external process type pooler (like Pgpool-II).
>>>
>>>> - temporary tables
>>>> - prepared statements
>>>> - GUCs and other session state
>>> These are only applied to "non session based" pooler; sharing a
>>> database connection with multiple client connections. "Session based"
>>> connection pooler like Pgpool-II does not have the shortcomings.
>> But them are not solving the main problem: restricting number of
>> launched backends.
> Pgpool-II already does this. If number of concurrent clients exceeds
> max_connections, max_connections+1 client have to wait until other
> client disconnect the session. So "restricting number of launched
> backends" is an indenpendet function from whether "session based"
> connection poolers" is used or not.
Sorry, but delaying new client connection until some other client is
disconnected is not an acceptable solution in most cases.
Most of customers want to provide connections to the database server for
unlimited (or at least > 100) number of clients.
And this clients used to keep connection alive and do not hangout after
execution of each statement/transaction.
In this case approach with session pooling dopesn't work.

>
>> Pgbouncer  also can be used in session pooling mode. But  it makes
>> sense only if there is limited number of clients which permanently
>> connect/disconnect to the database.
>> But I do not think that it is so popular use case. Usually there is
>> very large number of connected clients which rarely drop connection
>> but only few of them are active at each moment of time.
> Not neccessarily. i.e. Session based poolers allow to use temporary
> tables, prepared statements and keep GUCs and other session state,
> while non session based poolers does not allow to use them.
>
> So choosing "session based poolers" or "non session based poolers" is
> a trade off. i.e. let user choose one of them.
>
> If you are willing to merge your connection pooler into core, I would
> suggest you'd better to implement those pool modes.

Sorry, may we do not understand each other.
There are the following facts:
1. There are some entities in Postgres which are local to a backend:
temporary tables, GUCs, prepared statement, relation and catalog caches,...
2. Postgres doesn't "like"  larger number of backends. Even only few of
them are actually active. Large number of backends means large
procarray, large snapshots,...
Please refere to my measurement at the beginning of this thread which
illustrate how performance of Pastgres degrades with increasing number
of backends.
3. Session semantic (prepared statements, GUCs, temporary tables) can be
supported only in session level pooling mode.
4. This mode is not acceptable in most cases because it is not possible
to limit number of clients which want to establish connection wither
database server or keep it small.
This is why most pgbouncer users are using statement pooling mode.
5. It doesn't matter how you manged to implement pooling outside
Postgres: if you want to preserve session semantic, then you need to
spawn as much backends as sessions. And number of clients is limited by
number of backends/sessions.

The primary idea and main benefit of built-in connection pooler is to
support session semantic with limited number of backends.

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