PgBouncer & Connection Pooling

Learning outcomes

• Configure PgBouncer transaction pooling mode
• Calculate optimal database connection pool limits

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.

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).

; pgbouncer.ini production configuration
[databases]
app_db = host=127.0.0.1 port=5432 dbname=app_production

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
pool_mode = transaction
max_client_conn = 5000
default_pool_size = 25
reserve_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

1. Prepared Statement Incompatibility: Running legacy ORMs with prepared statements in PgBouncer pool_mode = transaction causes prepared statement does not exist errors unless protocol-level prepared statements are supported (PgBouncer 1.21+).
2. Over-Sizing Backend Pools: Setting default_pool_size = 500 degrades performance. The optimal PostgreSQL backend pool formula is (CPU_cores * 2) + effective_spindle_count.

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.

Primary sources

• PostgreSQL 16 Architecture, MVCC & Query Optimization Manual — PostgreSQL Global Development Group, verified 2026-07-22