Overview
A connection pooler sits between an application and the database, reusing a small, fixed set of real server connections across many more client connections, which matters because opening a real database connection has real overhead and most databases have a hard connection limit. Session pooling is the most conservative mode, one server connection stays assigned to a client for their entire session, supporting every PostgreSQL feature but scaling only as far as the connection limit directly allows. Transaction pooling reassigns the server connection at every transaction boundary instead, dramatically improving connection reuse for applications with many short-lived connections, but it explicitly breaks session-level features, prepared statements, SET/RESET, LISTEN, and session-level advisory locks among them, because none of those can rely on landing on the same server connection twice.
Quick Reference
| Pooling mode | Connection assigned for | Supports all features? |
|---|---|---|
| Session pooling | The entire client session | Yes |
| Transaction pooling | A single transaction | No, breaks session-level features |
| Statement pooling | A single statement (autocommit only) | No, also disallows multi-statement transactions |
| Feature broken by transaction pooling | Why |
|---|---|
SQL PREPARE/DEALLOCATE | Statement lives on one specific server connection (protocol-level named prepared statements can work under transaction pooling if max_prepared_statements is non-zero) |
SET/RESET | Session-level configuration tied to one connection |
LISTEN | Notification subscription tied to one persistent connection |
| Session-level advisory locks | Lock is held by a specific connection, not the client |
Syntax
; pgbouncer.ini
[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb
[pgbouncer]
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20Examples
-- Breaks under transaction pooling: the PREPARE may land on a
-- different server connection than the later EXECUTE.
PREPARE get_user AS SELECT * FROM users WHERE id = $1;
-- ... later, possibly on a different pooled connection ...
EXECUTE get_user(42);
-- Error: prepared statement "get_user" does not existTransaction pooling breaks more than people expect
Prepared statements, SET/RESET, LISTEN, WITH HOLD cursors, and session-level advisory locks all depend on state tied to one specific server connection. Transaction pooling's efficiency comes precisely from not guaranteeing that, audit for these features before switching pool modes.
Visual Diagram
Common Mistakes
- Switching to transaction pooling for better scalability without auditing the application for prepared statements,
LISTEN, or session-level advisory locks first. - Assuming pooling mode is purely a performance knob with no functional consequences, when transaction and statement pooling both explicitly remove PostgreSQL feature support.
- Sizing the pool to match expected concurrent clients instead of expected concurrent transactions, overprovisioning real server connections against session pooling, or underprovisioning against a database's actual connection limit.
- Choosing statement pooling without realizing it disallows multi-statement transactions entirely, a much stronger constraint than transaction pooling.
Performance
- Real database connections have meaningful setup cost and are capped by a hard server-side limit; pooling directly reduces both the cost and the count needed for a given level of application concurrency.
- Transaction pooling supports substantially higher client concurrency per real server connection than session pooling, precisely because idle-between-transactions clients hold no server connection at all.
Best Practices
- Choose transaction pooling by default for typical web application workloads with many short-lived connections, provided the application doesn't depend on session-level features.
- Audit for
PREPARE,SET/RESET,LISTEN, and session-level advisory locks before adopting transaction or statement pooling, and refactor around them or fall back to session pooling if they're required. - Size the pool based on expected concurrent transactions (for transaction pooling) or concurrent sessions (for session pooling), not a single blanket number.
- Treat pooling mode as a feature-compatibility decision as much as a performance one, not purely a scalability dial.