Does PgBouncer transaction pooling break prepared statements?

Quick answer: It used to. PgBouncer 1.21 added tracking for protocol-level named prepared statements, and 1.24.0 turned it on by default. On a current PgBouncer, the answer is no and no change is needed. On anything between 1.21 and 1.24 you must set max_prepared_statements yourself. SQL PREPARE is never tracked and still breaks, which this page reproduces on 1.25.2 with the defaults.

What is the problem?

A connection pooler sits between your application and PostgreSQL and shares a small number of database connections across a much larger number of clients. PgBouncer is one example.

It has three modes. Only transaction pooling causes this problem:

• Session pooling. A client keeps one server connection until it disconnects. Session state behaves as if the pooler were not there. It still caps how many backends PostgreSQL ever sees, queues clients past that cap and handles authentication centrally. It does not share one server connection between clients that are connected at the same time, so a pool of 500 concurrent clients needs 500 backends.
• Transaction pooling. A client gets a server connection for the duration of one transaction and then gives it back. This is where the savings and the trouble are.
• Statement pooling. The connection is returned after every statement. Multi statement transactions are not possible.

A prepared statement is parsed once and then executed many times by name, and the statement lives on the server connection where it was prepared. Planning is a separate question: with the default plan_cache_mode = auto, PostgreSQL builds a custom plan for the actual parameter values on the first executions and only switches to a reused generic plan if the generic plan’s estimated cost is competitive. “Prepared” means parsed once. It does not always mean planned once.

Put those facts together. Your driver prepares stmt_1 inside transaction one, and PgBouncer hands that server connection back to the pool. Transaction two lands on a different server connection, which has never heard of stmt_1:

ERROR:  prepared statement "stmt_1" does not exist

The application and database are both behaving as expected. The pooler has broken an assumption between them.

The version timeline, which is the whole answer

PgBouncer	behaviour
before 1.21	no tracking. Transaction pooling breaks prepared statements
1.21 to 1.23	tracking exists and is off. max_prepared_statements defaults to 0
1.24.0 and later	tracking is on. The default is 200

The changelog for 1.24.0, released 2025-01-10, says:

Enable prepared statement support by default, max_prepared_statements is now set to 200 by default.

A PgBouncer 1.25.2 container confirmed this with the setting absent from pgbouncer.ini. SHOW CONFIG in the admin console reports the current value and the default in separate columns:

max_prepared_statements|200|200|yes

The operational question is which version is running and what its SHOW CONFIG reports, rather than whether max_prepared_statements is non-zero. On a current PgBouncer, adding the line changes nothing.

What the setting actually counts

It is not a per-client quota. From the PgBouncer configuration manual:

PgBouncer internally examines all the queries that are sent by clients as a prepared statement, and gives each unique query string an internal name with the format PGBOUNCER_{unique_id}. If the same query string is prepared multiple times (possibly by different clients), then these queries share the same internal name.

So it caps an LRU cache of statements held on each server connection, and identical query text prepared by different clients is a single server-side statement. The required value depends on the number of distinct query shapes, not the number of clients. Raising it uses backend memory on every pooled connection, so size it from the distinct query count rather than copying a figure from a page.

It covers the protocol form only

There are two ways to prepare a statement, and they are not the same thing:

PREPARE recent_orders (int) AS
SELECT id, total FROM orders WHERE customer_id = $1;

That is SQL text, and PgBouncer forwards it straight to PostgreSQL without tracking it. The other form is a named statement created over the extended query protocol, which is what a driver sends on your behalf.

Passing parameters is not the same as creating a named prepared statement. A driver can use the extended protocol with an unnamed statement and get parameter binding without creating anything PgBouncer could track. Whether a driver creates named statements depends on the driver, its version and its settings: psycopg has prepare_threshold, JDBC has prepareThreshold, and pgx has an exec-mode setting. If your driver never names a statement, tuning max_prepared_statements will change nothing.

DEALLOCATE ALL and DISCARD ALL are exceptions to the SQL-level rule. The same manual page says:

The exception to this rule are the DEALLOCATE ALL and DISCARD ALL commands, these do work as expected and will clear the prepared statements that PgBouncer tracked for the client that sends this command.

A psycopg client with prepare_threshold=0 prepared a statement, ran DISCARD ALL, moved to a different backend, and successfully executed the statement again. DISCARD ALL does not desynchronise the pooler.

How do I confirm it?

Start by checking which mode is running. A config file on disk is not necessarily the configuration of the running process. Connect to the PgBouncer admin console and ask:

SHOW CONFIG;
SHOW POOLS;

Read pool_mode and max_prepared_statements. If the mode is transaction and the maximum is 0, you have the old behaviour regardless of the installed version.

Then check the version, because the behaviour depends on it:

SHOW VERSION;

Reproduce it rather than reasoning about it

The trigger is backend reassignment between transactions, not load. Load makes reassignment more likely, but a development machine can reassign a connection when a second client takes it after the first client releases it. A saturated pool can also hand you the same backend every time. Force the reassignment directly. Run two transactions from one client and make a second client take the first server connection in the gap:

-- transaction one
SELECT pg_backend_pid();
PREPARE recent_orders (int) AS SELECT id, total FROM orders WHERE customer_id = $1;

-- a second client now opens a transaction and holds that backend

-- transaction two, on a different backend
SELECT pg_backend_pid();
EXECUTE recent_orders(3);

On PgBouncer 1.25.2 in transaction pooling with the default max_prepared_statements = 200, the pid went from 186 to 191 and the execute returned:

ERROR:  prepared statement "recent_orders" does not exist

The identical query through psycopg with prepare_threshold=0, which makes it a named protocol-level statement, moved from pid 191 to 194 across the same gap and returned its rows. Both runs used the same pooler and configuration, so the form of the prepare is the only difference.

pg_prepared_statements is a weaker check than it looks:

SELECT name, statement, prepare_time, from_sql
FROM pg_prepared_statements;

from_sql is true for the SQL PREPARE form and false for the protocol form. That distinction determines whether PgBouncer tracks the statement. In transaction pooling, this view shows whichever backend the inspection session happens to hold. Tracked statements appear under PgBouncer’s internal PGBOUNCER_<id> names rather than the name chosen by the client. Not finding your statement name there proves nothing.

What to do

Upgrade to PgBouncer 1.24.0 or later. That fixes the protocol-level case and needs no configuration. On 1.21 to 1.23, set max_prepared_statements yourself. It fixes one incompatibility, not transaction pooling in general: SQL PREPARE, LISTEN and other session-scoped features are still unavailable.

Stop using SQL PREPARE across transactions through a transaction pool. It is not tracked and will not be. Check hand-written SQL, migration tooling and any layer that emits PREPARE as text rather than as a protocol message.

Or turn named prepared statements off in the driver. Every major driver has a switch. In JDBC, prepareThreshold=0. In psycopg, prepare_threshold=None. In Go’s pgx, DefaultQueryExecMode: QueryExecModeSimpleProtocol. You give up the saved parse work on every execution. That matters on a hot query and may not matter on a cold one, so measure before assuming a cost. The pgx option also drops the extended protocol entirely, which is a bigger change than the other two.

Or use session pooling for the connections that need it. PgBouncer can host several pools, so an admin or migration path can use session pooling while application traffic uses transaction pooling.

Why does AI-generated code cause this?

There are two failures here, and they point in opposite directions. The older one is a generated pgbouncer.ini with pool_mode = transaction and no max_prepared_statements, because the default was zero for most of the history of that file and most examples online predate 1.21. The newer one is advice to add max_prepared_statements = 200 to a PgBouncer where it is already 200, presented as the fix for a problem the reader does not have. The default changed in January 2025, and much of the written material has not caught up.

The same applies to connection strings. Nothing in a generated DSN will disable prepared statements, because a direct connection has no reason to do so.

Both work perfectly in development, where one client and one connection mean the statement is always where the driver left it.

How do I stop it coming back?

Put max_prepared_statements in the config file explicitly, even if you believe the default is fine, so the next reader can see the decision.

Pin the PgBouncer version in your deployment and treat it as a dependency with defined behaviour, not as infrastructure that is always current.

Test against the pooler, not around it. A staging environment that connects directly to PostgreSQL cannot reproduce this problem. Point it at PgBouncer with a deliberately small pool so connections are reused between transactions.

Know what else transaction pooling drops. Session state does not survive across transactions: SET outside a transaction, LISTEN, advisory locks held between transactions, temporary tables, and WITH HOLD cursors. Prepared statements are the most visible case, not the only one.

How DBGorilla helps

DBGorilla can help

DBGorilla connects read-only and gives your AI coding agent the connection picture over time: how many backends exist, what state they are in, and how that moves when pool settings change, alongside the queries that are actually running. That turns “the pool seems fine” into a number your agent can read. Prepared statement errors surface in your application logs and the PostgreSQL server log, so the pooler’s own console remains the place to read pool_mode and max_prepared_statements.

Start free

FAQ

• Does PgBouncer transaction pooling break prepared statements?

  It did before PgBouncer 1.21. In transaction pooling a client gets a different server connection for each transaction, so a statement prepared on one connection was missing on the next and the query failed. PgBouncer 1.21 added tracking for protocol-level named prepared statements, and 1.24.0 enabled it by default, so a current PgBouncer handles them with no configuration. SQL PREPARE is a separate case and still fails.

• What causes the error prepared statement does not exist?

  The client prepared a statement on one server connection and then executed it on another. Behind a transaction pooler that happens between transactions, and it is the classic symptom of a driver using prepared statements through a pooler that is not tracking them. It can also happen after a server restart or a failover, when the server's prepared statements are gone but the client still believes they exist.

• How do I enable prepared statement support in PgBouncer?

  On PgBouncer 1.24.0 and later it is already on: max_prepared_statements defaults to 200, confirmed by SHOW CONFIG on a 1.25.2 container with the setting absent from the file. On 1.21 to 1.23 set it in pgbouncer.ini. The value caps an LRU cache of statements per server connection, not per client, and identical query text from different clients shares one server-side statement.

• Does PgBouncer support SQL PREPARE statements in transaction pooling?

  No, and this is the part that does not improve with the version. Measured on 1.25.2 with the default configuration, a SQL PREPARE in one transaction and an EXECUTE in the next failed with 'prepared statement does not exist' after the backend changed. DEALLOCATE ALL and DISCARD ALL are the documented exception: PgBouncer handles those and they clear its own tracking for that client.

• Should I use session pooling instead?

  It avoids this problem, because one client keeps one server connection for the life of its session, and it still caps backends, queues clients and centralises authentication. What it gives up is sharing a connection between clients that are connected at once, so 500 concurrent clients need 500 backends. Prefer transaction pooling on a current PgBouncer, and keep session pooling for connections that genuinely need session state.

Related

• PostgreSQL connection pooling and too many connections
• Why do prepared statements get slow after the fifth execution?
• How to fix idle in transaction connections