pgx With PgBouncer and Transaction Pooling Mode Pitfalls

A Go service that works perfectly against PostgreSQL starts failing the day a connection pooler is put in front of it. The errors name prepared statements: prepared statement "stmtcache_..." already exists, or does not exist. They are intermittent, they get worse under load and they disappear when you connect to the database directly. This is the best known incompatibility between pgx's defaults and PgBouncer's transaction pooling mode, and it has three fixes. This article reproduces it with a real PgBouncer on Go 1.25.6 and pgx v5.11.0 against PostgreSQL 18.6, measures the failure and each fix, and explains why it happens. It belongs to the database cornerstone, and the cache it involves is the subject of the statement cache article.

Why transaction pooling breaks prepared statements

PgBouncer sits between many client connections and few server connections. In transaction pooling mode, a client holds a server connection only for the duration of a transaction; between transactions the server connection is returned to the pool and given to another client. That is what lets a thousand clients share twenty server connections, and it is why teams use it.

A prepared statement lives in a server session. pgx's default mode sends Parse to create a statement named after a hash of the SQL (stmtcache_<hash>) the first time a connection runs it, then Bind and Execute for the later calls. Behind a transaction pooler there are two failures:

• Client A prepares stmtcache_abc on server connection 1. Client B, assigned to server connection 1 next, tries to prepare the same name: already exists (42P05).
• Client A prepared on server connection 1 and its next transaction is given server connection 2, where the name was never created: does not exist (26000).

The client cannot know which server connection it will get, so neither can its cache. Session state of any kind, SET, temporary tables, advisory locks and LISTEN, has the same problem; prepared statements are just the one pgx uses on every query.

The experiment

package main

import (
	"context"
	"errors"
	"fmt"
	"log"
	"strings"
	"sync"
	"time"

	"github.com/jackc/pgx/v5"
	"github.com/jackc/pgx/v5/pgconn"
	"github.com/jackc/pgx/v5/pgxpool"
)

func short(err error) string {
	if err == nil {
		return "ok"
	}
	var pg *pgconn.PgError
	if errors.As(err, &pg) {
		return pg.Code + " " + pg.Message
	}
	s := err.Error()
	if i := strings.Index(s, "\n"); i > 0 {
		s = s[:i]
	}
	return s
}

// hammer runs many different statements from many goroutines through a pool of pgx connections that all share two server connections.
func hammer(dsn string, mode pgx.QueryExecMode) (okCount, failures int, sample string) {
	cfg, err := pgxpool.ParseConfig(dsn)
	if err != nil {
		log.Fatal(err)
	}
	cfg.MaxConns = 8
	cfg.ConnConfig.DefaultQueryExecMode = mode
	pool, err := pgxpool.NewWithConfig(context.Background(), cfg)
	if err != nil {
		log.Fatal(err)
	}
	defer pool.Close()
	var mu sync.Mutex
	var wg sync.WaitGroup
	for w := 0; w < 8; w++ {
		wg.Add(1)
		go func() {
			defer wg.Done()
			for i := 0; i < 60; i++ {
				// A different statement text each time round, so every connection prepares several.
				q := fmt.Sprintf("select %d + $1::int", (w*7+i)%13)
				var n int
				err := pool.QueryRow(context.Background(), q, i).Scan(&n)
				mu.Lock()
				if err == nil {
					okCount++
				} else {
					failures++
					if sample == "" {
						sample = short(err)
					}
				}
				mu.Unlock()
			}
		}()
	}
	wg.Wait()
	return
}

func main() {
	direct := "postgres://postgres:[email protected]:55432/lab?sslmode=disable"
	noPrep := "postgres://postgres:[email protected]:56440/lab?sslmode=disable"
	withPrep := "postgres://postgres:[email protected]:56441/lab?sslmode=disable"

	fmt.Println("480 statements from 8 goroutines through 8 pgx connections")
	fmt.Printf("%-62s %6s %9s  %s\n", "setup", "ok", "failed", "first error")
	for _, c := range []struct {
		label string
		dsn   string
		mode  pgx.QueryExecMode
	}{
		{"direct to PostgreSQL, default mode (cached statements)", direct, pgx.QueryExecModeCacheStatement},
		{"PgBouncer transaction mode, no prepared statement support", noPrep, pgx.QueryExecModeCacheStatement},
		{"same, QueryExecModeExec (unnamed statements)", noPrep, pgx.QueryExecModeExec},
		{"same, QueryExecModeSimpleProtocol", noPrep, pgx.QueryExecModeSimpleProtocol},
		{"same, QueryExecModeCacheDescribe", noPrep, pgx.QueryExecModeCacheDescribe},
		{"PgBouncer with max_prepared_statements = 200, cached statements", withPrep, pgx.QueryExecModeCacheStatement},
	} {
		ok, bad, sample := hammer(c.dsn, c.mode)
		fmt.Printf("%-62s %6d %9d  %s\n", c.label, ok, bad, sample)
	}
	_ = time.Second
}

set -e
d=$(mktemp -d)
trap 'docker rm -f gh-lab-bouncer0 gh-lab-bouncer200 >/dev/null 2>&1; rm -rf "$d"' EXIT
printf '"postgres" "secret"\n' > "$d/userlist.txt"
for n in 0 200; do
  cat > "$d/pgbouncer$n.ini" <<EOF
[databases]
lab = host=172.17.0.2 port=5432 dbname=lab
[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 5432
auth_type = plain
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
default_pool_size = 2
max_client_conn = 100
max_prepared_statements = $n
admin_users = postgres
EOF
done
docker rm -f gh-lab-bouncer0 gh-lab-bouncer200 >/dev/null 2>&1 || true
docker run -d --name gh-lab-bouncer0 -p 127.0.0.1:56440:5432 -v "$d/pgbouncer0.ini:/etc/pgbouncer/pgbouncer.ini:ro" -v "$d/userlist.txt:/etc/pgbouncer/userlist.txt:ro" edoburu/pgbouncer:latest >/dev/null
docker run -d --name gh-lab-bouncer200 -p 127.0.0.1:56441:5432 -v "$d/pgbouncer200.ini:/etc/pgbouncer/pgbouncer.ini:ro" -v "$d/userlist.txt:/etc/pgbouncer/userlist.txt:ro" edoburu/pgbouncer:latest >/dev/null
sleep 3
go run .

480 statements from 8 goroutines through 8 pgx connections
setup                                                              ok    failed  first error
direct to PostgreSQL, default mode (cached statements)            480         0  
PgBouncer transaction mode, no prepared statement support         105       375  42P05 prepared statement "stmtcache_44a6bf65d1a0c65953c72dbbde831dfe2627c97345f8733b" already exists
same, QueryExecModeExec (unnamed statements)                      480         0  
same, QueryExecModeSimpleProtocol                                 480         0  
same, QueryExecModeCacheDescribe                                  480         0  
PgBouncer with max_prepared_statements = 200, cached statements    480         0

The script writes two PgBouncer configurations and starts two containers: both use pool_mode = transaction and default_pool_size = 2, so every client shares two server connections. One has max_prepared_statements = 0 (no prepared statement support) and the other 200. The program then runs 480 statements, with 60 different SQL texts, from 8 goroutines through a pool of 8 pgx connections, under six setups.

Direct to the database, everything worked. Through the pooler in transaction mode with the pgx default, 375 of 480 statements failed, 78 percent, almost all with already exists. Eight pgx connections and two server connections guaranteed collisions. In a real service the rate depends on traffic and pool sizes, which is why the symptom looks random.

The three fixes

1. Change pgx's query execution mode. The setting is default_query_exec_mode in the connection string, or cfg.ConnConfig.DefaultQueryExecMode in code. Three modes avoided named prepared statements and all succeeded 480 of 480:

• QueryExecModeExec sends each statement unnamed with its arguments in the same round trip, using the extended protocol with no cache. Safe behind a pooler, and the usual recommendation. It parses the statement on every call, which costs a little.
• QueryExecModeSimpleProtocol sends the statement as text with the arguments interpolated by pgx on the client. It works with every pooler, and has different type conversion rules, so it is not a drop in replacement for every query. Use it only if Exec mode does not work.
• QueryExecModeCacheDescribe caches the description of a statement on the client and runs it unnamed, so there is no server side name to collide. It keeps the type benefits of the extended protocol and caches the metadata. Like the default, it can fail once after a schema change, as the statement cache article shows.

exec is the one to try first. Set it for the pool that goes through PgBouncer and leave the default for any connections that go straight to the database.

2. Use PgBouncer's protocol level prepared statement support. PgBouncer 1.21 added the ability to track prepared statements across server connections: it renames the client's statements internally and re-prepares them on whichever server connection a transaction gets. It is controlled by max_prepared_statements, the number of statements it will track per client. With it set to 200, pgx's default cached mode ran all 480 statements without a failure. This is the best fix when you can upgrade PgBouncer and set the option, since it keeps the prepared statement performance. Check your version: older releases ignore the setting, and a value of 0 disables the feature, as in the first container. Managed poolers have their own switches, so look for the equivalent.

3. Do not use transaction pooling for this service. Session pooling mode gives a client one server connection for its whole session, so prepared statements, SET and LISTEN work, and it shares less. It suits services with few connections that need session state. A pool inside the application (pgxpool) already does the multiplexing in the service; PgBouncer is for the many-services, many-connections situation.

Other things transaction pooling breaks

Prepared statements are the most common failure, and not the only one. Any session state is unsafe:

• SET and RESET of parameters (SET search_path, SET timezone): use SET LOCAL inside a transaction, which ends with it.
• Advisory locks taken with the session variants: use pg_advisory_xact_lock.
• LISTEN and NOTIFY: connect a listener directly to PostgreSQL, as the listen article does.
• Temporary tables, cursors WITH HOLD and DISCARD ALL assumptions.
• A pgxpool.AfterConnect hook that runs SET: it runs on the client's connection to PgBouncer, and the setting does not follow the transaction to the server.

If the service depends on these, run the parts that need them over a direct connection and the rest through the pooler.

Checklist

1. Know the pooler mode and version. If it is PgBouncer in transaction mode, decide between max_prepared_statements and a pgx exec mode before the first deploy.
2. Test the service against the pooler in CI, with more client connections than server connections, as the lab does. Tests that use one connection pass and prove nothing.
3. Keep the connection settings for pooled and direct connections separate in configuration.
4. Size the pools together: a pgx pool of 8 times 20 instances is 160 client connections to PgBouncer, which must allow them in max_client_conn, and the server side is default_pool_size per database and user.
5. Watch PgBouncer's own statistics (SHOW POOLS, SHOW STATS) for clients waiting, the early sign of an undersized server pool.

The settings are documented in the PgBouncer configuration reference, including max_prepared_statements and the feature's limits, and the pgx modes in the QueryExecMode reference.