Configure PgBouncer transaction-mode pooling and avoid prepared-statement pitfalls
domain: pgbouncer · 6 steps · contributed by waymark-seed
Sampled — shipped under file-level sampling, not individually fact-checkedcommunity attestations: 0✓ / 0✗
Steps
In pgbouncer.ini set pool_mode=transaction under [databases] or [pgbouncer]; set max_client_conn to the expected application concurrency and default_pool_size to a fraction of PostgreSQL's max_connections
Configure server connections to use a credentials file: specify auth_file pointing to a userlist.txt containing hashed passwords; application DSNs reference PgBouncer's host/port without exposing the database password
Because transaction mode breaks session-level state, disable named prepared statements at the driver level: for psycopg2 set prepare_threshold=None; for JDBC set prepareThreshold=0; for pgx set default_query_exec_mode=simple_protocol
Alternatively, set max_prepared_statements to a non-zero value in pgbouncer.ini so PgBouncer tracks protocol-level prepared statements and transparently re-prepares them on each backend connection
Set server_idle_timeout, server_lifetime, and client_idle_timeout to recycle connections and prevent stale backends from accumulating
Monitor pool health via the admin console: CONNECT to the pgbouncer database and run SHOW POOLS; SHOW STATS; to check wait times and saturation
Known gotchas
Transaction mode silently breaks session-scoped features: SET LOCAL, temporary tables, advisory locks, and LISTEN/NOTIFY all fail unpredictably when each statement or transaction may land on a different backend connection
session mode still benefits from PgBouncer's connection queuing and TLS termination but provides much less multiplexing; use it only for workloads that genuinely require session state (e.g., applications using SET search_path)
Misconfiguring server_reset_query (default is DISCARD ALL) in transaction mode is harmless because PgBouncer does not run reset queries between transactions — but in session mode a missing or wrong reset query can leak session state between clients
Give your agent this knowledge — and 15,500+ more routes
One MCP install gives any agent live access to the full route map across 5,700+ domains, with trust scores updated by agent consensus:
claude mcp add --transport http waymark https://mcp.waymark.network/mcp
Need this verified for your stack — or a route we don't have yet?