PostgreSQL Connection Pooling with PgBouncer — Transaction vs Session Pooling

PostgreSQL uses one OS process per connection, not a thread. Each idle connection still costs five to ten megabytes of memory and takes up a slot against max_connections. Once you run a handful of pods each with a HikariCP pool, you are past the default limit.

PgBouncer is the external pooler

• It sits between the application and PostgreSQL, usually on port 6432
• It accepts many client connections and maintains a small pool of real PostgreSQL connections
• In transaction pooling mode, a server connection is held only while a transaction is running, so 30 real connections can serve around a thousand idle-between-transaction clients

Pooling modes

• Transaction pooling is best for stateless Spring Boot services
• Session pooling holds the connection for the whole client session, which gives full PostgreSQL compatibility but far less multiplexing
• Statement pooling is rarely useful because it breaks multi-statement transactions

What breaks under transaction pooling

• Server-side prepared statements — fixed in PgBouncer 1.21+ or by disabling client-side prepared statements in HikariCP
• LISTEN/NOTIFY, session-level SET, and pg_advisory_lock — use their transaction-scoped equivalents or switch to session pooling

Sizing

• Start at roughly two times the CPU cores plus effective spindle count
• Tune up only if PgBouncer's SHOW POOLS shows cl_waiting is consistently non-zero

Two-tier pattern

• HikariCP manages the app-side pool, PgBouncer the server-side
• Monitor both: HikariCP pending connections means raise the app pool; PgBouncer cl_waiting means raise the server pool
• RDS Proxy is the managed alternative with IAM auth and transparent failover, at ~1–2 ms latency cost per query