Re: Built-in connection pooling

On 19.01.2018 19:59, Tomas Vondra wrote:
> The problem can be much easily solved in case of using pthread version
>> of Postgres. In this case reassigning session to another executor
>> (thread) can be don much easily.
>> And there is no need to use unportable trick with passing fiel
>> descriptor to other process.
>> And in future I am going to combine them. The problem is that pthread
>> version of Postgres is still in very raw state.
>>
> Yeah. Unfortunately, we're using processes now, and switching to threads
> will take time (assuming it happens at all).
I have to agree with you.

>>> 3) Is there any sort of shrinking the pools? I mean, if the backend is
>>> idle for certain period of time (or when we need backends for other
>>> databases), does it get closed automatically?
>> When client is disconnected, client session is closed. But backen is not
>> terminated even if there are no more sessions at this backend.
>> It  was done intentionally, to avoid permanent spawning of new processes
>> when there is one or few clients which frequently connect/disconnect to
>> the database.
> Sure, but it means a short peak will exhaust the backends indefinitely.
> That's acceptable for a PoC, but I think needs to be fixed eventually.
Sorry, I do not understand it.
You specify size of backends pool which will server client session.
Size of this pool is chosen to provide the best performance at the
particular system and workload.
So number of backends will never exceed this optimal value even in case
of "short peak".
From my point of view terminating backends when there are no active
sessions is wrong idea in any case, it was not temporary decision just
for PoC.

>
> Well, I haven't said it has to be single-threaded like pgbouncer. I
> don't see why the bgworker could not use multiple threads internally (of
> course, it'd need to be not to mess the stuff that is not thread-safe).
>

Certainly architecture with N multiple scheduling bgworkers and M
executors (backends) may be more flexible
than solution when scheduling is done in executor itself. But we will
have to pay extra cost for redirection.
I am not sure that finally it will allow to reach better performance.
More flexible solution in many cases doesn't mean more efficient solution.

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