The connection cost
Each Postgres connection is backed by its own operating system process with real memory overhead. A few hundred is fine, but thousands of idle application connections exhaust memory and the scheduler. A connection pooler like pgbouncer sits between clients and the database and multiplexes many client connections onto a small set of real server connections.
Pool modes
Pgbouncer offers three modes that differ in when a server connection is handed back:
- Session pooling assigns a server connection for the whole client session. Safest but offers the least sharing.
- Transaction pooling returns the server connection to the pool at the end of each transaction. This is the common choice and allows far more clients than servers.
- Statement pooling returns it after every statement, which forbids multi statement transactions entirely.
The trade offs
Transaction pooling breaks features tied to a session, because the next statement may land on a different backend:
- Prepared statements, session level SET values, advisory locks, and LISTEN or NOTIFY can misbehave.
- Applications must avoid relying on session state surviving across transactions.
In return you can serve thousands of clients with a few dozen backends.
Key idea
A pooler multiplexes many client connections onto few Postgres backends, and transaction mode gives the best sharing but breaks session bound features like prepared statements and session level SET.