{"id":8831,"date":"2025-07-09T09:45:23","date_gmt":"2025-07-09T09:45:23","guid":{"rendered":"https:\/\/test.opensource-db.in\/wp1\/?p=8831"},"modified":"2025-07-09T09:46:53","modified_gmt":"2025-07-09T09:46:53","slug":"how-we-solved-prepared-statement-issues-with-pgbouncers-pooling-modes","status":"publish","type":"post","link":"https:\/\/test.opensource-db.in\/wp1\/how-we-solved-prepared-statement-issues-with-pgbouncers-pooling-modes\/","title":{"rendered":"How We solved prepared statement issues with PgBouncer\u2019s pooling modes"},"content":{"rendered":"\n

When leveraging PostgreSQL for your application, PgBouncer<\/code> 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<\/a> 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.<\/p>\n\n\n\n

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<\/code> 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:<\/p>\n\n\n\n

ERROR:  prepared statement \"test\" already exists<\/code><\/pre>\n\n\n\n
How we solved it?<\/h2>\n\n\n\n
The issue was that pool_mode<\/code> was set to transaction<\/code>, but a prepared statement works at session level. In this case, we either need to disable prepared statement, or set the pool_mode<\/code> to session<\/code> for that particular database. Let us run a simple prepared statement in both of these modes .<\/p>\n\n\n\n
How to set multiple pool_modes in pgbouncer<\/strong>?<\/p>\n\n\n\n
We can set different pool_mode<\/code> for different database based on the requirement. The following pgbouncer.ini configuration file illustrates the setup:<\/p>\n\n\n\n
#Go to the PgBouncer configuration file.\nvi \/etc\/pgbouncer\/pgbouncer.ini\n[databases]\n* = host=localhost port=5433 auth_user=postgres\ntest_db = host=localhost port=5433 auth_user=postgres pool_mode=session\n[pgbouncer]\nlisten_addr = *\nlisten_port = 6432\nadmin_users = postgres\nauth_type = scram-sha-256\nauth_file = \/etc\/pgbouncer\/userlist.txt\npool_mode = transaction\nmax_client_conn = 100\ndefault_pool_size = 20<\/code><\/pre>\n\n\n\n
We can set a different pool_mode<\/code> for different databases as shown above. Here, we have set the default pool_mode<\/code> as transaction<\/code> for all the databases, but for the test_db<\/code> , it was set to session<\/code>. If you have already set pool_mode<\/code> to transaction<\/code> and need to change the pool_mode<\/code> to session<\/code> , add a new row as we did for test_db<\/code> above and reload the pgbouncer. This will not affect the current connections.

After making the changes, you could verify the pool_mode<\/code> being used as follows:<\/p>\n\n\n\n
#Here is the output of pools we had\npgbouncer=# show pools;\n-[ RECORD 1 ]---------+------------\ndatabase              | pgbouncer\nuser                  | pgbouncer\ncl_active             | 1\ncl_waiting            | 0\ncl_active_cancel_req  | 0\ncl_waiting_cancel_req | 0\nsv_active             | 0\nsv_active_cancel      | 0\nsv_being_canceled     | 0\nsv_idle               | 0\nsv_used               | 0\nsv_tested             | 0\nsv_login              | 0\nmaxwait               | 0\nmaxwait_us            | 0\npool_mode             | statement\nload_balance_hosts    |\n-[ RECORD 2 ]---------+------------\ndatabase              | test_db\nuser                  | postgres\ncl_active             | 0\ncl_waiting            | 0\ncl_active_cancel_req  | 0\ncl_waiting_cancel_req | 0\nsv_active             | 0\nsv_active_cancel      | 0\nsv_being_canceled     | 0\nsv_idle               | 0\nsv_used               | 1\nsv_tested             | 0\nsv_login              | 0\nmaxwait               | 0\nmaxwait_us            | 0\npool_mode             | session\nload_balance_hosts    |\n-[ RECORD 3 ]---------+------------\ndatabase              | test\nuser                  | postgres\ncl_active             | 0\ncl_waiting            | 0\ncl_active_cancel_req  | 0\ncl_waiting_cancel_req | 0\nsv_active             | 0\nsv_active_cancel      | 0\nsv_being_canceled     | 0\nsv_idle               | 0\nsv_used               | 1\nsv_tested             | 0\nsv_login              | 0\nmaxwait               | 0\nmaxwait_us            | 0\npool_mode             | transaction\nload_balance_hosts    |<\/code><\/pre>\n\n\n\n
If we observe the output of show pools<\/code> , it says the database test_db<\/code> is having pool_mode<\/code> as session<\/code> and database test<\/code> is having pool_mode<\/code> as transaction<\/code><\/p>\n\n\n\n
Running prepared statement in transaction pool_mode<\/strong><\/p>\n\n\n\n
Let’s run a simple prepared statement in database test<\/code> with pool_mode<\/code> set to transaction<\/code>.<\/p>\n\n\n\n
#Running the prepare statement and execute first time \n[postgres@ip-172-31-94-225 ~]$ psql -h localhost -p 6432 -U postgres -d test -c \"PREPARE stmtc AS SELECT 1;EXECUTE stmtc;\"\nPassword for user postgres:\nPREPARE\n ?column?\n----------\n        1\n(1 row)\n#Running the prepare statement and execute second time \n[postgres@ip-172-31-94-225 ~]$ psql -h localhost -p 6432 -U postgres -d test -c \"PREPARE stmtc AS SELECT 1;EXECUTE stmtc;\"\nPassword for user postgres:\nERROR:  prepared statement \"stmtc\" already exists<\/code><\/pre>\n\n\n\n
Here, we ran same set of commands two times and see the error message in the second time because when the pool_mode<\/code> is set to transaction<\/code>, 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.<\/code> 

Let’s now see what happens in the session<\/code> pool_mode.<\/p>\n\n\n\n
Running prepared statement in transaction pool_mode<\/strong><\/p>\n\n\n\n
Let’s run the same simple prepared statement in database test_db<\/code> with pool_mode<\/code> set to session<\/code>.<\/p>\n\n\n\n
#Running the prepare statement and execute first time\n[postgres@ip-172-31-94-225 ~]$ psql -h localhost -p 6432 -U postgres -d test_db -c \"PREPARE stmtc AS SELECT 1;EXECUTE stmtc;\"\nPassword for user postgres:\nPREPARE\n ?column?\n----------\n        1\n(1 row)\n#Running the prepare statement and execute second time\n[postgres@ip-172-31-94-225 ~]$ psql -h localhost -p 6432 -U postgres -d test_db -c \"PREPARE stmtc AS SELECT 1;EXECUTE stmtc;\"\nPassword for user postgres:\nPREPARE\n ?column?\n----------\n        1\n(1 row)<\/code><\/pre>\n\n\n\n
Here, we see the same set of commands executed without errors both times because pool_mode<\/code> is set to session<\/code>. 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<\/code> pool_mode only for those databases where prepared statements are used. 

But what if you can’t change the pool_mode to session<\/code>?<\/p>\n\n\n\n
In this case, you would either need to consider switching your prepared statements to normal queries and continue using pool_mode<\/code>=transaction<\/code>, or try disabling the caching of the client-side prepared statements.<\/p>\n\n\n\n
Disable client-side prepared statement<\/h2>\n\n\n\n
If you must use transaction<\/code> 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).<\/p>\n\n\n\n