Alright, let’s talk about one of the most common “gotchas” when you first start using PgBouncer: the whole prepared statement debacle. It’s the thing that will make your application, which ran perfectly fine connecting directly to Postgres, suddenly start throwing bizarre prepared statement "S_1" does not exist errors all over the place. It feels like a betrayal, but I promise you, it’s just PgBouncer doing its job a little too well. Let’s break down why this happens and how to wrestle it into submission.

The core of the issue is that PgBouncer’s entire reason for being is to be a dumb, fast, middleman. It’s not a full Postgres server; it’s a traffic cop. Its job is to route your connections and pass queries through as efficiently as possible. And this is where it clashes with the Postgres protocol’s concept of prepared statements.

When your application prepares a statement, it’s essentially sending a two-step process to the database:

  1. PREPARE my_stmt AS SELECT * FROM users WHERE id = $1
  2. EXECUTE my_stmt(123)

The first command creates a named object (my_stmt) that lives on that specific database connection. The second command uses it. The problem? PgBouncer, especially in its most useful transaction pooling mode, might send the PREPARE and the EXECUTE down two completely different actual backend connections to Postgres. The second connection has no idea what my_stmt is, so it throws its hands up in error.

The Root Cause: Protocol vs. Pooling Modes

This behavior is dictated by your pooling mode (session, transaction, or statement).

  • In session mode, a client gets a dedicated backend for its entire session. Prepared statements work fine here because the connection never changes. But you lose most of the benefits of pooling, as connections are held for much longer.
  • In statement mode, it’s chaos. PgBouncer can swap the backend connection between every single query. This breaks prepared statements even more spectacularly and has other nasty side-effects (like breaking multi-statement transactions). Avoid this mode unless you have a very specific, strange need.
  • transaction mode is the sweet spot for performance and why we use PgBouncer. It assigns a backend from the pool only for the duration of a transaction. As soon as you commit or rollback, that backend is returned to the pool for someone else to use. This is what utterly destroys the concept of a prepared statement that persists across transactions.

The Solutions: Fighting Fire with Smarts

You have a few weapons in your arsenal here, ranging from “tell your app to behave” to “reconfigure the world.”

Option 1: The Client-Side Fix (The Best Way)

The most robust solution is to make your application do something called protocol-level prepared statements. Instead of using the raw PREPARE and EXECUTE commands, you use a client library feature that leverages the wire protocol to prepare statements without relying on the connection-scoped named statement.

For example, in Python with psycopg2, you’d use the prepare method on the cursor. This tells the driver to manage the preparation internally, often by automatically de-duping and using the anonymous prepared statement protocol.

# This will break with PgBouncer in transaction mode
cur.execute("PREPARE my_stmt AS SELECT * FROM users WHERE id = $1")
cur.execute("EXECUTE my_stmt(123)")

# Do this instead. The driver handles it magically.
cur.execute("SELECT * FROM users WHERE id = %s", (123,))

In Go with pgx, this is handled automatically. The library will use the extended query protocol under the hood, and if it detects a prepared statement does not exist error, it will often automatically retry the entire query, preparing and executing on the same connection. It’s brilliant.

Option 2: The Server-Side Smash (Use With Caution)

If you have a legacy app you can’t modify, you can force the issue on the PgBouncer side with the server_reset_query setting. The default is DISCARD ALL, which is the nuclear option. It nukes every connection state (including prepared statements) before returning it to the pool. This is safe but has a performance cost.

You can change it to something less drastic:

# In pgbouncer.ini
server_reset_query = RESET ALL

RESET ALL is slightly lighter than DISCARD ALL, but it still obliterates prepared statements. The real magic trick is to use RESET ROLE if you don’t use role settings, or even an empty string server_reset_query = if you are absolutely certain no connection state carries between transactions. This is dangerous but can be a performance boon.

Option 3: The Nuclear Option (Just Don’t)

You can set ignore_startup_parameters to include extra_float_digits (a common one clients send) and hope for the best, but trying to strip the protocol-level prepare commands is a messy and unreliable game. It’s not the right tool for the job.

The bottom line is this: PgBouncer and prepared statements are like two brilliant engineers who refuse to talk to each other. Your job is to be the mediator. Push the work to the client library if you can. It’s the cleanest, most performant solution. If you can’t, tweak server_reset_query carefully and monitor for any weird state-related bugs. You’ve been warned.