PgBouncer in Production: Pool Modes, Sizing, and Safe Authentication
Date Published
Why connection pooling matters
A PostgreSQL backend is a real operating-system process with memory and scheduling cost. Large fleets of short-lived application connections can spend more time establishing sessions than doing work. PgBouncer accepts many client connections and maps active transactions onto a smaller, controlled set of PostgreSQL backends.
Choose the pool mode deliberately
Transaction pooling is the best default for stateless web workloads because a server connection returns to the pool after each transaction. Session pooling preserves session state but provides less multiplexing. Statement pooling is restrictive and rarely appropriate. Transaction pooling requires applications to avoid session-scoped assumptions such as persistent temporary tables, session advisory locks, and some prepared-statement patterns unless explicitly supported and tested.
Size from database capacity backward
Start with the number of concurrent database backends PostgreSQL can sustain, reserve capacity for administration and maintenance, then divide the remaining budget across PgBouncer pools. max_client_conn is not a performance target; it is an admission ceiling. default_pool_size, reserve_pool_size, queue time, active clients, waiting clients, and server utilization must be monitored together.
Use secure authentication
Do not use trust authentication for a production pool. Match PgBouncer authentication to PostgreSQL policy, protect the user list, and prefer an auth_query design when central role management is required. With SCRAM-enabled PostgreSQL, validate the exact PgBouncer version and credential format. Encrypt client-to-pool and pool-to-database links when they cross trust boundaries.
Place PgBouncer with HA in mind
A pooler does not discover a new primary by magic. Point it at a stable writer endpoint such as a VIP or HAProxy listener, or run poolers close to applications with controlled DNS behavior. During failover, terminate stale server connections and verify that queued clients reconnect cleanly. Test prepared statements, transaction retries, and application connection lifetimes before production cutover.