PgBouncer Now Supports Prepared Statements in Transaction and Statement Pooling Modes


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 $(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 - | 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
View PgBouncer version in package details

Then install the Pgbouncer package like this:

#apt install pgbouncer
Install PgBouncer version 1.21

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.

Package configuration file modification prompt
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
Sample Pgbouncer configuration file parameters

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.

Pgbouncer configuration error

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.

Spread the love

You may also like...

2 Responses

  1. I do not even understand how I stopped up right here, however I believed this publish was great. I do not understand who you’re however definitely you’re going to a well-known blogger should you aren’t already. Cheers!

Leave a Reply

Your email address will not be published. Required fields are marked *