Database Connection Pooling with PgBouncer

PgBouncer is a lightweight connection pooler for PostgreSQL that efficiently manages database connections, reducing resource consumption and improving application performance. It acts as a proxy between applications and PostgreSQL servers, maintaining a pool of persistent database connections while appearing to clients as individual connections. This guide covers installation, configuration, connection pool modes, authentication setup, monitoring, and tuning for production deployments.

Table of Contents

Overview and Benefits

PgBouncer reduces the overhead of establishing PostgreSQL connections, which is expensive due to authentication overhead and memory allocation. Each PostgreSQL connection consumes memory and creates a new backend process, limiting the number of simultaneous connections a server can accept. PgBouncer multiplexes many client connections onto fewer server connections, reducing resource consumption while maintaining connection semantics.

Connection pooling improves throughput for workloads with many short-lived connections and reduced memory usage for server instances. The lightweight nature of PgBouncer makes it suitable for deployment on application servers or dedicated pooler instances.

Installation

Install PgBouncer on Ubuntu/Debian systems:

# Install from repositories
sudo apt-get update
sudo apt-get install -y pgbouncer

# Or build from source
cd /tmp
wget https://pgbouncer.github.io/downloads/files/1.18.0/pgbouncer-1.18.0.tar.gz
tar -xzf pgbouncer-1.18.0.tar.gz
cd pgbouncer-1.18.0
./configure --prefix=/usr/local
make
sudo make install

# Verify installation
pgbouncer --version
which pgbouncer

On CentOS/RHEL:

# Install from EPEL repository
sudo dnf install -y pgbouncer

# Or build from source
sudo dnf install -y gcc libc-devel openssl-devel c-ares-devel pam-devel
# Then follow source installation steps

Create dedicated user and directories:

# Create pgbouncer user
sudo useradd -r -s /bin/false pgbouncer 2>/dev/null || true

# Create configuration directory
sudo mkdir -p /etc/pgbouncer
sudo chown pgbouncer:pgbouncer /etc/pgbouncer
sudo chmod 750 /etc/pgbouncer

# Create data directory
sudo mkdir -p /var/lib/pgbouncer
sudo chown pgbouncer:pgbouncer /var/lib/pgbouncer

# Create log directory
sudo mkdir -p /var/log/pgbouncer
sudo chown pgbouncer:pgbouncer /var/log/pgbouncer

Configuration File

Create the main PgBouncer configuration file:

sudo nano /etc/pgbouncer/pgbouncer.ini

Add comprehensive configuration:

[databases]
# Database name = connection string
myapp = host=192.168.1.10 port=5432 dbname=myapp
analytics = host=192.168.1.11 port=5432 dbname=analytics
default = host=192.168.1.10 port=5432

[pgbouncer]
# Listen address
listen_addr = 0.0.0.0
listen_port = 6432

# PostgreSQL server address (for default connections)
admin_users = postgres
stats_users = postgres

# Connection pool settings
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 25
min_pool_size = 10
reserve_pool_size = 5
reserve_pool_timeout = 3

# Server connection timeouts
server_lifetime = 3600
server_idle_in_transaction_session_timeout = 60
server_connect_timeout = 15
server_login_retry = 15
query_timeout = 0
query_wait_timeout = 120
idle_in_transaction_session_timeout = 0

# Connection aging
max_db_connections = 100
max_user_connections = 100
autodb_idle_timeout = 3600

# Protocol and parser settings
pkt_buf = 4096
sbuf_lookahead = 0
ignore_startup_parameters = extra_float_digits
tcp_keepalives = 1
tcp_keepidles = 30
tcp_keepintvl = 10
tcp_keepcnt = 5

# Authentication
auth_type = md5
auth_user = pgbouncer
auth_dbname = pgbouncer

# Logging
log_connections = 1
log_disconnections = 1
log_pooler_errors = 1
log_queries = 0
log_file = /var/log/pgbouncer/pgbouncer.log
pidfile = /var/run/pgbouncer/pgbouncer.pid
unix_socket_dir = /var/run/postgresql
unix_socket_mode = 0777

# Management
admin_users = postgres
stats_users = postgres,pgbouncer

Set proper permissions:

sudo chown pgbouncer:pgbouncer /etc/pgbouncer/pgbouncer.ini
sudo chmod 600 /etc/pgbouncer/pgbouncer.ini

Create a systemd service file:

sudo nano /etc/systemd/system/pgbouncer.service

Add:

[Unit]
Description=PgBouncer - PostgreSQL connection pooler
Documentation=https://www.pgbouncer.org/
After=network.target postgresql.service
Wants=postgresql.service

[Service]
Type=simple
User=pgbouncer
Group=pgbouncer
WorkingDirectory=/var/lib/pgbouncer

ExecStart=/usr/sbin/pgbouncer -u pgbouncer /etc/pgbouncer/pgbouncer.ini
ExecReload=/bin/kill -HUP $MAINPID
Restart=always
RestartSec=10

StandardOutput=journal
StandardError=journal

[Install]
WantedBy=multi-user.target

Enable and start the service:

sudo systemctl daemon-reload
sudo systemctl enable pgbouncer
sudo systemctl start pgbouncer
sudo systemctl status pgbouncer

# Monitor logs
sudo journalctl -u pgbouncer -f

Pool Modes

Configure the appropriate pool mode for your workload:

# Transaction mode - reuses connections for each transaction
# Best for web applications with many short transactions
pool_mode = transaction
server_lifetime = 3600
server_idle_in_transaction_session_timeout = 60

# Session mode - connection reused for entire session
# Good for applications maintaining persistent connections
pool_mode = session
server_lifetime = 86400

# Statement mode - connection returned after each query
# Use only with simple applications
pool_mode = statement

Understanding pool mode implications:

# Transaction mode (default)
# - Connection released after COMMIT/ROLLBACK
# - Some session state may be lost between transactions
# - Best throughput with transaction pooling

# Session mode
# - Connection held for entire client session
# - Maintains all session state
# - Lower overhead but fewer multiplexing benefits

# Statement mode
# - Connection released after each statement
# - Most aggressive pooling
# - Only works with stateless query patterns

User Authentication

Configure authentication between PgBouncer and PostgreSQL:

# Create userlist.txt for PgBouncer authentication
sudo nano /etc/pgbouncer/userlist.txt

Add user entries:

"postgres" "password_hash"
"app_user" "password_hash"
"readonly_user" "password_hash"

Generate MD5 password hashes:

# Generate hash for password
echo -n "mypassword" | md5sum
# Output: 2f0ead2b5fb0ad6dcaba0d76cfd8f920  -

# Format as: "username" "md5hash"
# "postgres" "md52f0ead2b5fb0ad6dcaba0d76cfd8f920"

Or extract hashes from PostgreSQL:

# Connect to PostgreSQL
psql -U postgres

-- Get MD5 hashes of users
SELECT usename, 'md5' || passwd FROM pg_shadow;

-- Exit and copy hashes to userlist.txt
\q

Set proper permissions on userlist.txt:

sudo chown pgbouncer:pgbouncer /etc/pgbouncer/userlist.txt
sudo chmod 600 /etc/pgbouncer/userlist.txt

Configure PostgreSQL to accept PgBouncer connections:

# Edit PostgreSQL pg_hba.conf
sudo nano /var/lib/postgresql/15/main/pg_hba.conf

Add:

# Allow PgBouncer connections
host    all             all             192.168.1.10/32         md5
host    all             all             127.0.0.1/32            md5

# Allow pgbouncer user for connection management
local   all             pgbouncer                               trust

Configure PgBouncer database in PostgreSQL:

# Connect as postgres
psql -U postgres

-- Create pgbouncer database
CREATE DATABASE pgbouncer;

-- Create pgbouncer user
CREATE USER pgbouncer WITH PASSWORD 'pgbouncer_password';
GRANT CONNECT ON DATABASE pgbouncer TO pgbouncer;

-- Grant required permissions
ALTER ROLE pgbouncer SUPERUSER;

Client Connection Management

Configure client connection behavior:

# Limits on client connections
echo "max_client_conn = 1000" | sudo tee -a /etc/pgbouncer/pgbouncer.ini
echo "default_pool_size = 25" | sudo tee -a /etc/pgbouncer/pgbouncer.ini

# Set client timeouts
echo "client_idle_timeout = 900" | sudo tee -a /etc/pgbouncer/pgbouncer.ini
echo "client_login_timeout = 60" | sudo tee -a /etc/pgbouncer/pgbouncer.ini

# Reload configuration
sudo kill -HUP $(pgrep pgbouncer)

Handle connection limits gracefully:

# Set server-side limits
echo "max_user_connections = 100" | sudo tee -a /etc/pgbouncer/pgbouncer.ini

# Configure pause on high load
echo "pause_mode = pause" | sudo tee -a /etc/pgbouncer/pgbouncer.ini

Monitoring and Statistics

Monitor PgBouncer performance:

# Connect to admin interface
psql -h localhost -p 6432 -U postgres -d pgbouncer

-- Show statistics
SHOW stats;

-- Show client connections
SHOW clients;

-- Show server connections
SHOW servers;

-- Show database status
SHOW databases;

-- Show pools
SHOW pools;

-- Show configuration
SHOW config;

-- Reset statistics
RESET stats;

-- Exit
\q

Create monitoring script:

#!/bin/bash
# /usr/local/bin/pgbouncer-monitor.sh

PGBOUNCER_HOST="localhost"
PGBOUNCER_PORT="6432"
PGBOUNCER_USER="postgres"

echo "=== PgBouncer Statistics ==="
psql -h $PGBOUNCER_HOST -p $PGBOUNCER_PORT -U $PGBOUNCER_USER -d pgbouncer \
  -c "SELECT now() as timestamp, * FROM pgbouncer.stats ORDER BY name;" 2>/dev/null

echo ""
echo "=== Connection Status ==="
psql -h $PGBOUNCER_HOST -p $PGBOUNCER_PORT -U $PGBOUNCER_USER -d pgbouncer \
  -c "SELECT database, count(*) as connections FROM pgbouncer.clients GROUP BY database;" 2>/dev/null

echo ""
echo "=== Pool Status ==="
psql -h $PGBOUNCER_HOST -p $PGBOUNCER_PORT -U $PGBOUNCER_USER -d pgbouncer \
  -c "SELECT * FROM pgbouncer.pools;" 2>/dev/null

Run monitoring script periodically:

chmod +x /usr/local/bin/pgbouncer-monitor.sh
/usr/local/bin/pgbouncer-monitor.sh

# Schedule with cron
(crontab -l 2>/dev/null; echo "*/5 * * * * /usr/local/bin/pgbouncer-monitor.sh >> /var/log/pgbouncer-monitor.log") | crontab -

Performance Tuning

Optimize PgBouncer for your workload:

# Calculate appropriate pool size
# pool_size = (connections_needed / app_servers) + reserve_for_failover
# Typical: 20-40 connections per database

sudo nano /etc/pgbouncer/pgbouncer.ini

# Adjust for high throughput
pool_mode = transaction
default_pool_size = 50
reserve_pool_size = 10
reserve_pool_timeout = 3

# For many concurrent connections
max_client_conn = 5000
default_pool_size = 20

# Tune network parameters
tcp_user_timeout = 60000
tcp_keepidles = 30
tcp_keepintvl = 10
tcp_keepcnt = 5

# Disable query logging in production
log_queries = 0
log_disconnections = 0

# Reload configuration
sudo kill -HUP $(pgrep pgbouncer)

Monitor pool efficiency:

# Connect to PgBouncer admin
psql -h localhost -p 6432 -U postgres -d pgbouncer

-- Calculate pool utilization
SELECT 
  database,
  pool_mode,
  cur_connections,
  default_pool_size,
  ROUND(100.0 * cur_connections / default_pool_size, 2) as utilization_percent
FROM pgbouncer.pools;

-- Find waiting clients
SELECT client_addr, count(*) as waiting_connections 
FROM pgbouncer.clients 
WHERE state = 'wait' 
GROUP BY client_addr;

Troubleshooting

Handle common PgBouncer issues:

# Check if PgBouncer is running
systemctl status pgbouncer

# View recent logs
sudo tail -50 /var/log/pgbouncer/pgbouncer.log

# Test connection
psql -h localhost -p 6432 -U postgres -d myapp -c "SELECT 1"

# Check pool status
psql -h localhost -p 6432 -U postgres -d pgbouncer -c "SHOW pools"

# Reload configuration safely
sudo kill -HUP $(pgrep pgbouncer)

# Graceful shutdown
sudo kill -TERM $(pgrep pgbouncer)

# Hard restart
sudo systemctl restart pgbouncer

Debug connection issues:

# Enable debug logging temporarily
psql -h localhost -p 6432 -U postgres -d pgbouncer -c "SET log_min_messages = debug;"

# Check for authentication failures
grep "auth failed" /var/log/pgbouncer/pgbouncer.log

# Verify PostgreSQL can accept connections
psql -h 192.168.1.10 -U postgres -d postgres -c "SELECT version()"

# Test PgBouncer directly
psql -h localhost -p 6432 -U postgres -d postgres -c "SELECT version()"

Conclusion

PgBouncer provides an essential connection pooling layer for PostgreSQL deployments, reducing database resource consumption and improving application throughput. Proper configuration of pool modes, user authentication, and performance parameters ensures efficient connection management. By deploying PgBouncer between applications and PostgreSQL servers, you can support more concurrent application connections while reducing memory usage and improving overall system performance. Regular monitoring and tuning of pool sizes based on actual workload patterns keeps your database infrastructure operating optimally.