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(at)gmail(dot)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