Fix PgBouncer Connection Pool Exhausted

What's Actually Happening

PgBouncer runs out of available connections in the pool. New client connections are rejected or timeout waiting for an available server connection.

The Error You'll See

Pool exhaustion:

```bash $ psql -h pgbouncer -p 6432 -U user -d mydb

ERROR: no more connections allowed (max_client_conn) ```

Wait timeout:

ERROR: timeout waiting for connection from pool

PgBouncer logs:

```bash $ journalctl -u pgbouncer | grep -i pool

WARNING: client reached max_client_conn: 100 WARNING: pool 'mydb' reached max pool size: 20 ```

Why This Happens

1. 1.max_client_conn too low - Not enough client slots
2. 2.Pool size too small - Fewer server connections than needed
3. 3.Long-running queries - Connections held too long
4. 4.Connection leaks - Applications not releasing connections
5. 5.Timeout too short - Clients timeout before getting connection
6. 6.High traffic spike - Sudden connection increase

Step 1: Check Pool Status

```bash # Connect to PgBouncer admin database: psql -h pgbouncer -p 6432 -U pgbouncer -d pgbouncer

# Show pools: pgbouncer=# SHOW POOLS;

database | pool_size | min_pool_size | max_pool_size | res_pool_size | res_pool_max | clients | servers | clients_waiting mydb | 20 | 5 | 20 | 0 | 0 | 100 | 20 | 50

# pool_size: current server connections # clients: current client connections # clients_waiting: clients waiting for server connection

# Show clients: pgbouncer=# SHOW CLIENTS;

# Show servers (PostgreSQL connections): pgbouncer=# SHOW SERVERS;

# Show database configuration: pgbouncer=# SHOW DATABASES; ```

Step 2: Check Current Configuration

```bash # Check PgBouncer config: cat /etc/pgbouncer/pgbouncer.ini

# Key settings: [databases] mydb = host=postgres port=5432 dbname=mydb pool_size=20

[pgbouncer] max_client_conn = 100 default_pool_size = 20 min_pool_size = 5 max_pool_size = 20 reserve_pool_size = 5 reserve_pool_timeout = 3 listen_addr = 0.0.0.0 listen_port = 6432

# Show current config via admin: psql -h pgbouncer -p 6432 -d pgbouncer -c "SHOW CONFIG;" ```

Step 3: Increase Pool Limits

```bash # Edit PgBouncer config: vi /etc/pgbouncer/pgbouncer.ini

# Increase limits: [pgbouncer] max_client_conn = 500 # Was 100 default_pool_size = 50 # Was 20 max_pool_size = 100 # Was 20 min_pool_size = 10 # Was 5 reserve_pool_size = 10 # Was 5

# Per database override: [databases] mydb = host=postgres port=5432 dbname=mydb pool_size=100 max_db_connections=200

# Restart PgBouncer: systemctl restart pgbouncer

# Verify changes: psql -h pgbouncer -p 6432 -d pgbouncer -c "SHOW CONFIG;" ```

Step 4: Adjust Timeout Settings

```bash # In pgbouncer.ini: [pgbouncer] # How long to wait for server connection server_connect_timeout = 15 # Default 15 seconds

# How long to wait for server login server_login_retry = 3 # Default 3 seconds

# How long client waits for connection query_timeout = 30 # Default 0 (no limit)

# Client connection timeout client_connect_timeout = 5 # Default 5 seconds

# Idle timeout for server connections server_idle_timeout = 600 # Default 600 seconds

# Client idle timeout client_idle_timeout = 0 # Default 0 (no limit)

# Increase wait timeout: server_connect_timeout = 30 query_timeout = 60

# Restart PgBouncer: systemctl restart pgbouncer ```

Step 5: Check PostgreSQL Connections

```bash # Check PostgreSQL max connections: psql -h postgres -U postgres -c "SHOW max_connections;"

# Output: 100

# If PostgreSQL limit too low: psql -h postgres -U postgres -c "ALTER SYSTEM SET max_connections = 300;" psql -h postgres -U postgres -c "SELECT pg_reload_conf();"

# Or in postgresql.conf: max_connections = 300

# Restart PostgreSQL for full change: systemctl restart postgresql

# Check current connections: psql -h postgres -U postgres -c "SELECT count(*) FROM pg_stat_activity;"

# PgBouncer pool_size * number of databases should be < PostgreSQL max_connections # Example: 3 databases with pool_size=100 = 300 connections needed ```

Step 6: Check Connection Leaks

```bash # In PgBouncer admin: psql -h pgbouncer -p 6432 -d pgbouncer -c "SHOW CLIENTS;"

# Look for: # - Long connect_time (connections held for hours) # - Same state for extended periods

# Check application connection handling: # Applications should: # - Close connections after use # - Use connection pooling in app # - Not hold connections idle

# Force disconnect idle clients: # In pgbouncer.ini: client_idle_timeout = 300 # Disconnect after 5 minutes idle

# Kill stuck connections: psql -h pgbouncer -p 6432 -d pgbouncer -c "KILL <client_id>;"

# Check for prepared statements leak: # Prepared statements can prevent connection reuse # In pgbouncer.ini: server_reset_query = DISCARD ALL # This cleans up prepared statements on connection release ```

Step 7: Optimize Pool Mode

```bash # PgBouncer has three pool modes:

# Session pooling (most compatible): [databases] mydb = host=postgres port=5432 pool_mode=session # Connection held until client disconnects # Good for prepared statements, SET commands # Less efficient pool usage

# Transaction pooling (most efficient): [databases] mydb = host=postgres port=5432 pool_mode=transaction # Connection returned after transaction ends # Maximum efficiency # Cannot use prepared statements, SET commands

# Statement pooling (not recommended): [databases] mydb = host=postgres port=5432 pool_mode=statement # Connection returned after each statement # Maximum efficiency but most limitations

# Recommended: transaction pooling with prepared statement workaround: [databases] mydb = host=postgres port=5432 pool_mode=transaction server_reset_query=DISCARD ALL

# Restart PgBouncer: systemctl restart pgbouncer ```

Step 8: Add Reserve Pool

```bash # Reserve pool for spikes: # In pgbouncer.ini: [pgbouncer] reserve_pool_size = 10 # Extra connections for spikes reserve_pool_timeout = 5 # Wait 5 seconds before using reserve

# Per database: [databases] mydb = host=postgres port=5432 reserve_pool=10

# Reserve pool is used when: # - Regular pool exhausted # - Client waits > reserve_pool_timeout seconds

# Check reserve pool usage: psql -h pgbouncer -p 6432 -d pgbouncer -c "SHOW POOLS;" | grep res_pool

# If reserve_pool_size > 0 regularly, increase default pool ```

Step 9: Monitor PgBouncer Metrics

```bash # Create monitoring script: cat << 'EOF' > /usr/local/bin/monitor-pgbouncer.sh #!/bin/bash

echo "=== PgBouncer Pool Status ===" psql -h localhost -p 6432 -d pgbouncer -c "SHOW POOLS;" -t

echo "" echo "=== Client Count ===" psql -h localhost -p 6432 -d pgbouncer -c "SELECT count(*) FROM SHOW CLIENTS;" -t

echo "" echo "=== Waiting Clients ===" psql -h localhost -p 6432 -d pgbouncer -c "SELECT sum(clients_waiting) FROM SHOW POOLS;" -t

echo "" echo "=== Server Connections ===" psql -h localhost -p 6432 -d pgbouncer -c "SELECT count(*) FROM SHOW SERVERS;" -t EOF

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

# PgBouncer exposes Prometheus metrics (1.12+): # Enable in config: [pgbouncer] prometheus_port = 9127

# Key metrics: # pgbouncer_pools_clients - clients per pool # pgbouncer_pools_servers - servers per pool # pgbouncer_pools_clients_waiting - waiting clients # pgbouncer_clients_active - active clients

# Alert for pool exhaustion: - alert: PgBouncerPoolExhausted expr: pgbouncer_pools_clients_waiting > 0 for: 2m labels: severity: warning annotations: summary: "PgBouncer clients waiting for connections" ```

Step 10: Handle Traffic Spikes

```bash # For sudden traffic spikes:

# Quick fix - increase limits temporarily: psql -h pgbouncer -p 6432 -d pgbouncer -c "SET max_client_conn = 500;" # Note: Requires admin console enabled

# Or edit config and restart: vi /etc/pgbouncer/pgbouncer.ini systemctl restart pgbouncer

# Long-term solutions: # 1. Use multiple PgBouncer instances # 2. Add application-level connection pooling # 3. Queue requests during spikes

# Multi-instance setup: # HAProxy -> [pgbouncer-1, pgbouncer-2, pgbouncer-3] -> PostgreSQL

# Application pooling: # Use connection pooler in application (HikariCP, etc.) # App pool (10) -> PgBouncer pool (100) -> PostgreSQL

# Disconnect all clients for emergency: psql -h pgbouncer -p 6432 -d pgbouncer -c "SHUTDOWN;" systemctl restart pgbouncer ```

PgBouncer Pool Exhaustion Checklist

Verify the Fix

```bash # After increasing pool limits

# 1. Check pool status psql -h pgbouncer -p 6432 -d pgbouncer -c "SHOW POOLS;" // clients < pool_size, no waiting

# 2. Test new connections psql -h pgbouncer -p 6432 -U user -d mydb // Connected successfully

# 3. Monitor waiting clients psql -h pgbouncer -p 6432 -d pgbouncer -c "SELECT sum(clients_waiting) FROM SHOW POOLS;" // 0

# 4. Check PostgreSQL connections psql -h postgres -U postgres -c "SELECT count(*) FROM pg_stat_activity;" // Within max_connections

# 5. Test under load # Run many concurrent queries psql -h pgbouncer -p 6432 -U user -d mydb -c "SELECT * FROM large_table;" // No timeout errors

# 6. Verify configuration psql -h pgbouncer -p 6432 -d pgbouncer -c "SHOW CONFIG;" // Limits increased ```

Related Issues

• [Fix PostgreSQL Connection Limit Exceeded](/articles/fix-postgresql-connection-limit-exceeded)
• [Fix Redis Connection Pool Exhausted](/articles/fix-redis-connection-pool-exhausted)
• [Fix MongoDB Connection Pool Exhausted](/articles/fix-mongodb-connection-pool-exhausted)