Configuring PostgreSQL Connection Pooling with PgBouncer for High Traffic Applications

When PostgreSQL databases face high connection volumes, performance can degrade quickly. Each client connection maps to a PostgreSQL backend process, so a busy web app can spend too much memory and CPU just keeping sessions open. PgBouncer connection pooling reduces that pressure by letting many client connections reuse a smaller set of server connections.

Why Connection Pooling Matters

The Connection Problem

• Resource overhead: Every PostgreSQL connection has a backend process and memory overhead.
• Connection limits: max_connections is finite, and raising it too high can make the database less stable instead of faster.
• Startup cost: Creating fresh database connections adds latency.
• Context switching: Too many active backend processes can waste CPU.

PgBouncer Benefits

• Lets many application clients share fewer PostgreSQL server connections.
• Queues clients when the pool is busy instead of overwhelming the database.
• Supports session, transaction, and statement pooling modes.
• Gives you operational commands such as SHOW POOLS, SHOW CLIENTS, and RELOAD.

Installation and Basic Setup

Installing PgBouncer

On Ubuntu/Debian:

sudo apt update
sudo apt install pgbouncer

On CentOS/RHEL:

sudo yum install pgbouncer

On macOS:

brew install pgbouncer

Common File Locations

/etc/pgbouncer/
  ├── pgbouncer.ini        # Main configuration
  └── userlist.txt         # Authentication credentials

Configuration File Setup

Basic pgbouncer.ini Configuration

[databases]
; database_name = host=hostname port=5432 dbname=actual_db
myapp = host=localhost port=5432 dbname=production_db

[pgbouncer]
; Connection pooling mode
pool_mode = transaction

; Maximum connections
max_client_conn = 1000
default_pool_size = 25
reserve_pool_size = 5
reserve_pool_timeout = 3

; Networking
listen_addr = 0.0.0.0
listen_port = 6432

; Authentication
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt

; Logging
log_connections = 1
log_disconnections = 1
log_pooler_errors = 1

; Performance
max_prepared_statements = 0

Package defaults vary by distribution, so check the installed service file and config path after installation. On many Linux packages, /etc/pgbouncer/pgbouncer.ini is the main file.

Understanding Pooling Modes

1. Session Pooling (pool_mode = session)

• Behavior: Connection assigned to client for entire session
• Use Case: Applications using temporary tables, prepared statements
• Efficiency: Low (1:1 connection ratio)

pool_mode = session

2. Transaction Pooling (pool_mode = transaction) - Recommended

• Behavior: Connection returned to pool after each transaction
• Use Case: Most web applications with short transactions
• Efficiency: High, when your app does not rely on session state

pool_mode = transaction
default_pool_size = 25
max_client_conn = 1000

Transaction pooling is a strong default for request/response web apps, but it can break assumptions around session-level features. Be careful with temporary tables, session variables, advisory locks held outside a transaction, LISTEN/NOTIFY, and driver-level prepared statements unless you have tested them with your PgBouncer version and settings.

3. Statement Pooling (pool_mode = statement)

• Behavior: Connection returned after each statement
• Use Case: Simple read-only queries without transactions
• Efficiency: Maximum (but very restrictive)

pool_mode = statement
; Use with caution - breaks multi-statement transactions

Authentication Setup

Creating userlist.txt

PgBouncer requires a separate authentication file. Generate MD5 hash and add to userlist.txt.

Example userlist.txt:

"app_user" "md5d8578edf8458ce06fbc5bb76a58c5ca4"
"readonly_user" "md5a3c7f5e89d24e7c8b1f9d2e4a6c8b0d2"

For PostgreSQL-style MD5 passwords, the value is md5 plus the MD5 hash of password + username. Do not paste fake hashes into production; generate entries from your real user names and passwords, or use a safer authentication method supported by your environment.

Using PostgreSQL auth_query (Advanced)

PgBouncer can query PostgreSQL for user credentials when a user is not found in auth_file, but this requires an auth_user that PgBouncer can log in as. A minimal example looks like this:

auth_type = md5
auth_user = pgbouncer_auth
auth_query = SELECT usename, passwd FROM pg_shadow WHERE usename=$1

Limit the privileges of the authentication user and follow your PostgreSQL version's guidance. Many teams use a SECURITY DEFINER function instead of granting direct access to catalog password data.

Optimal Configuration for High Traffic

Sizing the Connection Pool

There is no universal pool-size formula. Start with a conservative value based on the number of queries your database can actively run well, then tune from real metrics.

For a typical web app, you might start here and adjust:

default_pool_size = 25
reserve_pool_size = 5
max_client_conn = 1000

Watch PostgreSQL CPU, query latency, lock waits, and PgBouncer's cl_waiting count. If clients wait while the database is idle, the pool may be too small. If the database is saturated, increasing the pool can make things worse.

Complete Production Configuration

[databases]
production = host=db.example.com port=5432 dbname=prod_db pool_size=30
analytics = host=db-replica.example.com port=5432 dbname=prod_db pool_size=15

[pgbouncer]
pool_mode = transaction

; Connection limits
max_client_conn = 2000
default_pool_size = 25
min_pool_size = 10
reserve_pool_size = 8
reserve_pool_timeout = 3
server_lifetime = 3600
server_idle_timeout = 600

; Networking
listen_addr = 0.0.0.0
listen_port = 6432
so_reuseport = 1
pkt_buf = 8192

; Security
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
ignore_startup_parameters = extra_float_digits,options

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

; Performance
max_prepared_statements = 0
query_timeout = 30
query_wait_timeout = 120

max_prepared_statements = 0 disables PgBouncer's prepared statement tracking. Recent PgBouncer versions can support protocol-level prepared statements in transaction pooling when this is configured above zero, but you should test your driver and workload before enabling it.

Application Connection String

Before PgBouncer

# Direct PostgreSQL connection
DATABASE_URL = "postgresql://user:[email protected]:5432/mydb"

After PgBouncer

# Connect through PgBouncer
DATABASE_URL = "postgresql://user:[email protected]:6432/mydb"

Monitoring and Management

Admin Console Commands

Connect to PgBouncer admin console:

psql -h localhost -p 6432 -U pgbouncer pgbouncer

Essential commands:

-- Show pool statistics
SHOW POOLS;

-- Show active connections
SHOW CLIENTS;
SHOW SERVERS;

-- Show configuration
SHOW CONFIG;

-- Reload configuration
RELOAD;

Troubleshooting Common Issues

Issue 1: "no more connections allowed"

This can mean PgBouncer is rejecting client connections or PostgreSQL is rejecting server connections. Check where the error appears.

Possible PgBouncer-side changes:

max_client_conn = 5000
default_pool_size = 50

Before increasing both, confirm your OS file descriptor limit and PostgreSQL max_connections can support the new totals. PgBouncer itself also needs enough file descriptors for client and server sockets.

Issue 2: High cl_waiting Count

Solutions:

1. Increase pool size
2. Optimize slow queries
3. Add reserve pool

Issue 3: Prepared Statement Errors

If your app or driver uses prepared statements and you are in transaction pooling mode, you may see errors when a later statement lands on a different server connection. Options include:

1. Disable driver-side prepared statements.
2. Use session pooling for that workload.
3. Test PgBouncer prepared statement support with a positive max_prepared_statements value on a recent PgBouncer version.

Conservative setting:

max_prepared_statements = 0

Practical Deployment Example

Imagine your app servers can open hundreds of concurrent HTTP requests, but the database runs best with a few dozen active queries. Point the app at PgBouncer on port 6432, set max_client_conn high enough for client bursts, and keep default_pool_size close to the number of database connections you actually want active for that database/user pair.

Then validate with:

SHOW POOLS;
SHOW STATS;

If cl_waiting stays above zero during normal traffic, investigate slow queries before simply raising pool size. PgBouncer protects PostgreSQL from connection storms, but it does not make slow SQL cheap.

Practical Takeaway

Start with transaction pooling for stateless web workloads, keep pool sizes intentionally small, and tune from PgBouncer plus PostgreSQL metrics. If your app depends on session behavior or prepared statements, test those paths before putting PgBouncer in front of production traffic.