Transaction-mode pooling is the default most people reach for when pgbouncer enters the picture, because it's the mode that actually lets you multiplex thousands of client connections onto a handful of real Postgres backends. The trade-off is rarely explained clearly: pgbouncer hands a backend connection to a client only for the duration of one transaction, then takes it back and may give the next transaction a completely different backend. Anything that relies on connection-level state surviving between statements will break, intermittently, in ways that are hard to reproduce because it depends on which backend you happened to land on.
Here are the four that actually bite people in production, in the order they tend to show up.
1. Server-side prepared statements
If your driver uses PREPARE / named statements (many ORMs do this transparently — Npgsql, some JDBC configurations, asyncpg with default settings) and caches the prepared statement handle on the client side across requests, the second request can be routed to a backend that never saw the PREPARE, and you get:
ERROR: prepared statement "s1" does not exist
or worse, a different statement with the same generated name if the pool reused an id, which some drivers will misreport as data corruption before anyone thinks to check the pooler. The fix is almost never "turn off prepared statements everywhere" — that throws away real planning-time savings. It's to either disable statement caching specifically for the pooled connection string (most drivers expose this: prepareThreshold=0 for pgjdbc, Max Auto Prepare=0 for Npgsql, statement_cache_size=0 for asyncpg), or move to pgbouncer 1.21+ with max_prepared_statements set, which added real support for prepared statements under transaction pooling by tracking and replaying them per-connection. Check your pgbouncer version before assuming either path.
2. Session-level SET and GUCs
SET statement_timeout = '30s';
SELECT ...; -- this is fine, same transaction
works. But:
SET search_path = 'tenant_42';
-- transaction ends, connection goes back to the pool
SELECT * FROM widgets; -- might run on a different backend with the default search_path
is a silent, dangerous bug, because the query doesn't error — it just quietly reads from the wrong schema. This is the one that actually causes incidents rather than just exceptions. Never rely on a SET outside of the same transaction when pooling in transaction mode. If you need per-tenant isolation, pass the tenant as a query parameter or use pgbouncer's own connection-string-level variable injection, or a view/RLS policy that doesn't depend on session state.
3. Advisory locks
pg_advisory_lock is session-scoped by design — that's the entire point of the non-transactional variant. Under transaction pooling, "session" and "transaction" are no longer the same duration from the client's point of view, so a lock taken in one statement can vanish (return the backend to the pool, get reassigned) before you release it, or — worse — stay held on a backend that's now serving a completely different client, who has no idea they're blocked on a lock they never asked for. If you need advisory locks under pgbouncer, use pg_advisory_xact_lock, the transaction-scoped variant, and make sure the lock and the work it protects happen inside one transaction, start to finish.
4. LISTEN/NOTIFY
This one doesn't fail with an error, it just silently does nothing useful. LISTEN registers interest on the specific backend connection you're attached to; the moment that connection goes back to the pool, your subscription is gone, and pgbouncer has no concept of forwarding notifications to whoever logically "owns" the listen. If you need pub/sub semantics, put the listener on its own dedicated connection outside the pool (pgbouncer supports a separate pool in session mode for exactly this), or move the pattern to something built for it — Postgres logical replication, or an external queue.
What transaction pooling doesn't break
Ordinary autocommit statements, single-statement transactions, and anything your ORM wraps correctly in BEGIN...COMMIT work exactly as expected — this is 95% of application traffic, which is why transaction pooling is the right default for most services. The failures above cluster specifically around code that assumes a persistent session, which is usually a small, identifiable part of the codebase once you know to look for it: migration tooling, admin scripts, anything doing multi-tenant schema switching, and background workers that hold long-lived connections out of habit rather than need.
Diagnosing it after the fact: if you suspect transaction-pooling breakage in production, grep application logs for
prepared statement ... does not exist, and separately audit every call site that does a bareSEToutside an explicit transaction block. Both are greppable; both are the actual root cause behind a surprising number of "intermittent Postgres errors" tickets that get closed as "retried and it worked."