A pooler in transaction mode hands each transaction a server connection and takes it back at COMMIT. Anything that expects the connection to remember something between transactions will break, often without an error.
The checklist
- Session settings:
SET search_pathorSET timezoneoutside a transaction won't stick. - Prepared statements: older drivers prepare once per connection and then fail with
prepared statement "s1" does not exist. - Advisory locks taken with
pg_advisory_lockbelong to a connection you no longer own. LISTEN/NOTIFYneeds a dedicated connection that skips the pool.
A minimal config to start from
[pgbouncer]
pool_mode = transaction
max_client_conn = 2000
default_pool_size = 20
server_reset_query =
max_prepared_statements = 100
Load test with the pooler in place before launch day. The bugs it exposes are much cheaper to find on a Tuesday afternoon.