Postgres connection pooling with PgBouncer — the patterns we run in production

Postgres connections are expensive. A single idle Postgres backend uses 5-15MB of memory and holds OS resources that don't release until the connection closes. Most application frameworks open dozens of connections per instance, multiply by replicas, multiply by environments — and you've quickly got 500-2000 connections to a database that performs best at maybe 100.

This is what PgBouncer is for. We deploy it for almost every managed Postgres customer, and it remains the single highest-leverage piece of Postgres ops tooling.

A baseline transaction-pooling config

[databases]
app = host=db.internal port=5432 dbname=app
 
[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
pool_mode = transaction
max_client_conn = 5000
default_pool_size = 25
server_lifetime = 3600
server_reset_query = DISCARD ALL
max_prepared_statements = 100

Default to transaction pooling. Use session pooling only for the specific subset of workloads that need LISTEN/NOTIFY, advisory locks, or temp tables — ideally on a separate port.

The full write-up covers:

• Session vs transaction vs statement pooling — when each one is right
• Sizing default_pool_size against your actual Postgres max_connections
• Reading SHOW POOLS; during peak load to tune
• Native prepared-statement support in PgBouncer 1.21+ (and removing your client-side workaround)
• One PgBouncer per app host vs centralized cluster — the trade-offs
• auth_query so credentials never leave Postgres

We ship this on every managed Postgres deployment.