Optimize PostgreSQL connection pooling with PgBouncer for high-traffic applications

Update system packages

Start by updating your package manager to ensure you get the latest versions of all packages.

sudo apt update && sudo apt upgrade -y

sudo dnf update -y

Install PgBouncer

Install PgBouncer from the official repositories. This includes the main daemon and configuration tools.

sudo apt install -y pgbouncer postgresql-client

sudo dnf install -y pgbouncer postgresql

Create PgBouncer user and directories

Create a dedicated system user for PgBouncer and set up the required directory structure with proper permissions.

sudo useradd --system --home /var/lib/pgbouncer --shell /bin/bash pgbouncer
sudo mkdir -p /var/lib/pgbouncer /var/log/pgbouncer /etc/pgbouncer
sudo chown pgbouncer:pgbouncer /var/lib/pgbouncer /var/log/pgbouncer
sudo chmod 750 /var/lib/pgbouncer /var/log/pgbouncer

Configure PostgreSQL for connection pooling

Optimize PostgreSQL settings to work efficiently with PgBouncer. These settings ensure proper connection handling and performance.

# Connection settings for PgBouncer optimization
max_connections = 200
shared_buffers = 256MB
effective_cache_size = 1GB

# Reduce connection overhead
tcp_keepalives_idle = 600
tcp_keepalives_interval = 30
tcp_keepalives_count = 3

# Statement timeout to prevent long-running queries
statement_timeout = 300000
idle_in_transaction_session_timeout = 60000

Create authentication file

Set up the userlist.txt file containing database users and their password hashes. PgBouncer uses this for client authentication.

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

"myappuser" "md5d8578edf8458ce06fbc5bb76a58c5ca4"
"readonly_user" "md5a1b2c3d4e5f6789012345678901234ab"

Configure PgBouncer main settings

Create the primary PgBouncer configuration file with optimized settings for high-traffic applications.

[databases]
myapp_db = host=127.0.0.1 port=5432 dbname=myapp_production user=myappuser
readonly_db = host=127.0.0.1 port=5432 dbname=myapp_production user=readonly_user

[pgbouncer]
# Connection pooling settings
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 25
min_pool_size = 5
reserve_pool_size = 5
max_db_connections = 50

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

# Network settings
listen_addr = *
listen_port = 6432
unix_socket_dir = /var/run/postgresql

# Logging
log_connections = 1
log_disconnections = 1
log_pooler_errors = 1
logfile = /var/log/pgbouncer/pgbouncer.log
pidfile = /var/run/pgbouncer/pgbouncer.pid

# Performance tuning
server_lifetime = 3600
server_idle_timeout = 600
client_idle_timeout = 0
query_timeout = 300
query_wait_timeout = 120
client_login_timeout = 60

# Admin settings
admin_users = pgbouncer
stats_users = pgbouncer

Create systemd service file

Set up a systemd service to manage PgBouncer with proper security settings and automatic restart capability.

[Unit]
Description=PgBouncer connection pooler for PostgreSQL
Documentation=man:pgbouncer(1)
After=network.target postgresql.service
Requires=network.target

[Service]
Type=notify
User=pgbouncer
Group=pgbouncer
ExecStart=/usr/bin/pgbouncer /etc/pgbouncer/pgbouncer.ini
ExecReload=/bin/kill -HUP $MAINPID
KillSignal=SIGINT
TimeoutSec=300
Restart=always
RestartSec=5

# Security settings
NoNewPrivileges=true
PrivateTmp=true
ProtectSystem=strict
ProtectHome=true
ReadWritePaths=/var/log/pgbouncer /var/run/pgbouncer

# Process limits
LimitNOFILE=65536
LimitNPROC=32768

[Install]
WantedBy=multi-user.target

Create runtime directory

Set up the runtime directory for PgBouncer PID files with proper ownership and permissions.

sudo mkdir -p /var/run/pgbouncer
sudo chown pgbouncer:pgbouncer /var/run/pgbouncer
sudo chmod 755 /var/run/pgbouncer

Configure log rotation

Set up log rotation to prevent PgBouncer logs from consuming too much disk space.

/var/log/pgbouncer/*.log {
    daily
    rotate 7
    compress
    delaycompress
    missingok
    notifempty
    copytruncate
    su pgbouncer pgbouncer
}

Optimize system limits

Increase system limits to handle high connection counts. These settings are essential for high-traffic applications.

pgbouncer soft nofile 65536
pgbouncer hard nofile 65536
pgbouncer soft nproc 32768
pgbouncer hard nproc 32768

Configure firewall rules

Open the PgBouncer port (6432) in your firewall to allow client connections.

sudo ufw allow 6432/tcp comment 'PgBouncer'
sudo ufw status

sudo firewall-cmd --permanent --add-port=6432/tcp
sudo firewall-cmd --reload
sudo firewall-cmd --list-ports

Enable and start services

Start PostgreSQL and PgBouncer services, enabling them to start automatically on boot.

sudo systemctl restart postgresql
sudo systemctl enable pgbouncer
sudo systemctl start pgbouncer
sudo systemctl status pgbouncer

Transaction pooling for OLTP workloads

For web applications with short transactions, use transaction pooling mode for maximum efficiency.

# OLTP/Web application settings
pool_mode = transaction
default_pool_size = 25
min_pool_size = 5
reserve_pool_size = 5
max_db_connections = 50
server_idle_timeout = 300
query_timeout = 120

Session pooling for complex applications

For applications using prepared statements, temporary tables, or session-specific settings, use session pooling.

# Session-based application settings
pool_mode = session
default_pool_size = 50
min_pool_size = 10
reserve_pool_size = 10
max_db_connections = 100
server_lifetime = 1800
server_idle_timeout = 600

Statement pooling for simple queries

For read-heavy applications with simple SELECT queries, statement pooling provides the highest connection efficiency.

# Read-heavy workload settings
pool_mode = statement
default_pool_size = 10
min_pool_size = 2
reserve_pool_size = 3
max_db_connections = 20
server_idle_timeout = 600
query_timeout = 60

Access PgBouncer admin console

Connect to PgBouncer's built-in admin console to monitor pool statistics and performance metrics.

psql -h localhost -p 6432 -U pgbouncer pgbouncer
</code><h2><code>Password: same as configured in userlist.txt</code></h2></pre>
</div>

<div class="step">
### Monitor key metrics
<p>Use these admin commands to check pool health, connection usage, and identify bottlenecks.</p>
<pre class="terminal"><code>-- Show pool statistics
SHOW POOLS;

-- Show client connections
SHOW CLIENTS;

-- Show server connections
SHOW SERVERS;

-- Show configuration
SHOW CONFIG;

-- Show statistics
SHOW STATS;

Set up automated monitoring

Create a monitoring script to track PgBouncer metrics and send alerts when thresholds are exceeded.

#!/bin/bash

# PgBouncer monitoring script
HOST="localhost"
PORT="6432"
USER="pgbouncer"
DB="pgbouncer"
MAX_CLIENT_CONN=800
MAX_WAIT_TIME=30

# Check client connections
CLIENT_COUNT=$(psql -h $HOST -p $PORT -U $USER -d $DB -t -c "SELECT SUM(cl_active + cl_waiting) FROM pgbouncer.pools;" 2>/dev/null | tr -d ' ')

if [ "$CLIENT_COUNT" -gt $MAX_CLIENT_CONN ]; then
    echo "WARNING: High client connections: $CLIENT_COUNT" | logger -t pgbouncer-monitor
fi

# Check for waiting clients
WAIT_COUNT=$(psql -h $HOST -p $PORT -U $USER -d $DB -t -c "SELECT SUM(cl_waiting) FROM pgbouncer.pools;" 2>/dev/null | tr -d ' ')

if [ "$WAIT_COUNT" -gt 10 ]; then
    echo "CRITICAL: Clients waiting for connections: $WAIT_COUNT" | logger -t pgbouncer-monitor
fi

Configure performance alerts

Set up cron job to run monitoring checks every minute and log issues for further analysis.

sudo -u pgbouncer crontab -e

* * * * * /usr/local/bin/pgbouncer-monitor.sh