Find out 📣 who is speaking 📣 at POSETTE: An Event for Postgres 2024.
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
------------------
postgres_verbose
(1 row)
client2=> SET intervalstyle = sql_standard;
SET
client2=> SHOW intervalstyle;
IntervalStyle
---------------
sql_standard
(1 row)
PgBouncer clients override each other’s session variables
interval_style in the postgres backend. In 3rd and 4th steps, client2 which happens to be assigned the same backend due to transaction pooling mode, overrides interval_style. In 5th step, client1 sees the client2’s session variable unexpectedly.In transaction pooling mode, there is no guarantee that a server is assigned to the same client over the lifetime of the client. A server connection in the pool may serve multiple clients interchangeably. An assignment of a server connection lasts for the duration of a transaction. Afterwards the client may be assigned a different server connection for subsequent transactions.
This way of operation imposes some limitations one of which is that the session variables cannot be used reliably.
When a client changes a session parameter, it changes the session state on the server connection it is using. If another concurrent client is assigned the same server connection later, it will inadvertently see the first client’s session parameters. The interval_style example above would not work in transaction pooling mode.
# client1 changes the intervalstyle parameter to be postgres_verbose
client1=> SET intervalstyle = postgres_verbose;
SET
client1=> SHOW intervalstyle;
IntervalStyle
------------------
postgres_verbose
(1 row)
# client2 changes the intervalstyle parameter to be sql_standard
client2=> SET intervalstyle = sql_standard;
SET
client2=> SHOW intervalstyle;
IntervalStyle
---------------
sql_standard
(1 row)
# client1 session variable intervalstyle is overriten unexpectedly
client1=> SHOW intervalstyle;
IntervalStyle
---------------
sql_standard
(1 row)
Note that how the intervalstyle value for client1 is overwritten by client2.
The clients override each other’s session state whenever they change a parameter. This was a clearly listed restriction of transaction pooling mode until our fix.
The New track_extra_parameters Configuration Option
PgBouncer 1.20.0 Release introduced a new configuration option called track_extra_parameters. This is a list of Postgres parameters that clients can change for their session even in the Transaction Pooling mode. PgBouncer should be configured with this option to enable the use of session variables.
For instance, to enable the use of session variables extra_float_digits and intervalstyle, the following line should be added to the .ini file:
track_extra_parameters = extra_float_digits, intervalstyle
Note that only the parameters whose changed values are reported back by Postgres server can be tracked. When a client runs a SET query, Postgres responds with a CommandComplete message. For the parameters marked with GUC_REPORT flag internally, Postgres appends a ParameterStatus message which includes the new parameter value as well. PgBouncer relies on this feature to cache the values of parameters per client connection.
For instance search_path is not marked with GUC_REPORT as of Postgres 16.0. So it is not possible to track search_path if you have a vanilla Postgres server. However if you use Citus 12.0+, you can use search_path as a session variable when connecting through PgBouncer.
As the track_extra_parameters name implies, PgBouncer previously supported a set of session variables {client_encoding, DateStyle, TimeZone, standard_conforming_strings, application_name}. But this static list was not enough for the desired Postgres compatibility. Support request for intervalstyle came up frequently. Also Citus 12.0 added a new schema-based sharding feature which required the use of search_path session variable for clients. Therefore, rather than expanding the static list of supported variables, a more flexible solution is provided such that the clients can explicitly opt-in to track their variables.
For more PgBouncer updates, follow:
