# 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](https://www.pgbouncer.org/config.html):

> 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:

<!-- sql-check: skip a SQL PREPARE is exactly the form PgBouncer does not track; running it here would prove nothing -->
```sql
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:

```sql
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 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`.
[Get started free →](https://app.dbgorilla.com/signup)

## Related

- [PostgreSQL connection pooling and too many connections](https://www.dbgorilla.com/learn/postgres/postgresql-connection-pooling-too-many-connections/)
- [Why do prepared statements get slow after the fifth execution?](https://www.dbgorilla.com/learn/postgres/why-do-prepared-statements-get-slow-after-the-fifth-execution/)
- [How to fix idle in transaction connections](https://www.dbgorilla.com/learn/postgres/how-to-fix-idle-in-transaction-connections/)