Connection Pooling for PostgreSQL with PgBouncer

Each PostgreSQL connection is a separate server process using memory. Hundreds of app instances each opening twenty connections will exhaust max_connections and slow the database down. Pooling fixes this.

Layer 1: the application pool

import pg from 'pg';

export const pool = new pg.Pool({
  connectionString: process.env.DATABASE_URL,
  max: 10,                      // per app instance
  idleTimeoutMillis: 30_000,
  connectionTimeoutMillis: 5_000,
});

Total connections = instances x max. With autoscaling or serverless functions, that number can explode.

Layer 2: PgBouncer

PgBouncer sits between apps and Postgres. Thousands of client connections share a small number of real database connections.

; pgbouncer.ini
[databases]
app = host=10.0.0.5 port=5432 dbname=app

[pgbouncer]
listen_port = 6432
pool_mode = transaction
max_client_conn = 2000
default_pool_size = 30
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt

Pool modes

• Session - a client keeps one server connection until it disconnects. Most compatible, least sharing.
• Transaction - a server connection is assigned only for the duration of a transaction. Most efficient and the usual choice.
• Statement - per statement; rarely used.

Transaction-mode caveats

• Session state doesn't stick between transactions: SET without LOCAL, session advisory locks, LISTEN, temporary tables.
• Prepared statements needed special handling historically; recent PgBouncer versions support protocol-level prepared statements (max_prepared_statements).
• Run migrations and admin tasks through a direct connection, not the pooler.

Sizing

More database connections isn't faster. A database with N CPU cores often performs best with a few times N active connections. Measure throughput and latency as you adjust default_pool_size.

Many managed Postgres services include a built-in pooler (often PgBouncer-based); use it rather than running your own.