PgBouncer Guide — Connection Pooling for PostgreSQL at Scale

Why connection pooling matters#

Every PostgreSQL connection consumes memory — roughly 5-10 MB per connection for the backend process. A Postgres server configured with max_connections = 200 may use 1-2 GB just for connection overhead, before any query execution.

Modern applications with hundreds of serverless functions, microservices, or application pods can easily exhaust those connections. A 50-pod deployment with 10 connections each needs 500 connections — far beyond what most Postgres instances can handle.

Connection pooling solves this by multiplexing many application connections over a smaller number of database connections.

What is PgBouncer?#

PgBouncer is a lightweight connection pooler for PostgreSQL. It sits between your application and the database, accepting thousands of client connections and funneling them through a smaller pool of server connections.

Key characteristics:

• Single-threaded, event-loop based (like nginx)
• Extremely low memory footprint (~2 KB per connection)
• Supports three pooling modes
• Drop-in replacement — applications connect to PgBouncer as if it were Postgres
• Battle-tested in production at companies of all sizes

The three pooling modes#

Session pooling#

A server connection is assigned to a client for the entire duration of the client session. The connection returns to the pool only when the client disconnects.

pool_mode = session

Pros: full PostgreSQL feature compatibility — prepared statements, SET commands, advisory locks, LISTEN/NOTIFY all work.

Cons: minimal pooling benefit. If clients hold connections for a long time, you still exhaust the pool.

Use when: you need full Postgres feature compatibility and your clients connect/disconnect frequently.

Transaction pooling#

A server connection is assigned to a client only for the duration of a single transaction. Between transactions, the connection returns to the pool.

pool_mode = transaction

Pros: much better connection reuse. 1,000 application connections can share 50 database connections if transactions are short.

Cons: session-level features break. SET commands, prepared statements (by default), LISTEN/NOTIFY, and advisory locks do not work because your next transaction may run on a different server connection.

Use when: your application uses short transactions and does not rely on session state. This is the most common production mode.

Statement pooling#

A server connection is assigned per individual SQL statement. Multi-statement transactions are not allowed.

pool_mode = statement

Pros: maximum connection reuse.

Cons: no transactions at all. Only useful for simple, stateless query patterns like read replicas or connection-per-query workloads.

Use when: autocommit-only workloads, typically read replicas or analytics queries.

PgBouncer configuration walkthrough#

A production pgbouncer.ini:

[databases]
mydb = host=postgres.internal port=5432 dbname=mydb

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt

; Pooling
pool_mode = transaction
default_pool_size = 25
min_pool_size = 5
reserve_pool_size = 5
reserve_pool_timeout = 3

; Limits
max_client_conn = 5000
max_db_connections = 100

; Timeouts
server_idle_timeout = 300
client_idle_timeout = 0
server_login_retry = 3
query_timeout = 30
client_login_timeout = 15

; Logging
log_connections = 0
log_disconnections = 0
log_pooler_errors = 1
stats_period = 60

Key configuration parameters explained#

default_pool_size — the number of server connections per user/database pair. If you have 3 databases and 2 users, you get 3 * 2 * 25 = 150 server connections maximum.

max_client_conn — maximum number of client connections PgBouncer accepts. Set this high (thousands). Client connections are cheap in PgBouncer.

max_db_connections — hard cap on server connections to a specific database, regardless of pool size. This is your safety valve.

reserve_pool_size — extra connections allowed when the main pool is exhausted. Clients waiting longer than reserve_pool_timeout seconds get a reserve connection.

server_idle_timeout — close server connections that have been idle for this many seconds. Keeps the pool from holding connections unnecessarily.

Connection math#

Your application has 200 pods, each with a connection pool of 10 = 2,000 client connections.

With transaction pooling and default_pool_size = 25:

• PgBouncer holds 25 server connections (per user/db pair)
• 2,000 client connections multiplex through 25 server connections
• Ratio: 80:1

This works because transactions are short (milliseconds). At any moment, only a handful of clients are actively in a transaction.

If your average transaction takes 5 ms and you get 1,000 transactions per second:

• Concurrent server connections needed: 1000 * 0.005 = 5
• A pool of 25 has plenty of headroom

Prepared statements in transaction mode#

The biggest gotcha with transaction pooling: prepared statements. When a client prepares a statement in one transaction, the next transaction may run on a different server connection where that statement does not exist.

Solutions:

PgBouncer 1.21+ with prepared statement tracking:

max_prepared_statements = 100

PgBouncer now intercepts PREPARE/EXECUTE commands and re-prepares statements on the server connection transparently.

Application-side disable:

In many ORMs, disable prepared statements:

# SQLAlchemy
engine = create_engine(url, connect_args={"prepare_threshold": 0})

// node-postgres
const pool = new Pool({
  ...config,
  statement_timeout: 30000,
  // Use simple query protocol
});

Use extended query protocol with PgBouncer 1.21+ — this is now the recommended approach.

Monitoring PgBouncer#

Connect to PgBouncer's admin console:

psql -h localhost -p 6432 -U admin pgbouncer

Key commands:

SHOW POOLS;     -- active, waiting, server connections per pool
SHOW CLIENTS;   -- all client connections
SHOW SERVERS;   -- all server connections
SHOW STATS;     -- queries per second, bytes, wait times
SHOW CONFIG;    -- current configuration

Critical metrics to monitor:

• cl_waiting — clients waiting for a server connection. Non-zero means pool exhaustion.
• sv_active — server connections actively processing queries
• avg_wait_time — average time clients wait for a connection (should be near zero)
• total_query_count — throughput indicator

Alert on cl_waiting > 0 sustained for more than a few seconds.

Odyssey: a multi-threaded alternative#

Odyssey, developed by Yandex, is a multi-threaded connection pooler for PostgreSQL.

Key differences from PgBouncer:

• Multi-threaded — uses multiple CPU cores
• Per-route configuration — different pooling modes for different users/databases
• Built-in TLS termination — no need for a separate TLS proxy
• Graceful client management — can pause and resume clients during failover

# odyssey.conf
storage "postgres_server" {
  type "remote"
  host "postgres.internal"
  port 5432
}

database "mydb" {
  user "app_user" {
    pool "transaction"
    pool_size 25
    storage "postgres_server"
  }
}

When to choose Odyssey over PgBouncer:

• You need multi-core performance
• You have different pooling requirements per user/database
• You want native TLS termination

When to stick with PgBouncer:

• Battle-tested stability
• Simpler configuration
• Wider community support and documentation

Serverless pooling#

Serverless environments (AWS Lambda, Vercel Functions, Cloudflare Workers) are the worst case for database connections. Every function invocation may open a new connection, and cold starts mean connections are constantly churning.

Solutions:

Managed poolers:

• Neon — built-in connection pooling with PgBouncer
• Supabase — PgBouncer included, plus Supavisor (Elixir-based pooler)
• AWS RDS Proxy — managed connection pooler for RDS and Aurora

Self-hosted PgBouncer — deploy PgBouncer as a sidecar or standalone service between your serverless functions and the database.

Connection-per-invocation with pooling:

// Vercel / Next.js with Neon serverless driver
import { neon } from "@neondatabase/serverless";

const sql = neon(process.env.DATABASE_URL);
// Uses HTTP, no persistent connection needed
const users = await sql`SELECT * FROM users WHERE id = ${userId}`;

HTTP-based database drivers (like Neon's serverless driver) sidestep the connection problem entirely by using HTTP instead of the PostgreSQL wire protocol.

PgBouncer deployment patterns#

Sidecar — run PgBouncer in the same pod as your application. Each pod has its own pooler. Simple but duplicates pooling logic.

Centralized — run PgBouncer as a standalone service. All application pods connect to the PgBouncer service. Better connection reuse but adds a network hop.

Per-database — run one PgBouncer instance per database. Isolates pools and failure domains.

The centralized pattern is most common in production.

Common pitfalls#

Idle transactions — a client that begins a transaction and then goes idle holds a server connection hostage. Use server_idle_timeout and query_timeout to reclaim connections.

Too many pools — each unique user/database pair creates a separate pool. 10 databases times 5 users = 50 pools, each with default_pool_size connections.

Not monitoring cl_waiting — pool exhaustion is silent until clients start timing out. Monitor waiting clients proactively.

Forgetting DNS TTL — if your database fails over to a new IP, PgBouncer may cache the old DNS. Set dns_max_ttl appropriately.

Visualize your pooling architecture#

See how PgBouncer sits between your application tier and PostgreSQL — generate an interactive architecture diagram with Codelit.

Key takeaways#

1. Transaction pooling is the sweet spot — 80:1 connection multiplexing for most workloads
2. default_pool_size and max_db_connections are your two most important knobs
3. Prepared statements now work in transaction mode with PgBouncer 1.21+
4. Monitor cl_waiting — non-zero means clients are blocked
5. Odyssey is the multi-threaded alternative when PgBouncer's single thread is a bottleneck
6. Serverless pooling with managed services (Neon, Supabase, RDS Proxy) or HTTP drivers
7. This is article #384 of our ongoing system design series