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