Re: Built-in connection pooling

On 03.05.2018 20:01, Robert Haas wrote:
> On Fri, Apr 27, 2018 at 4:43 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
>> What _I_ (maybe not others) want is a
>> faster pgbouncer that is integrated into the database; IMO it does
>> everything exactly right.
> I have to admit that I find that an amazing statement.  Not that
> pgbouncer is bad technology, but saying that it does everything
> exactly right seems like a vast overstatement.  That's like saying
> that you don't want running water in your house, just a faster motor
> for the bucket you use to draw water from the well.
>
May be if you are engaged in agriculture at your country house, then 
having a well with good motor pump is better for watering of plants than 
water faucet at your kitchen.
But most of homeowners prefer to open a tapto wash hands rather than 
perform some complex manipulations with motor pump.

I absolutely sure that external connection poolers will always have 
their niche: them can be used as natural proxy between multiple clients 
and DBMS.
Usually HA/load balancing also can be done at this level.

But there are many cases when users just do not want to worry about  
connection pooling: them just has some number of clients (which can be 
larger enough and several times larger than optimal number of Postgres 
backends) and them want them to access database without introducing some 
intermediate layers. In this case built-in connection pooler will be the 
ideal solution.

This is from user's point of view. From Postgres developer's point of 
view, built-in pooler has  some technical advantages comparing with 
external pooler.
Some of this advantages can be eliminated by significant redesign of 
Postgres architecture, for example introducing shared cache of prepared 
statements...
But in any case, the notion of session context  and possibility to 
maintain larger number of opened sessions will always be topical.


Some update on status of built-in connection pooler prototype: I managed 
to run regression and isolation tests for pooled connections.
Right now  6 of 185 tests failed are failed for regression tests and 2 
of 67 tests failed for isolation tests.
For regression tests result may vary depending on parallel schedule, 
because of manipulations with roles/permissions which are not currently 
supported.
The best results are for sequential schedule: 5 failed tests: this 
failures caused by differences in pg_prepared_statements caused by 
"mangled" prepared names.

Failures of isolation tests are caused by unsupported advisory locks.

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