PgBouncer offers three pooling modes that differ in how aggressively they reuse server connections. Choosing the wrong mode either wastes the pooler's efficiency or breaks your application in subtle ways.
1. Session Pooling (Safest, Least Efficient)
A client gets a server connection when it connects to PgBouncer and holds it for the entire duration of its session — until it disconnects.
- Pros: Completely transparent to the application. All PostgreSQL features work: temporary tables, advisory locks,
SETvariables, prepared statements,LISTEN/NOTIFY. The application behaves exactly as if it connected directly to PostgreSQL. - Cons: Least efficient. If you have 500 application workers that each hold a session open (even while idle), you need 500 server connections. This is better than 500 direct connections (PgBouncer still handles auth and reduces TCP overhead), but it doesn't solve the connection count problem.
- Use when: Your application uses session-level features that can't be moved to transaction scope, or you need a zero-risk migration path.
2. Transaction Pooling (Most Common in Production)
A client gets a server connection only for the duration of a single transaction (from BEGIN to COMMIT or ROLLBACK). Between transactions, the connection is returned to the pool.
- Pros: Dramatically more efficient. A pool of 50 server connections can serve thousands of clients, because most clients are idle between transactions. This is the mode that makes PgBouncer genuinely transformative.
- Cons: Session-level state does not persist between transactions. You cannot use:
SETvariables that need to persist across transactions- Temporary tables (they belong to a session, not a transaction)
- Advisory locks held across transaction boundaries
LISTEN/NOTIFY(requires a dedicated session)- Named prepared statements (PgBouncer can be configured to handle these, but it's complex)
- Use when: Your application is stateless between transactions, which is true for most modern web applications using an ORM.
3. Statement Pooling (Most Aggressive, Rarely Used)
A client gets a server connection for a single SQL statement. The connection is returned immediately after the statement completes.
- Pros: Maximum possible connection reuse. Theoretically the highest concurrency.
- Cons: Multi-statement transactions are completely broken. Each statement in a
BEGIN...COMMITblock may execute on a different physical connection, making atomicity impossible. Only works for simple, auto-commit, single-statement workloads. - Use when: Almost never for OLTP. Occasionally useful for read-only analytics queries that are always single statements.
Choosing the Right Mode
For most teams the answer is transaction pooling: reach for session pooling only if the application relies on temp tables, SET variables, or advisory locks, and reach for statement pooling only for purely single-statement, read-only analytics workloads.
Interviewers often ask: "You switched to PgBouncer transaction pooling and now your application is throwing errors about missing temporary tables. What happened?" The answer is that temporary tables are session-scoped in PostgreSQL. In transaction pooling mode, the session that created the temp table may be a different physical connection than the one executing the next transaction — so the temp table doesn't exist. The fix is either to switch to session pooling, or to refactor the application to use regular tables with explicit cleanup, or to use ON COMMIT DROP temp tables within a single transaction.