Re: Built-in connection pooling

On 27.04.2018 23:43, Merlin Moncure wrote:
> On Fri, Apr 27, 2018 at 11:44 AM, Konstantin Knizhnik
> <k(dot)knizhnik(at)postgrespro(dot)ru> wrote:
>>
>> On 27.04.2018 18:33, Merlin Moncure wrote:
>>> On Fri, Apr 27, 2018 at 10:05 AM, Konstantin Knizhnik
>>> <k(dot)knizhnik(at)postgrespro(dot)ru> wrote:
>>>> On 27.04.2018 16:49, Merlin Moncure wrote:
>>> I'm confused here...could be language issues or terminology (I'll look
>>> at your latest code). Here is how I understand things:
>>> Backend=instance of postgres binary
>>> Session=application state within postgres binary (temp tables,
>>> prepared statement etc)
>>> Connection=Client side connection
>> Backend is a process, forked by postmaster.
> right, we are saying the same thing here.
>
>>> AIUI (I could certainly be wrong), withing connection pooling, ratio
>>> of backend/session is still 1:1. The idea is that client connections
>>> when they issue SQL to the server reserve a Backend/Session, use it
>>> for the duration of a transaction, and release it when the transaction
>>> resolves. So many client connections share backends. As with
>>> pgbouncer, the concept of session in a traditional sense is not really
>>> defined; session state management would be handled within the
>>> application itself, or within data within tables, but not within
>>> backend private memory. Does that align with your thinking?
>> No. Number of sessions is equal to number of client connections.
>> So client is not reserving "Backend/Session" as it happen in pgbouncer.
>> One backend keeps multiple sessions. And for each session it maintains
>> session context which included client's connection.
>> And it is backend's decision transaction of which client it is going to
>> execute now.
>> This is why built-in pooler is able to provide session semantic without
>> backend/session=1:1 requirement.
> I see. I'm not so sure that is a good idea in the general sense :(.
> Connection sharing sessions is normal and well understood, and we have
> tooling to manage that already (DISCARD). Having the session state
> abstracted out and pinned to the client connection seems complex and
> wasteful, at least sometimes. What _I_ (maybe not others) want is a
> faster pgbouncer that is integrated into the database; IMO it does
> everything exactly right.
Yandex's Odyssey is faster version of pgbouncer (supporting
multithreading and many other things).
Why do you need to integrate it in Postgres if you do not want to
preserve session semantic? Just to minimize efforts needed to maintain
extra components?
But in principle, pooler can be distributed as Postgres extension and is
started as background worker.
Will it help to eliminate administration overhead of separate page pool?

In any case, my built-in pooler isoriented on application which needs
session sementic (using temporary tables, GUCs, prepared statements,...)
As I many time mentioned, is is possible to provide it only inside
database, not in some external pooler, doesn't matter which architecture
it has.

>
> merlin

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