Re: Built-in connection pooling

On 19.01.2018 20:28, Tomas Vondra wrote:
>>
>> With pgbouncer you will never be able to use prepared statements which
>> slows down simple queries almost twice (unless my patch with
>> autoprepared statements is committed).
>>
> I don't see why that wouldn't be possible? Perhaps not for prepared
> statements with simple protocol, but I'm pretty sure it's doable for
> extended protocol (which seems like a reasonable limitation).
>
> That being said, I think it's a mistake to turn this thread into a
> pgbouncer vs. the world battle. I could name things that are possible
> only with standalone connection pool - e.g. pausing connections and
> restarting the database without interrupting the clients.
>
> But that does not mean built-in connection pool is not useful.
>
>
> regards
>

Sorry, I do not understand how extended protocol can help to handle
prepared statements without shared prepared statement cache or built-in
connection pooling.
The problems is that now in Postgres most of caches including catalog
cache, relation cache, prepared statements cache are private to a backend.
There is certainly one big advantage of such approach: no need to
synchronize access to the cache. But it seems to be the only advantage.
And there are a lot of drawbacks:
inefficient use of memory, complex invalidation mechanism, not
compatible with connection pooling...

So there are three possible ways (may be more, but I know only three):
1. Implement built-in connection pooling which will be aware of proper
use of local caches. This is what I have implemented with the proposed
approach.
2. Implicit autoprepare. Clients will not be able to use standard
Postgres prepare mechanism, but executor will try to generate generic
plan for ordinary queries. My implementation of this approach is at
commit fest.
3. Global caches. It seems to be the best solution but the most
difficult to implement.

Actually I think that the discussion about the value of built-in
connection pooling is very important.
Yes, external connection pooling is more flexible. It allows to perform
pooling either at client side either at server side (or even combine two
approaches).
Also external connection pooling for PostgreSQL is not limited by
pgbouncer/pgpool.
There are many frameworks maintaining their own connection pool, for
example J2EE, jboss, hibernate,...
I have a filling than about 70% of enterprise systems working with
databases are written in Java and doing connection pooling in their own way.
So may be embedded connection pooling is not needed for such applications...
But what I have heard from main people is that Postgres' poor connection
pooling is one of the main drawbacks of Postgres complicating it's usage
in enterprise environments.

In any case please find updated patch with some code cleanup and more
comments added.

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