Re: Connection pool branch merged in psycopg3

On 12/03/21, Daniele Varrazzo (daniele(dot)varrazzo(at)gmail(dot)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