PgBouncer 1.21 tracks named prepared statements across pooled connections

A prepared statement belongs to a PostgreSQL server connection, while transaction pooling allows successive transactions from one client to use different server connections. That mismatch has made named prepared statements difficult to combine with pooled connections. PgBouncer 1.21.0 adds tracking and rewriting so that the statement a client names can exist on whichever server connection receives the next command.

The October 17 announcement identifies named prepared statements as the main addition. Its behavior is controlled by max_prepared_statements, and the configuration documentation at the 1.21.0 tag explains the mechanism and limits. The setting defaults to zero, which leaves the new support disabled for transaction and statement pooling.

I prefer PostgreSQL partly because its behavior can usually be traced to a documented mechanism. This feature is attractive for the same reason. It addresses a specific kind of connection state by maintaining the mappings needed to reconstruct it on another server connection.

Client names become internal names

When tracking is enabled, PgBouncer examines query strings sent through protocol-level prepared-statement commands. It gives each unique query string an internal name in the form PGBOUNCER_ followed by an identifier. The client's chosen name remains part of PgBouncer's own mapping, while PostgreSQL sees the internal name.

Commands referring to the statement are rewritten to use that internal name before PgBouncer forwards them. The pooler also checks whether the assigned server connection has the statement prepared. If it does not, PgBouncer prepares it there before forwarding the command that needs it. Availability follows from that extra operation, rather than from requiring the client to retain one server session forever.

This distinction explains what the feature changes. The client can retain a logical relationship between its name and its query even when its next transaction uses another backend. PgBouncer owns the work of making the corresponding server-side statement available. The server still has connection-local prepared state, and the pooler now has enough information to maintain it.

The mapping also permits reuse between clients. The documentation gives an example with 100 clients using the same query and a pool of 20 server connections. PostgreSQL needs to prepare that query on those 20 connections, rather than separately for all 100 clients. That is a documented illustration of deduplication, not a benchmark result.

The limit applies per server connection

The numeric value of max_prepared_statements controls how many statements PgBouncer keeps active on one server connection. It is not simply a global maximum number of queries in the application. Choosing a useful value requires understanding the commonly reused prepared statements that need to remain available within each backend's working set.

A minimal configuration fragment enables tracking for a transaction pool. The illustrative limit below is an administrator's choice, rather than a value claimed to fit every workload.

[pgbouncer]
pool_mode = transaction
max_prepared_statements = 100

Larger values increase PostgreSQL memory use because more statements remain prepared on each connection. PgBouncer also stores query strings and needs packet-rewriting buffers. Its documentation says unique query strings are stored once in a global cache, while each client connection has a rewriting buffer with a documented upper bound of four times pkt_buf.

Tracking therefore has costs on both sides of the pool. PgBouncer inspects and rewrites commands, which consumes CPU, and the server retains prepared queries. The feature can reduce repeated parsing and planning work, especially when clients share query strings, but it does not make that bookkeeping free. The useful comparison includes reuse across the pool and the resources consumed to preserve it. For pooler CPU capacity, the documentation points to multiple PgBouncer instances sharing a port through so_reuseport when processing needs more than one core. That addresses pooler processing capacity separately from the statement limit.

SQL commands are outside the new tracking

The versioned documentation explicitly excludes SQL-level PREPARE, EXECUTE and DEALLOCATE from this mechanism. The new support concerns the wire protocol's prepared-statement commands. A driver using that protocol behavior and an application issuing SQL PREPARE are not interchangeable cases, even though both describe preparing a statement.

Version 1.21.0 also does not track SQL DEALLOCATE ALL or DISCARD ALL. The documentation calls these particularly problematic because they can appear to succeed while removing server state without updating PgBouncer's view of that state. Later uses of tracked statements on that connection can then fail. This is an explicit release limitation, not an edge case to infer from a generic prepared-statement announcement.

The normal server_reset_query deserves separate attention. The same tagged configuration document says it is not used in transaction pooling by default. Its default DISCARD ALL value therefore does not mean every transaction pool automatically executes the unsupported reset command after each transaction. Forcing reset behavior into every pooling mode changes that behavior and needs to be understood separately.

My preference is to inspect how a client library actually prepares and resets statements before describing it as compatible. The version number establishes that PgBouncer has the mechanism, while the client's protocol and SQL commands determine whether the application uses the supported path.

Reuse makes type changes visible

The documentation identifies another limit when identical query strings expect different argument or result types. PostgreSQL can report that a cached plan must not change its result type. A schema migration adding a column or changing a column type is one way applications can encounter that mismatch.

The suggested response after such a migration is RECONNECT through the PgBouncer administration console, forcing statements to be prepared again on new server connections. That is a specific remedy for retained server-side preparation state. It does not change the semantics of a query whose clients disagree about types, and it is not a general instruction to reset every session with unsupported SQL commands.

PgBouncer 1.21.0 consequently provides a defined subset of session-state continuity. Protocol-level statement names can survive assignment to different server connections because the pooler translates and reinstalls them. Applications still need to preserve the release's distinction between those tracked commands and SQL operations that independently modify prepared state on the server.