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

Short version: pgx v5 defaults to cache_statement, which prepares named statements on whichever backend it is holding, and a transaction-mode pooler then hands that backend to a different client. The fix belongs on the pooler: set max_prepared_statements in PgBouncer 1.21 or newer and extended-protocol clients work unchanged. The widely repeated client-side workaround, default_query_exec_mode=simple_protocol, does work, but it costs you server-side prepared statements and changes how arguments reach the server; exec is the better client-side lever if you need one. The dangerous part is the timing: a cold pool passes, and the failures only start once pooled backends have accumulated named statements from earlier clients.

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.

FAQ

What does prepared statement "stmtcache_1" already exists actually mean?

It means pgx registered a named statement on a backend connection, the pooler handed that backend to another client session, and the name is now either colliding with one the new session is about to create or missing from the one it expects. You will see both faces of it: SQLSTATE 42P05, already exists, on sequential connections, and SQLSTATE 26000, does not exist, under concurrency. Neither message names your code or the pooler, which is most of why it is hard to place.

Is simple_protocol safe to leave on in production?

It works, and it is not free. You lose server-side prepared statements, which is the thing making your repeated queries fast, and your arguments get interpolated into the query text rather than sent as bound parameters, so you inherit a different set of escaping edge cases than the one your tests cover. If you need a client-side lever, default_query_exec_mode=exec keeps the extended protocol and its parameter binding and only gives up the statement cache, which is the part the pooler objects to.

Why did it pass in development and fail in production?

Because the naive test is a cold pool. A bare pgx.Connect plus SELECT 1 against a freshly created database passed 5 out of 5, twice. The 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. A pooling bug that reproduces on connection one is a gift. This class is not.

What should max_prepared_statements be set to?

It is a cache size, not a cap, so the question is what is worth keeping warm rather than what your app might use at peak. Past the limit PgBouncer deallocates the least recently used statement, which costs a re-parse and never an error. We chose 100, deliberately below PgBouncer's own newer default of 200, because our pools are per-database and small at default_pool_size = 20, so 100 already permits up to 2,000 statements held open across a pool's backends.

Does this affect drivers other than pgx?

Every extended-protocol client that prepares named statements is exposed to the same conflict, so pgx is simply the one that surfaced it here. database/sql over pgx/v5/stdlib and pgxpool share the same default, and we have not measured them. That is the argument for fixing it on the pooler rather than documenting a per-driver workaround: otherwise each client rediscovers the problem independently, in production.

Does session-mode pooling have this problem?

No. A client keeps its backend for the whole session, so there is nothing to collide with. The setting parses harmlessly under session mode, which means you can write it unconditionally rather than branching your config on pool mode.

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.