Mental model
PostgreSQL uses a process-per-connection architecture (fork() per backend). Opening 1,000 direct database connections consumes gigabytes of RAM and wastes CPU cycles on process context switching. PgBouncer acts as a lightweight proxy multiplexing thousands of client connections into a tiny pool of backend server connections.
1000 Async Client Connections
PgBouncer Proxy Pooler
Multiplex into 20 Backend Processes
PostgreSQL Shared Memory Engine
Theory
- Session Pooling: Assigns a server connection to a client for the entire duration of the client session.
- Transaction Pooling: Assigns a server connection to a client ONLY for the duration of a transaction, returning it to the pool immediately on
COMMIT/ROLLBACK. - Statement Pooling: Returns connections after every SQL query (disables multi-statement transactions).
ini(12 lines)
1; pgbouncer.ini production configuration
2[databases]
3app_db = host=127.0.0.1 port=5432 dbname=app_production
4
5[pgbouncer]
6listen_addr = 0.0.0.0
7listen_port = 6432
8pool_mode = transaction
9max_client_conn = 5000
10default_pool_size = 25
11reserve_pool_size = 5
Alternatives and trade-offs
- Direct Connections: Simple setup; exhausts database RAM when client count scales beyond 100-200 processes.
- Transaction Pooling (PgBouncer): Supports 10,000+ clients; disables session-level features (
SET SESSION,LISTEN/NOTIFY, named prepared statements).
Failure modes and misconceptions
- Prepared Statement Incompatibility: Running legacy ORMs with prepared statements in PgBouncer
pool_mode = transactioncausesprepared statement does not existerrors unless protocol-level prepared statements are supported (PgBouncer 1.21+). - Over-Sizing Backend Pools: Setting
default_pool_size = 500degrades performance. The optimal PostgreSQL backend pool formula is(CPU_cores * 2) + effective_spindle_count.
Reflect before revealing the guide
Decision scenario
Deploy PgBouncer in transaction mode as a sidecar proxy in front of FastAPI / AsyncPG deployments to support 5,000 concurrent web workers with only 30 active PostgreSQL server processes.
Learning outcomes
- Calculate optimal database backend connection limits using CPU core formulas.
- Configure PgBouncer in transaction pooling mode for serverless and containerized workloads.
- Avoid session state leakage when using proxy connection poolers.
Trade-offs
PgBouncer multiplexes thousands of client connections into minimal database processes, but transaction mode restricts session-scoped SQL features.