On 12/03/21, Daniele Varrazzo (daniele.varra...@gmail.com) wrote: > I have just merged the connection pool branch to main in psycopg3. I > am very happy about this object and I think it's an immense > improvement compared to psycopg2 pool. The documentation is already > online: > > - behaviour description: > https://www.psycopg.org/psycopg3/docs/advanced/pool.html
Hi Daniele Thanks for the really well written description. I've got a few comments having just been through some hairy rewiring of pgbouncer pools on a busy production system. First of all the pool stats sound great. requests_waiting and requests_wait_ms are pretty crucial to have when things are going wrong. pgbouncer has a reserve_pool concept with an associated reserve_pool_timeout. These are rather odd because although you can help "iron out" an initial spike of connections, after they are in the pool they full pool is available until the equivalent of max_idle. I'm unconvinced by their purpose so (although I may well be wrong) aren't worth having. I see that the max_size/pool_max settings are quite different from maximum database connections, with connections that cannot be contained in the pool queuing for a place in the pool, and that these "non-pooled" connections are controlled through timeout, max_waiting and so on. That sounds really good too. I was going to suggest a max_total_connections but max_waiting kind of does that for you when put together with max_size etc. Maximum connections is of considerable interest where there are more than 1 app connecting to the database. The combined result of many connections can quickly become devastating for the database server (as the Oracle video you linked to neatly shows). One major performance boost we receive, in areas where our code can support it, is pgbouncer "transaction" rather than "session" pools. The "session" pool type keeps a per-client connection for the lifetime of the client's statements or until the pool connection timeout is up. This is important where "set search_path = xyz;" and "select * from fn_get_complex_data;" are issued in sequence and the second statement needs to depend on the previous one. The alternative (since our code doesn't support the third, "statement", mode) of transaction pool mode gives us very high performance through connection re-use at a transaction level. (I found the heroku explanation of this quite helpful, at https://devcenter.heroku.com/articles/best-practices-pgbouncer-configuration). Is it worth considering pool types in this way? Regards Rory
