PgBouncer Modes
PgBouncer pool_mode decides when a server connection returns to the pool. Transaction pooling maximizes density; session pooling maximizes compatibility on PostgreSQL 18.4.
Recipe
Quick-reference recipe card - copy-paste ready.
; pgbouncer.ini
[databases]
appdb = host=127.0.0.1 port=5432 dbname=appdb
[pgbouncer]
pool_mode = transaction
default_pool_size = 50
max_client_conn = 2000When to reach for this: Choosing pool mode for a new service or debugging prepared statement already exists errors.
Working Example
-- Session mode: SET and temp tables persist for client connection lifetime
SET search_path = app, public;
CREATE TEMP TABLE staging (id int);
-- OK in session pooling until disconnect
-- Transaction mode: server connection may switch after COMMIT
BEGIN;
SET LOCAL statement_timeout = '5s';
SELECT count(*) FROM orders;
COMMIT;
-- SET LOCAL is safe; session-level SET without LOCAL is riskyVerify mode with PgBouncer admin: SHOW CONFIG; -> pool_mode.
What this demonstrates:
- Transaction-scoped
SET LOCALvs sessionSET - Temp tables require session mode (or avoid)
- Why ORMs default assumptions matter
Deep Dive
How It Works
- session - Server backend pinned to client until disconnect. Highest compatibility, lowest multiplexing.
- transaction - Backend assigned per transaction; returned at COMMIT/ROLLBACK. Default for web OLTP.
- statement - Backend per statement (rare); breaks multi-statement transactions and most ORMs.
Mode Comparison
| Mode | Multiplexing | Temp tables | Prepared stmts |
|---|---|---|---|
| session | Low | Yes | Yes |
| transaction | High | No | Tricky |
| statement | Highest | No | Broken |
SQL Notes
-- Safe pattern in transaction pooling
BEGIN;
SET LOCAL lock_timeout = '2s';
SELECT * FROM orders WHERE id = 1 FOR UPDATE;
COMMIT;Gotchas
- LISTEN/NOTIFY in transaction mode - Unreliable across backend swaps. Fix: Session pool or dedicated listener connection.
- Advisory locks across requests - Must use session mode. Fix: Separate pool for job workers needing locks.
- Schema migrations via pooler - DDL may need session stickiness. Fix: Direct admin connection bypassing pooler.
- statement mode with Hibernate - Broken transactions. Fix: Never use statement mode for ORMs.
- Forgotten
DEALLOCATE ALLon checkout - Some drivers needserver_reset_query. Fix: Configureserver_reset_query = DISCARD ALLwhere appropriate.
Alternatives
| Alternative | Use When | Don't Use When |
|---|---|---|
| session pooling | Heavy temp table ETL in app | Thousands of idle clients |
| RDS Proxy | AWS managed multiplexing | On-prem K8s |
| Direct connect (no pool) | Local dev only | Production fleet |
FAQs
Default mode for web APIs?
transaction with SET LOCAL and no temp tables.
Does transaction mode break RLS SET?
Use SET LOCAL inside transaction or connection init via startup params.
server_reset_query?
DISCARD ALL clears session state between transactions in transaction mode.
prepared statements?
See dedicated page; often disable in ORM for transaction mode.
multiple databases?
PgBouncer database stanza per target; pools isolated per db/user.
TLS to Postgres?
Pooler connects upstream TLS; clients TLS to pooler.
auth query?
userlist.txt or auth_query for SCRAM with PostgreSQL 18.
transaction pooling and COPY?
COPY in transaction usually OK within single transaction block.
failover?
Pooler config update or DNS to new primary; apps reconnect to pooler.
Next?
Prepared statements pitfalls in transaction mode.
Related
- Prepared Statements & Pooling - ORM fixes
- Pooling Basics - Why pool
- Role & Timeout per Pool - Pool separation
- Pooling Best Practices - Operations
Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.