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. Esta guía cubre installation, configuration, connection pool modes, authentication setup, monitoring, and tuning for production deployments.

Tabla de Contenidos

• Descripción General and Benefits
• Instalación
• Configuración File
• Pool Modes
• User Authentication
• Client Connection Gestión
• Monitoreo and Statistics
• Performance Tuning
• Solución de Problemas
• Conclusión

Descripción General 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.

Instalación

Instala PgBouncer on Ubuntu/Debian systems:

# Instala 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

# Verifica installation
pgbouncer --version
which pgbouncer

On CentOS/RHEL:

# Instala from EPEL repositorio
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

Crea dedicated user and directories:

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

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

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

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

Configuración File

Crea 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 puerto=5432 dbname=myapp
analytics = host=192.168.1.11 puerto=5432 dbname=analytics
default = host=192.168.1.10 puerto=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

# Gestión
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

Crea a systemd servicio file:

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

Add:

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

[Servicio]
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
Reinicia=always
RestartSec=10

StandardOutput=journal
StandardError=journal

[Instala]
WantedBy=multi-user.target

Habilita and start the servicio:

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

# Monitorea logs
sudo journalctl -u pgbouncer -f

Pool Modes

Configura 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

Comprendiendo 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

Configura authentication between PgBouncer and PostgreSQL:

# Crea 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:

# Conecta 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

Configura PostgreSQL to accept PgBouncer connections:

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

Add:

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

# Permite pgbouncer user for connection management
local   all             pgbouncer                               trust

Configura PgBouncer database in PostgreSQL:

# Conecta as postgres
psql -U postgres

-- Crea pgbouncer database
CREATE DATABASE pgbouncer;

-- Crea 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 Gestión

Configura 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

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

Monitoreo and Statistics

Monitorea PgBouncer performance:

# Conecta 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

Crea 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

Ejecuta 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

Optimiza 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 red parameters
tcp_user_timeout = 60000
tcp_keepidles = 30
tcp_keepintvl = 10
tcp_keepcnt = 5

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

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

Monitorea pool efficiency:

# Conecta 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;

Solución de Problemas

Handle common PgBouncer issues:

# Check if PgBouncer is running
systemctl status pgbouncer

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

# Prueba 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

Depura connection issues:

# Habilita 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

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

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

Conclusión

PgBouncer proporciona an essential connection pooling capa 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.