The problem
Each database connection costs memory and a backend process. When hundreds of stateless app instances each open their own pool, the database drowns in idle connections long before its CPU is busy.
What a connection proxy does
A connection proxy such as PgBouncer or ProxySQL sits between the app and the database. It keeps a small pool of real backend connections and multiplexes many client connections onto them.
Pooling modes
- Session pooling assigns a backend connection for a whole client session.
- Transaction pooling lends a backend connection only for one transaction, packing far more clients onto few backends.
- Statement pooling is the most aggressive and most restrictive.
What to watch
- Feature limits: transaction pooling breaks session level features like prepared statements and session variables if used carelessly.
- The proxy itself becomes a hop to size, monitor, and make highly available.
Key idea
A database connection proxy multiplexes thousands of app connections onto a handful of real backends, protecting the database from connection exhaustion as long as you respect the pooling mode limits.