PgBouncer: The Complete PostgreSQL Connection Pooling Guide

Your Rails e-commerce app was humming along fine at 2,000 daily users. Then a flash sale hit, concurrent users spiked to 10,000, and PostgreSQL started returning FATAL: sorry, too many clients already. Each Rails Puma worker opened its own database connection — 40 workers across 8 dynos meant 320 connections, and that was before the background job queues joined in. The database server ran out of memory allocating process slots, the OOM killer fired, and your users got a cascade of 503s for eleven minutes until you manually restarted the DB. The fix wasn't more database RAM. It was a connection pooler sitting between your application and PostgreSQL.

PgBouncer is a lightweight, single-binary connection pooler for PostgreSQL. It accepts thousands of client connections, multiplexes them onto a much smaller pool of real server connections, and hands connections back to the pool the moment a transaction (or session) finishes. A properly tuned PgBouncer installation can reduce active server connections from 500 to 20 while supporting 5,000 simultaneous application threads — with no change to application code.

Why PostgreSQL Needs a Connection Pooler

PostgreSQL spawns a dedicated OS process for every client connection. Each process allocates memory for its working set — shared buffers, local sort memory, stack space — typically 5–10 MB per connection before any query runs. At 500 connections, that's 2.5–5 GB of RAM consumed purely by idle process overhead. The kernel scheduler also burns CPU context-switching between hundreds of processes, most of which are waiting for the next query from an application thread that itself is blocked waiting for an HTTP response.

Application frameworks compound the problem. Ruby on Rails with ActiveRecord, Django ORM, and Spring Boot's HikariCP all maintain their own per-thread connection pools. Each application instance connects independently, and connection counts multiply: 10 app servers × 40 threads × 3 microservices = 1,200 PostgreSQL connections before a single user logs in. PostgreSQL's max_connections defaults to 100 and even at a tuned 500, you will hit the ceiling under sustained load.

PgBouncer solves this mismatch. It presents itself to the application as a PostgreSQL server, accepts client connections, and internally maintains a small pool of long-lived server connections to the real PostgreSQL instance. When a client needs a connection, PgBouncer assigns one from the pool; when the client is done, the connection returns to the pool immediately rather than persisting idle. Your application thinks it has 1,200 connections available. PostgreSQL sees 20.

PgBouncer Pooling Modes

PgBouncer offers three pooling modes that differ in when a server connection is returned to the pool. Choosing the right mode is the most consequential configuration decision you will make.

Session Pooling

In session mode, a server connection is assigned to a client when the client connects and held until the client disconnects. This is the safest mode and the default. Every PostgreSQL feature works correctly: prepared statements persist across queries, SET variables survive the session, advisory locks behave as expected, and LISTEN/NOTIFY works normally.

The trade-off is efficiency: session mode provides connection multiplexing only in the sense that PgBouncer can queue clients when all server connections are busy, rather than refusing them outright. If clients hold connections idle for long periods (common in admin tools or long-lived CLI sessions), session mode offers limited throughput improvement over direct connections. Use session mode when your application relies on session-level state or when you are pooling connections from long-lived processes rather than stateless web workers.

Transaction Pooling

Transaction mode returns the server connection to the pool after each transaction commits or rolls back. This is the highest-throughput mode and the most common choice for stateless web applications. A Puma worker that holds a connection idle between requests releases it immediately after its last COMMIT, making the connection available to another worker on a different app server in milliseconds.

The efficiency gains are dramatic. With transaction mode and 100ms average transaction time, a single server connection can serve 10 application threads per second. A pool of 20 server connections can sustain 200 application threads with no queueing — meaning you can run 5,000 application threads and still need fewer than 20 real PostgreSQL connections, as long as not all threads are in a transaction simultaneously.

Statement Pooling

Statement mode returns the server connection to the pool after each individual SQL statement, even within a multi-statement transaction. This means explicit transactions (BEGIN ... COMMIT) spanning multiple statements will execute each statement on potentially different server connections, breaking transactional integrity entirely.

Statement mode is almost never appropriate for application workloads. It was designed for PL/pgSQL stored procedure environments where each stored procedure call is a single statement from the client's perspective. In practice, statement mode breaks too many assumptions about transaction semantics to be useful in web application backends. Leave it set to transaction or session mode.

Pooling Mode Comparison

Installing PgBouncer

PgBouncer is available in the standard Ubuntu and Debian package repositories. For most production deployments, the packaged version is sufficient.

Ubuntu / Debian (apt)

# Update package index and install PgBouncer
sudo apt update
sudo apt install -y pgbouncer

# Check the installed version
pgbouncer --version
# PgBouncer 1.21.0

# Service is disabled by default; we'll enable it after configuration
sudo systemctl status pgbouncer

The package creates the pgbouncer system user, installs the binary at /usr/sbin/pgbouncer, and places configuration files at /etc/pgbouncer/. Logs go to /var/log/postgresql/pgbouncer.log by default.

Building from Source

Build from source when you need a version newer than what your distribution ships, or when you want to enable specific TLS options.

# Install build dependencies
sudo apt install -y build-essential libssl-dev libevent-dev pkg-config

# Download and extract the latest release (check https://www.pgbouncer.org/downloads/ for current version)
wget https://www.pgbouncer.org/downloads/files/1.23.1/pgbouncer-1.23.1.tar.gz
tar -xzf pgbouncer-1.23.1.tar.gz
cd pgbouncer-1.23.1

# Configure with TLS support and install to /usr/local
./configure --prefix=/usr/local --with-openssl
make -j$(nproc)
sudo make install

# Verify installation
/usr/local/bin/pgbouncer --version

pgbouncer.ini Configuration

PgBouncer's configuration lives in a single INI-format file, conventionally at /etc/pgbouncer/pgbouncer.ini. The file has two mandatory sections: [databases], which maps logical database names to real PostgreSQL connection strings, and [pgbouncer], which controls pooling behavior. Authentication credentials live in a separate userlist.txt file.

Database Section

Each entry in the [databases] section creates a named pool that clients connect to. The key on the left is the database name clients use; the value on the right is the real connection string to PostgreSQL.

[databases]
# Clients connect to "myapp" — PgBouncer proxies to localhost:5432, database "myapp_production"
myapp = host=127.0.0.1 port=5432 dbname=myapp_production

# Read-replica pool — clients connect to "myapp_ro"
myapp_ro = host=10.0.1.50 port=5432 dbname=myapp_production

# Wildcard: any database name not listed above routes to the same host
# * = host=127.0.0.1 port=5432

The host=127.0.0.1 form (IPv4 loopback) is preferred over host=localhost on Linux, because the latter triggers a Unix socket connection on some PostgreSQL configurations rather than TCP, which can cause PgBouncer to pick up the wrong socket path.

PgBouncer Section

The [pgbouncer] section controls listening address, pool behavior, and connection limits. The annotated configuration below represents a solid starting point for a production web application.

[pgbouncer]
# Where PgBouncer listens for client connections
listen_addr = 127.0.0.1
listen_port = 6432

# Pooling mode: session | transaction | statement
# Use "transaction" for stateless web apps; "session" for jobs with advisory locks
pool_mode = transaction

# Maximum number of client connections PgBouncer will accept across all pools
# This is the total ceiling — set higher than your application's thread count
max_client_conn = 1000

# Number of server connections to maintain per (database, user) pair
# Formula: start at num_cores * 2, tune based on SHOW POOLS; cl_waiting
default_pool_size = 20

# Extra server connections held in reserve for burst traffic
# When all pool connections are busy, PgBouncer uses up to this many additional connections
reserve_pool_size = 5

# How long (seconds) to wait before using reserve connections (0 = immediately)
reserve_pool_timeout = 3

# Maximum server connections per database across all pools
# Prevents a single database from consuming all PostgreSQL connections
max_db_connections = 100

# Maximum server connections per user across all pools
max_user_connections = 50

# Where to find authentication credentials
auth_file = /etc/pgbouncer/userlist.txt

# Authentication method: scram-sha-256 for PostgreSQL 14+, md5 for older versions
auth_type = scram-sha-256

# Admin interface: connect as user "pgbouncer" to database "pgbouncer" for SHOW commands
admin_users = pgbouncer_admin

# Where to write logs
logfile = /var/log/postgresql/pgbouncer.log
pidfile = /var/run/postgresql/pgbouncer.pid

# Log client connect/disconnect events (useful for debugging; disable in high-traffic production)
log_connections = 0
log_disconnections = 0
log_pooler_errors = 1

# TLS — recommended when PgBouncer and PostgreSQL are on separate hosts
# server_tls_sslmode = require
# server_tls_ca_file = /etc/ssl/certs/ca-certificates.crt

Authentication

PgBouncer does not connect to PostgreSQL to verify passwords by default. Instead, it reads credentials from userlist.txt and validates client authentication itself before passing the connection through to PostgreSQL.

The userlist.txt file format is one entry per line, with the username and password (or password hash) as double-quoted strings separated by a space:

# Generate a scram-sha-256 verifier from the shell (requires PostgreSQL client tools)
# The verifier comes from PostgreSQL's pg_authid.rolpassword column
psql -U postgres -c "SELECT rolname, rolpassword FROM pg_authid WHERE rolname = 'myapp_user';"

# Alternatively, get the password in MD5 format (PostgreSQL 13 and earlier default)
# MD5 format: "md5" + md5(password + username)
echo -n "mysecretpasswordmyapp_user" | md5sum
# Output: a1b2c3d4e5f6... (prepend "md5")

# userlist.txt format (double-quoted, space-separated)
# "username" "password_or_hash"

# /etc/pgbouncer/userlist.txt
# For scram-sha-256 (PostgreSQL 14+): copy the full SCRAM verifier from pg_authid.rolpassword
"myapp_user" "SCRAM-SHA-256$4096:base64salt$base64StoredKey:base64ServerKey"

# For MD5 (PostgreSQL 13 and earlier):
"myapp_user" "md5a1b2c3d4e5f6a1b2c3d4e5f6a1b2c3d4"

# The PgBouncer admin user (password: adminpass)
"pgbouncer_admin" "adminpass"

For session mode, PgBouncer also needs to issue a server_reset_query between client sessions to clean up any session state before the connection is handed to the next client:

# In [pgbouncer] section — only needed for session mode
# DISCARD ALL clears temporary tables, prepared statements, advisory locks, and session variables
server_reset_query = DISCARD ALL

# For transaction mode, leave this empty (no reset between transactions)
# server_reset_query =

Tuning PgBouncer for Production

The default configuration values in the package installation are conservative minimums. Production deployments need tuning based on the actual workload, hardware, and PostgreSQL server capacity.

Pool size formula. The maximum number of effective server connections PostgreSQL can service concurrently is constrained by CPU and I/O capacity, not RAM. A widely cited starting formula for default_pool_size (which translates to the total server connections PgBouncer opens per database/user pair) is:

# Effective server connections formula
# num_cores: physical CPU cores on the PostgreSQL server
# effective_spindle_count: number of independent I/O spindles (1 for SSD/NVMe, disk count for HDD RAID)

max_server_connections = (num_cores * 2) + effective_spindle_count

# Example: 4-core server with NVMe SSD
# max_server_connections = (4 * 2) + 1 = 9
# Round up to 10; add reserve_pool_size of 2 → default_pool_size = 10

# Example: 8-core server with NVMe SSD
# max_server_connections = (8 * 2) + 1 = 17
# Round up to 20; reserve_pool_size = 5 → default_pool_size = 20

This formula, popularized by the HikariCP documentation, reflects the reality that CPUs can only run so many threads simultaneously, and adding more connections beyond that point increases contention rather than throughput. Validate with SHOW POOLS; — if cl_waiting is consistently above zero, increase default_pool_size incrementally.

Timeout settings. Three timeout values control how long idle connections are held before being closed:

[pgbouncer]
# How long (seconds) PgBouncer keeps an idle server connection open before closing it
# Prevents stale connections after PostgreSQL restarts or firewall drops
server_idle_timeout = 600

# How long (seconds) to allow a client connection to sit idle (no queries sent)
# Helps reclaim connections from application threads that opened a connection and stalled
client_idle_timeout = 0

# How long (seconds) to wait for a server connection to become available
# If a client waits longer than this for a pool connection, it receives an error
query_wait_timeout = 120

# Maximum lifetime of a server connection — recycles connections to prevent memory bloat
server_lifetime = 3600

Reserve pool. The reserve_pool_size setting creates a buffer of additional connections above default_pool_size that PgBouncer can draw on when the main pool is exhausted. This absorbs short traffic bursts without forcing clients to queue. Set it to roughly 25% of default_pool_size — a value of 5 alongside default_pool_size=20 gives you 25 total connections available during spikes without permanently allocating the extra capacity.

Monitoring PgBouncer

PgBouncer exposes an administrative interface as a virtual database named pgbouncer. Connect to it with psql on the PgBouncer port to run diagnostic queries.

# Connect to the PgBouncer admin interface
psql -h 127.0.0.1 -p 6432 -U pgbouncer_admin pgbouncer

The three most useful monitoring commands are:

-- SHOW POOLS: connection counts per (database, user) pool
SHOW POOLS;

-- Output columns:
-- database   : the logical database name from [databases]
-- user       : the PostgreSQL role
-- cl_active  : clients currently executing a query (holding a server connection)
-- cl_waiting : clients waiting for a free server connection ← watch this one
-- sv_active  : server connections currently in use
-- sv_idle    : server connections in the pool, available for use
-- sv_used    : server connections returned to pool but not yet reset
-- maxwait    : how long the longest-waiting client has been waiting (seconds)

-- SHOW STATS: throughput and latency metrics per database
SHOW STATS;

-- Key columns:
-- total_query_count  : queries routed through this pool since startup
-- total_query_time   : total microseconds spent executing queries
-- avg_query_time     : average query execution time (microseconds)
-- total_wait_time    : total time clients spent waiting for a connection

-- SHOW CLIENTS: one row per connected client
SHOW CLIENTS;

-- SHOW SERVERS: one row per server connection in the pool
SHOW SERVERS;

-- Reload configuration without restarting (picks up pgbouncer.ini changes)
RELOAD;

For Prometheus-based monitoring, use the pgbouncer_exporter, which scrapes the SHOW POOLS and SHOW STATS output and exposes metrics in Prometheus format. Key metrics to graph are pgbouncer_pools_cl_waiting, pgbouncer_pools_sv_idle, and pgbouncer_stats_avg_query_time.

Common Pitfalls

Transaction mode is the right choice for most web applications, but several common mistakes cause hard-to-debug failures after switching from direct connections or session mode.

Prepared statements in transaction mode. This is the most frequent breakage. ORMs like ActiveRecord (Rails), SQLAlchemy (Python), and many JDBC drivers use server-side prepared statements by default. In transaction mode, the prepared statement is created on one server connection and the next query from the same client may execute on a different server connection that has never seen the prepared statement.

# Rails: disable prepared statements in config/database.yml
production:
  adapter: postgresql
  prepared_statements: false
  host: 127.0.0.1
  port: 6432       # ← PgBouncer port, not 5432
  database: myapp

# Django: pgbouncer mode disables server-side cursors; use django-pgbouncer-listen or set:
# DATABASES = { 'default': { ..., 'DISABLE_SERVER_SIDE_CURSORS': True } }

# Node.js (pg / node-postgres): use simple query protocol, not extended query protocol
# pool = new Pool({ statement_timeout: 30000 })  # Simple queries work fine

Advisory locks in transaction mode. PostgreSQL's advisory locking functions (pg_advisory_lock, pg_try_advisory_lock) are session-scoped. When a connection returns to the PgBouncer pool at the end of a transaction, the advisory lock is released — even if you acquired it with the intent of holding it across multiple transactions. This silently breaks distributed locking patterns used by background job libraries (Delayed::Job, Good Job, Que). These libraries must either use a separate session-mode PgBouncer listener or connect directly to PostgreSQL.

LISTEN/NOTIFY. LISTEN channel registers a subscription on the current server connection. In transaction mode, that connection returns to the pool after each transaction, and the subscription is lost. Any feature relying on NOTIFY — real-time updates, pg-boss job notifications, ActionCable with PostgreSQL adapter — requires a dedicated persistent connection outside the PgBouncer pool.

Connection strings pointing to the wrong port. After installing PgBouncer, applications must connect to port 6432 (PgBouncer) rather than 5432 (PostgreSQL). This sounds obvious, but in containerized environments with service discovery, DNS aliases like postgres often resolve to the PgBouncer sidecar rather than the database. Verify with SHOW VERSION; — a PgBouncer connection returns PgBouncer in the response rather than a PostgreSQL version string.

Working with JusDB on PgBouncer

JusDB configures and manages PgBouncer for PostgreSQL deployments handling thousands of concurrent connections. We size pool modes, tune timeouts, and monitor connection saturation — keeping your database responsive under any traffic spike.

Explore JusDB PostgreSQL Services →  |  Talk to a DBA

Related reading:

• PostgreSQL PITR and Backup
• PostgreSQL Explained
• PostgreSQL Schema Design