Re: Built-in connection pooling

>>>> 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.

I just wanted to pointed out the counter fact against this.

>>> But them are not solving the main problem: restricting number of
>>> launched backends.

> 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.

I understand your customers like to have unlimited number of
connections. But my customers do not. (btw, even with normal
PostgreSQL, some of my customers are happily using over 1k, even 5k
max_connections).

>>> 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.

I agree with 1 -3.

> 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.

Not sure about 4. I rarely see such users around me.

> 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.

Rigt. I am happy with the limitation for now.

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

I am confused. If so, why do you want to push statement based or
transaction based built-in connection pooler?

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp