Your Go app passes in dev and fails in production behind PgBouncer

ERROR: prepared statement "stmtcache_1" already exists (SQLSTATE 42P05)

If you write Go against Postgres through a connection pooler, you will meet that error eventually. The advice you will find is to append default_query_exec_mode=simple_protocol to your connection string, which does work.

We think that advice is aimed at the wrong side of the connection. Here is what we measured, including a diagnosis we got wrong first, and why the fix belongs on the pooler.

The setup

A transaction-mode pooler hands a backend connection to a client for the duration of a transaction, then returns it to the pool for someone else. That is the whole point: many clients share few backends.

pgx v5 defaults to cache_statement, which uses the extended protocol and prepares named statements on the connection it is holding. Those two facts are incompatible in an obvious way. The name is registered on a backend connection, the pooler then hands that same backend to a different client session, and now there is a name registered that the new session does not know about, or is about to collide with.

What we measured, and the correction

Our first write-up of this said a bare pgx.Connect followed by SELECT 1 failed 5 times out of 5, deterministically.

That was wrong, and the way it was wrong matters more than the original claim.

Re-running it properly against a freshly created database: pgx.Connect plus SELECT 1 passed 5/5. Ran it again, passed 5/5 again. On a new database with a cold pool, the naive test says everything is fine.

The failures appear once pooled backends have accumulated named statements from earlier clients. A wider probe (20 sequential connections, then 8 concurrent twice, then 10 connections issuing 3 distinct statements each) failed 16 of 46 on the first pass and 14 of 46 on a repeat. Two different errors, depending on shape:

prepared statement "stmtcache_N" already exists   (SQLSTATE 42P05)  -- sequential
prepared statement "stmtcache_N" does not exist   (SQLSTATE 26000)  -- concurrent

Two controls, same binary, same database. The direct port, bypassing the pooler, produced zero prepared-statement errors. The pooled port with default_query_exec_mode=simple_protocol passed 46 of 46.

So the diagnosis held. The severity assessment did not, and it moved in the worse direction. "Fails on the first query" is a bug you find in development. "Passes cold and starts failing once the pool warms" is a bug you find in production, on a Tuesday, under load, with an error message that names neither your code nor the pooler.

If you take one thing from this post: a connection-pooling bug that reproduces on connection one is a gift. This class does not.

Why we did not just document the workaround

The obvious fix is to tell users to set default_query_exec_mode=simple_protocol. It is measured to work, it is one query parameter, and plenty of pooled Postgres providers do exactly that.

We did not, for two reasons.

It puts the burden on every user who has not hit the problem yet, and every extended-protocol client will keep rediscovering it independently. pgx is simply the one that found it first here; database/sql over pgx/v5/stdlib and pgxpool share the same default and we have not measured them.

More importantly, simple protocol is not free. You lose server-side prepared statements, which is the thing making your repeated queries fast. It also changes how your arguments reach the server: they are interpolated into the query text rather than sent as bound parameters, which is a different set of escaping edge cases than the one you tested against. Telling users to turn that off to work around a pooler configuration is a real performance tax paid by people who never chose it.

If you do need a client-side lever, default_query_exec_mode=exec is the better one. It keeps the extended protocol and its parameter binding and only gives up the statement cache, which is the part the pooler is unhappy about.

The server-side fix

PgBouncer 1.21 added protocol-level support for named prepared statements, controlled by max_prepared_statements. Through 1.23 it defaulted to 0, which is disabled; 1.24 changed the default to 200. Set it, and the pooler tracks prepared statements per client session and re-prepares them on whichever backend a session lands on. Extended-protocol clients work unchanged.

Two things to know before you set it.

It matters for the pool modes that move backends between clients. That is transaction and statement mode. Session mode does not have this conflict, since a client keeps its backend. The setting parses harmlessly under session mode, so you can write it unconditionally.

It is a cache size, not a cap. This is the part that trips people into setting it too high. Past the limit, PgBouncer deallocates the least recently used statement. Exceeding it costs a re-parse, never an error. So the question is not "what is the maximum my app might use," it is "what is worth keeping warm."

We chose 100, deliberately below PgBouncer's own newer default of 200. Our pools are per-database and small, with default_pool_size = 20, so 100 already permits up to 2,000 statements held open across a pool's backends, and backend parse state is not free on a memory-budgeted database.

Verifying it independently of your app

Do not verify this with your application, because your application is the thing whose behavior you are unsure about. Use pgbench, which speaks the extended protocol on demand.

Its built-in workload runs against tables it does not create for you, so initialize a throwaway database first and run the benchmark against that, never against anything you care about:

pgbench -i -s 1 "postgresql://.../scratch"          # creates pgbench_* tables
pgbench -M prepared -c 8 -T 30 "postgresql://.../scratch"

Against a transaction-mode pooler with max_prepared_statements = 0, that reproduces the failure class immediately and for every client:

prepared statement "P_0" already exists

With the setting at 100, the same command completes cleanly. That gives you a reproduction and a regression test that does not depend on any particular driver's defaults.

Two things worth checking in your own stack

Know your PgBouncer version, and whether anything pins it. The feature needs 1.21 or newer. Ours came from a distro package with no pin, and we found the installed version had moved underneath us between one check and the next. It was still new enough, but that was luck rather than design. pgbouncer --version inside the actual image, not the version you believe you installed.

Know when your config actually gets rewritten. This is the part that cost us the most time and is entirely generic. Our pooler config is generated by a script baked into a container image, so changing the script does not change any running pooler. Existing databases keep their old configuration until their container is recreated. A fix that is merged, built, and deployed can still be absent from every database you have.

Whatever your equivalent is, work out the answer to "when does this file get regenerated" before you conclude the fix is live, and verify against a database that has actually been through that path rather than one that merely exists after the deploy.

We hit this because the pooled connection string is the one we hand to customers at Layerbase Cloud, so a Go user copying it and writing idiomatic pgx would have hit a failure that pointed at nobody. If you want a Postgres instance with a pooler in front of it to test your own driver's behavior, creating one takes about a minute.