Feature idea: Add a pooling mode that can support both transaction and session pooling

[JelteF]

When using PgBouncer as a transparent proxy, in many usecases you will want to support both session and transaction pooling. Transaction pooling for the OLTP workload, but session pooling for connections that require features that are not supported in transaction pooling. This requires creating two pools for the same postgres database, one in transaction pooling and one in session pooling mode (usually these run on two different processes that listen on two different ports).

This works but it has a big downside. The server connections are not shared between these two pools, this makes choosing the pool_size quite difficult. i.e. how do you divide the total connections that you want Postgres to get between transaction and session pooling. This heavily depends on your workload, and this might even change throughout the day.

It would be much easier if we could choose a pooling mode for a pool that caused it to support both session and transaction pooling. The main question then becomes: How do you decide if a client wants a session pooling connection or a transaction pooling connection?
I can think of two ideas:

1. Allow pgbouncer to listen on two ports, a session and a transaction one. And then assign the server connection to a client for the whole session or just the transaction based on the port that the client connected to.
2. Create a pgbouncer specific startup parameter to configure it. This parameter can then either be passed in options or directly. Probably we'd want to default to session mode. Something like this:

PGOPTIONS='-c pgbouncer.pool_mode=session' psql 'port=6432'
PGOPTIONS='-c pgbouncer.pool_mode=transaction' psql 'port=6432'
# Would even work with statement pooling (even though I still don't understand its purpose)
PGOPTIONS='-c pgbouncer.pool_mode=statement' psql 'port=6432'

I like the explicitness of option 2, and I have a feeling it would be easier to implement too.

But option 1 I like because it can be a drop-in replacement for existing setups that already use the two ports approach to run both a transaction pooler and a session pooler. So no client connection strings would need to be changed.

[JelteF]

Relevant pgsql-hackers thread that would allow updating the pgbouncer.pool_mode parameter for clients after their initial connection. Which could be useful for multilayered PgBouncer setups.

https://www.postgresql.org/message-id/flat/CAGECzQTg2hcmb5GaU53uuWcdC7gCNJFLL6mnW0WNhWHgq9UTgw%40mail.gmail.com