When leveraging PostgreSQL for your application, PgBouncer is the go-to connection pooler for high concurrent workloads. You might already be aware of the different pooling modes available on PgBouncer and their uses. If you are unfamiliar with PgBouncer, this post would help you get up to speed. In most setups, PgBouncer is used in transaction pooling mode. And if one intends to use prepared statements for better performance with transaction pooling, they would soon realize that PgBouncer isn’t that great for this use case, and will cause errors/issues. In this blog, we’ll look at what challenge we had encountered in such a setup, how we solved it, as well as some of the other potential alternatives that you could consider, and also discuss how to verify effectiveness of the changes that we do.
In our case, we were migrating a few databases from OpenShift Containers to On-Premise. We are leveraging PgBouncer as the connection pooler, and configured pool_mode=transaction as it is a banking application. The client was using liquibase to generate prepared statements in one of the databases. After successful migration of that particular database to the new environment and mapping the application to the target database, we have observed a few errors related to a prepared statement as shown below:
ERROR: prepared statement "test" already existsHow we solved it?
The issue was that pool_mode was set to transaction, but a prepared statement works at session level. In this case, we either need to disable prepared statement, or set the pool_mode to session for that particular database. Let us run a simple prepared statement in both of these modes .
How to set multiple pool_modes in pgbouncer?
We can set different pool_mode for different database based on the requirement. The following pgbouncer.ini configuration file illustrates the setup:
#Go to the PgBouncer configuration file.
vi /etc/pgbouncer/pgbouncer.ini
[databases]
* = host=localhost port=5433 auth_user=postgres
test_db = host=localhost port=5433 auth_user=postgres pool_mode=session
[pgbouncer]
listen_addr = *
listen_port = 6432
admin_users = postgres
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 100
default_pool_size = 20We can set a different pool_mode for different databases as shown above. Here, we have set the default pool_mode as transaction for all the databases, but for the test_db , it was set to session. If you have already set pool_mode to transaction and need to change the pool_mode to session , add a new row as we did for test_db above and reload the pgbouncer. This will not affect the current connections.
After making the changes, you could verify the pool_mode being used as follows:
#Here is the output of pools we had
pgbouncer=# show pools;
-[ RECORD 1 ]---------+------------
database | pgbouncer
user | pgbouncer
cl_active | 1
cl_waiting | 0
cl_active_cancel_req | 0
cl_waiting_cancel_req | 0
sv_active | 0
sv_active_cancel | 0
sv_being_canceled | 0
sv_idle | 0
sv_used | 0
sv_tested | 0
sv_login | 0
maxwait | 0
maxwait_us | 0
pool_mode | statement
load_balance_hosts |
-[ RECORD 2 ]---------+------------
database | test_db
user | postgres
cl_active | 0
cl_waiting | 0
cl_active_cancel_req | 0
cl_waiting_cancel_req | 0
sv_active | 0
sv_active_cancel | 0
sv_being_canceled | 0
sv_idle | 0
sv_used | 1
sv_tested | 0
sv_login | 0
maxwait | 0
maxwait_us | 0
pool_mode | session
load_balance_hosts |
-[ RECORD 3 ]---------+------------
database | test
user | postgres
cl_active | 0
cl_waiting | 0
cl_active_cancel_req | 0
cl_waiting_cancel_req | 0
sv_active | 0
sv_active_cancel | 0
sv_being_canceled | 0
sv_idle | 0
sv_used | 1
sv_tested | 0
sv_login | 0
maxwait | 0
maxwait_us | 0
pool_mode | transaction
load_balance_hosts |If we observe the output of show pools , it says the database test_db is having pool_mode as session and database test is having pool_mode as transaction
Running prepared statement in transaction pool_mode
Let’s run a simple prepared statement in database test with pool_mode set to transaction.
#Running the prepare statement and execute first time
[postgres@ip-172-31-94-225 ~]$ psql -h localhost -p 6432 -U postgres -d test -c "PREPARE stmtc AS SELECT 1;EXECUTE stmtc;"
Password for user postgres:
PREPARE
?column?
----------
1
(1 row)
#Running the prepare statement and execute second time
[postgres@ip-172-31-94-225 ~]$ psql -h localhost -p 6432 -U postgres -d test -c "PREPARE stmtc AS SELECT 1;EXECUTE stmtc;"
Password for user postgres:
ERROR: prepared statement "stmtc" already existsHere, we ran same set of commands two times and see the error message in the second time because when the pool_mode is set to transaction, PgBouncer ends the backend client connection once the transaction is committed, and would not allow the same prepared statement (which works at session level) to be executed again.
Let’s now see what happens in the session pool_mode.
Running prepared statement in transaction pool_mode
Let’s run the same simple prepared statement in database test_db with pool_mode set to session.
#Running the prepare statement and execute first time
[postgres@ip-172-31-94-225 ~]$ psql -h localhost -p 6432 -U postgres -d test_db -c "PREPARE stmtc AS SELECT 1;EXECUTE stmtc;"
Password for user postgres:
PREPARE
?column?
----------
1
(1 row)
#Running the prepare statement and execute second time
[postgres@ip-172-31-94-225 ~]$ psql -h localhost -p 6432 -U postgres -d test_db -c "PREPARE stmtc AS SELECT 1;EXECUTE stmtc;"
Password for user postgres:
PREPARE
?column?
----------
1
(1 row)Here, we see the same set of commands executed without errors both times because pool_mode is set to session. This setting goes well with prepared statements since they also work at the session level.
It is my recommendation, therefore, that if you’re using PgBouncer, it is better to use session pool_mode only for those databases where prepared statements are used.
But what if you can’t change the pool_mode to session?
In this case, you would either need to consider switching your prepared statements to normal queries and continue using pool_mode=transaction, or try disabling the caching of the client-side prepared statements.
Disable client-side prepared statement
If you must use transaction pooling mode to maximize connection reuse and minimize backend connections, the application needs to send normal queries instead of preparing them. You could also consider disabling the client-side prepared statements (which again depends on the driver that you use).
- Set
max_prepared_statements=0in yourpgbouncer.ini. - If JDBC , we can use prepareThreshold=0&preparedStatementCacheQueries=0 in your connection string.
- If psycopg, set the
prepare_thresholdtoNone.
Post tracking
It is important to monitor and track these kind of changes, and also test your failover process in a staging environment to ensure prepared statements and pooling modes behave as expected.
- Configure both PgBouncer and PostgreSQL logs to capture these errors.
- Monitor the logs actively and identify the frequency of the errors.
- Leverage PgBouncer admin database for stats to analyze the traffic better.
- Also test a failover scenario and simulate production-like poolers in staging to catch these issues early.
- Mirror the whole production setup including identical PgBouncer pooling modes and configurations. This helps catch issues with prepared statements, connection recycling, and pool state transitions before they impact users.
- Use PgBouncer admin commands like
pause,resume,reconnectandkillto manage and observe the connection pool behavior during failover. - Automate failover drills and include monitoring and validation as part of your test plan.
- Integrate alerts or trigger based notifications for these errors for quick identification and resolution.
Summary
In this blog, we discussed how to resolve the issues caused by using prepared statements with PgBouncer configured in transaction pooling mode. Since prepared statements are session-bound but transaction mode reuses connections per transaction, statements may fail with errors like “prepared statement already exists.” The solution involves setting pool_mode = session specifically for affected databases while keeping the default as transaction, enabling session persistence only where needed. As an alternative, disabling client-side prepared statements in the application driver (e.g., JDBC or psycopg) was suggested when changing pool mode wasn’t possible. When making changes to the PgBouncer settings, it is very important to monitor logs, simulate production-like setups in staging, and test failovers thoroughly to catch issues early, ultimately ensuring reliability and performance in PostgreSQL environments using PgBouncer.
Here are some of our blogs on PgBouncer
- Connection Pooling with pgBouncer: A Primer
- Step-by-Step Guide to PostgreSQL HA with Patroni: Part 3
Being a PostgreSQL expert specializing in designing scalable and efficient systems focuses on delivering robust technical solutions aligned with business goals. Feel free to reach us for any query.
Thank you!!