PgBouncer

PgBouncer is a lightweight connection pooler for PostgreSQL. Postgres forks an OS process per connection (~5-10 MB resident memory each), so hundreds of application connections quickly exhaust memory. PgBouncer sits between the application and Postgres, maintaining a small pool of real backend connections and multiplexing many application connections onto them.

Pooling modes

PgBouncer supports three pooling modes with different isolation guarantees:

Mode	When server connection is reused	Notes
Session	When the client disconnects	Safest — full connection semantics preserved. Effectively no pooling benefit beyond limiting max connections.
Transaction	After each transaction completes	The standard production mode. SET LOCAL and BEGIN/COMMIT work correctly. Most SET settings are reset between transactions.
Statement	After each statement	Disallows multi-statement transactions. Rarely used.

Transaction mode is the right default for most OLTP applications.

SET LOCAL and connection safety

When PgBouncer reuses a connection, session-level state (search path, SET variables) from the previous transaction can leak to the next client. This is why Row-Level Security policies must use SET LOCAL (or set_config(..., true)) rather than SET — LOCAL scopes the setting to the current transaction and is automatically cleared when the transaction ends, so the next client on the same connection cannot inherit it.

Configuration

[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb
 
[pgbouncer]
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20

default_pool_size is the number of real Postgres connections per database+user pair. Keep this well below max_connections in postgresql.conf.

See also

• Row-Level Security — how SET LOCAL interacts with connection poolers
• Multi-version concurrency control — Postgres connection model background