Connection Pooling Setup

Pooling Modes Configuration

pgBalancer supports three pooling modes with different connection behaviors and tuning knobs. Use the configuration snippet below as a starting point, then adjust limits based on concurrency and SLA targets.

# Connection Pooling Configuration

# Pool mode: session, transaction, or statement
# - session: Connection held for entire client session (default)
# - transaction: Connection returned after each transaction
# - statement: Connection returned after each statement
pool_mode = transaction

# Maximum client connections (per process)
num_init_children = 32
max_pool = 4

# Connection limits
max_connections = 100
reserved_connections = 1

# Connection lifecycle
connection_life_time = 600      # Disconnect pooled connections after 10 minutes
client_idle_limit = 0           # Disconnect idle clients (0 = disabled)

# Authentication timeout
authentication_timeout = 60

# Child process management
child_life_time = 300           # Child process lifetime (seconds)
child_max_connections = 0       # Max connections per child (0 = unlimited)

# Connection cache
connection_cache = on           # Enable connection caching

Monitor Pool Utilization

Query pgBalancer to monitor connection pool usage and identify bottlenecks.

-- Connect to pgBalancer
psql -h localhost -p 9999 -U postgres

-- View all pool processes and their connections
SHOW POOL_PROCESSES;

-- Output shows:
-- pool_pid | start_time | database | username | create_time | pool_counter

-- Get pool utilization metrics
SELECT 
    database,
    username,
    COUNT(*) as active_connections,
    MAX(pool_counter) as max_reuse
FROM pool_processes
GROUP BY database, username;

-- Check for pool exhaustion
SELECT 
    CASE 
        WHEN COUNT(*) >= 32 THEN 'WARNING: Pool exhausted'
        ELSE 'OK'
    END as pool_status,
    database
FROM pool_processes
GROUP BY database;

Tune Pool Limits

Adjust max connections and pool sizes based on observed workload patterns. Use structured experiments to determine the right settings.

# Increase simultaneous clients
num_init_children = 48
max_pool = 6

# Add reserve for admin connections
reserved_connections = 3

connection_life_time = 300
client_idle_limit = 120
child_life_time = 180

Authentication, Limits & Health Checks

# Authentication options
# auth_type = md5 | pam | cert
# auth_file = '/etc/pgbalancer/userlist.txt'
# authentication_query = 'SELECT usename, passwd FROM pgbalancer_users'

# Connection limits
max_connections = 100
reserved_connections = 5
max_pool = 4

# Health checks
health_check_period = 15               # seconds
health_check_timeout = 10
health_check_user = 'pgbalancer_health'
health_check_password = 'SecureHealthPass!'
health_check_database = 'postgres'

psql -d postgres -c "CREATE ROLE pgbalancer_health LOGIN PASSWORD 'SecureHealthPass!'"

Multi-tenant Pools

Allocate pools per tenant or service by defining user/database pairs. This avoids noisy neighbors and allows granular tuning.

# Tenant A pool
user1_database = 'tenant_a'
user1_pool_mode = statement
user1_max_pool = 8

# Tenant B pool
user2_database = 'tenant_b'
user2_pool_mode = transaction
user2_max_pool = 6

Metrics & Dashboards

Integrate with Prometheus and Grafana to visualize pool usage, wait times, and failover events. The REST API exposes counters for automation and alerting.

SELECT histogram(bucket_ms, wait_event) AS histogram
FROM pgbalancer_wait_times
WHERE bucket_ms <= 500;

curl -s http://localhost:8080/api/v1/pool/summary | jq