PgBouncer Now Supports Prepared Statements in Transaction and Statement Pooling Modes

Overview:

This guide shows how to enable support for prepared statements in the transaction and statement pooling mode in the Pgbouncer connection pooler.

What you need to know

PgBouncer can now track prepared statements in transaction pooling mode and ensure they are prepared on the fly on the associated server connection, as of version 1.21.0. To activate this functionality, set the max_prepared_statements to a non-zero number, as shown below.

Important: PgBouncer versions prior to 1.21.0 only support prepared statements in the session pooling mode. The only workaround for versions below 1.21.0 is deactivating prepared statements on the client side when running Pgbouncer in the transaction or statement pooling mode.

If you already have Pgbouncer version 1.21 installed, skip the installation/upgrade version section. Also, use the sudo command where necessary while running the commands in this guide if you are not administering the system as the root user.

Installing/Upgrading to Pgbouncer 1.21

As mentioned above, the “max_prepared_statements” configuration parameter is supported in Pgbouncer version 1.21. The Debian official repository has an older version of Pgbouncer. To install version 1.21, which is the latest(at the time of writing), you must enable the PostgreSQL official repository on your system.

For the purpose of this guide, we demonstrate using a Debian and Ubuntu system. Start by creating the file repository configuration, like this:

#sh -c 'echo "deb https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

Then import the repository signing key by running this command:

#wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add -

Next, update the local package index as follows:

#apt update

Now check the details of the Pgbouncer version pointed to by the apt package manager as follows:

#apt show pgbouncer

Then install the Pgbouncer package like this:

#apt install pgbouncer

During the package installation process, if you already have a configuration file, the package manager will prompt you to either install the package maintainer’s version of the configuration file or keep your currently installed version. Read the options and select the correct option.

We recommend keeping your currently-installed version if your Pgbouncer service is already running especially in a production environment.

Adding max_prepared_statements in Pgbouncer Configuration File

Now open your Pgbouncer configuration file and add the max_prepared_statements parameter as shown in the following screenshot. In this example, I have set the value of the parameter to 20. You need to set this value based on the requirements of your application.

#vim /etc/pgbouncer/pgbouncer.ini

Save and close the file. Then restart the pgbouncer service to apply the new configuration:

# systemctl restart pgbouncer.service

Note: If you add the max_prepared_statements parameter in your configuration file for a Pgbouncer version lower than 1.21, you will encounter the error message shown in the screenshot that follows. The pgbouncer service will not run.

Conclusion

That’s all I prepared for you. In this guide, I have shown how to enable support for prepared statements in transaction and statement pooling mode in Pgbouncer. If you have any queries, send them to me via the feedback form below.

Reference:

• https://www.pgbouncer.org/faq.html
• https://www.pgbouncer.org/config.html#max_prepared_statements.