Configuring PostgreSQL Connection Pooling with PgBouncer for High Traffic Applications

Introduction

When PostgreSQL databases face high connection volumes, performance can degrade rapidly. Each database connection consumes system resources, and PostgreSQL has practical limits on concurrent connections. PgBouncer, a lightweight connection pooler, solves this problem by maintaining a pool of database connections and efficiently distributing them to client applications.

Why Connection Pooling Matters

The Connection Problem

• Resource Overhead: Each PostgreSQL backend process consumes 5-10MB of memory
• Connection Limits: Default max_connections is typically 100-200
• Startup Cost: Creating new connections takes 1-5ms each
• Context Switching: Too many processes cause CPU thrashing

PgBouncer Benefits

• Reduces database connection count by 10-100x
• Enables thousands of client connections with minimal overhead
• Provides connection queuing during peak loads
• Supports multiple pooling modes for different use cases

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

Directory Structure

/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

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 (10-100x reduction)

pool_mode = transaction
default_pool_size = 25
max_client_conn = 1000

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"

Using PostgreSQL auth_query (Advanced)

Query PostgreSQL directly for authentication:

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

Optimal Configuration for High Traffic

Sizing the Connection Pool

Formula for pool sizing:

default_pool_size = (num_cores × 2) + effective_spindle_count

For a 4-core server with SSD:

default_pool_size = 20
reserve_pool_size = 5
max_client_conn = 1000

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

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"

Solution:

max_client_conn = 5000
default_pool_size = 50

Issue 2: High cl_waiting Count

Solutions:

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

Issue 3: Prepared Statement Errors

Solution:

max_prepared_statements = 0

Performance Impact Examples

Before PgBouncer

• 500 concurrent requests → 500 PostgreSQL connections
• Database load: 95% CPU, 8GB RAM
• Response time: 250ms average

After PgBouncer

• 500 concurrent requests → 25 PostgreSQL connections
• Database load: 35% CPU, 1GB RAM
• Response time: 80ms average
• Result: 3x faster, 70% less resource usage

Conclusion

PgBouncer is essential for scaling PostgreSQL applications. It reduces connection overhead by 90%+, supports 10-100x more clients, and dramatically improves response times. Start with transaction pooling mode and adjust based on monitoring.