Emel Şimşek: PgBouncer Connection Pooler for Postgres Now Supports More Session Vars

PgBouncer is probably the most popular connection pooler for Postgres. It is essentially a transparant middleware between clients and the server. However, it is not %100 transparent in practice. There are a few intricacies that should be taken into account when using PgBouncer. One such consideration is that PgBouncer does not support the use of all session variables in transaction pooling mode. This lack of support is one of the reasons that the most commonly used transaction pooling mode is not fully compatible with Postgres. PgBouncer 1.20.0 started supporting two of the most requested session variables and laid ground work to be able to support all session variables in the future. Let’s break this down further. The Impact of Pooling Mode on Postgres Compatibility A connection pooler between the client and the server should ideally be completely transparent such that your application doesn’t have to be aware of the presence of a connection pooler. This is not the case with PgBouncer for all the pooling modes. There are three different connection pooling modes: Session Transaction Statement The pooling mode determines how the connections in the pool are assigned to the clients. The way this is done may impose some limitations impacting Postgres compatibility. In session pooling mode, there is a one-to-one mapping between the client and the pooled server connection throughout the lifetime of the client connection. Hence session pooling mode is the most compatible mode with Postgres. The clients can use session variables which are Postgres parameters whose values can be set per session. For instance, intervalstyle is a Postgres parameter with default value postgres. Now let’s say there are two clients connected to a Postgres server via a PgBouncer. In the session pooling mode, those clients can set intervalstyle to different values for their own session. client1=> SET intervalstyle = postgres_verbose; SET client1=> SHOW intervalstyle; IntervalStyle -----------[...]