Connection Pooling with PgBouncer¶

PgBouncer provides connection pooling for PostgreSQL, reducing connection overhead and improving performance.

Architecture¶

graph LR
    subgraph "Application Layer"
        APP1[App Instance 1]
        APP2[App Instance 2]
        APP3[App Instance N]
    end

    subgraph "Pooling Layer"
        PGB[PgBouncer<br/>Transaction Pooling]
    end

    subgraph "Database Layer"
        PG[PostgreSQL<br/>Limited Connections]
    end

    APP1 --> PGB
    APP2 --> PGB
    APP3 --> PGB
    PGB --> PG

PgBouncer Modes¶

Transaction Pooling (Recommended)¶

• Connection Sharing: Connections shared across transactions
• Compatibility: Works with most applications
• Performance: Optimal for high-throughput workloads

Session Pooling¶

• Connection Per Session: One connection per client session
• Compatibility: Full PostgreSQL feature support
• Use Case: Applications requiring session state

Statement Pooling¶

• Connection Per Statement: New connection per SQL statement
• Compatibility: Limited - no prepared statements
• Use Case: Simple applications only

Configuration¶

pgbouncer.ini¶

[databases]
blacklake = host=haproxy port=5432 dbname=blacklake

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
auth_user = pgbouncer_auth
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 50
min_pool_size = 5
reserve_pool_size = 20
server_reset_query = DISCARD ALL
server_idle_timeout = 60
ignore_startup_parameters = extra_float_digits
admin_users = postgres, pgbouncer_admin
stats_users = pgbouncer_admin
auth_query = SELECT * FROM pgbouncer.lookup_user($1)

Key Settings¶

SQLAlchemy Integration¶

Connection String¶

# PgBouncer connection with transaction pooling
DATABASE_URL = "postgresql+psycopg://app_user:password@pgbouncer:6432/blacklake?prepare_threshold=0"

Engine Configuration¶

from sqlalchemy import create_engine
from sqlalchemy.pool import NullPool

engine = create_engine(
    DATABASE_URL,
    poolclass=NullPool,  # PgBouncer handles pooling
    connect_args={
        "options": "-c statement_timeout=30000 -c idle_in_transaction_session_timeout=30000"
    }
)

Prepared Statements¶

# Disable prepared statements for transaction pooling
url = "postgresql+psycopg://user:pass@pgbouncer:6432/db?prepare_threshold=0"

Authentication¶

PgBouncer Auth Function¶

CREATE SCHEMA IF NOT EXISTS pgbouncer;

CREATE OR REPLACE FUNCTION pgbouncer.lookup_user(_username text)
RETURNS TABLE(username text, password text)
LANGUAGE plpgsql SECURITY DEFINER AS $$
BEGIN
  RETURN QUERY
  SELECT u.rolname::text, u.rolpassword::text
  FROM pg_authid u
  WHERE u.rolname = _username;
END;
$$;

REVOKE ALL ON FUNCTION pgbouncer.lookup_user(text) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION pgbouncer.lookup_user(text) TO pgbouncer_auth;

User Management¶

-- Create PgBouncer users
CREATE ROLE pgbouncer_auth WITH LOGIN PASSWORD 'secure_password';
CREATE ROLE pgbouncer_admin WITH LOGIN PASSWORD 'admin_password';

-- Grant permissions
GRANT CONNECT ON DATABASE blacklake TO pgbouncer_auth;
GRANT CONNECT ON DATABASE blacklake TO pgbouncer_admin;

Monitoring¶

PgBouncer Admin Interface¶

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

# Show pools
SHOW POOLS;

# Show clients
SHOW CLIENTS;

# Show servers
SHOW SERVERS;

# Show stats
SHOW STATS;

Metrics Export¶

# PgBouncer metrics endpoint
curl http://pgbouncer:9127/metrics

Key Metrics¶

• Active Connections: Current active client connections
• Pool Size: Number of server connections in pool
• Query Rate: Queries per second
• Wait Time: Average wait time for connections

Performance Tuning¶

Connection Limits¶

# Adjust based on workload
max_client_conn = 1000        # Max client connections
default_pool_size = 50        # Pool size per database
min_pool_size = 5             # Minimum connections
reserve_pool_size = 20        # Reserve for admin

Timeout Settings¶

server_idle_timeout = 60      # Server connection idle timeout
server_connect_timeout = 15   # Connection timeout
query_timeout = 0            # Query timeout (0 = disabled)
client_idle_timeout = 0       # Client idle timeout

PostgreSQL Settings¶

-- Adjust PostgreSQL max_connections
ALTER SYSTEM SET max_connections = 200;

-- Enable connection logging
ALTER SYSTEM SET log_connections = on;
ALTER SYSTEM SET log_disconnections = on;

Troubleshooting¶

Common Issues¶

1. Connection Exhaustion¶

Symptoms: "too many connections" errors Resolution:

# Check connection usage
SHOW POOLS;

# Increase pool size
ALTER DATABASE blacklake SET pool_size = 100;

2. Prepared Statement Errors¶

Symptoms: "prepared statement does not exist" errors Resolution:

# Disable prepared statements
url = "postgresql+psycopg://user:pass@pgbouncer:6432/db?prepare_threshold=0"

3. Authentication Failures¶

Symptoms: "authentication failed" errors Resolution:

# Check auth function
SELECT * FROM pgbouncer.lookup_user('app_user');

# Verify user exists
SELECT rolname FROM pg_roles WHERE rolname = 'app_user';

Debugging¶

Enable Logging¶

log_connections = 1
log_disconnections = 1
log_pooler_errors = 1

Check PgBouncer Logs¶

docker logs blacklake-pgbouncer

Monitor Connections¶

# Real-time connection monitoring
watch -n 1 'psql -h pgbouncer -p 6432 -U pgbouncer_admin -d pgbouncer -c "SHOW POOLS;"'

Best Practices¶

Application Design¶

• Stateless Connections: Don't rely on connection state
• Connection Pooling: Let PgBouncer handle pooling
• Error Handling: Implement retry logic for connection failures
• Monitoring: Track connection metrics and errors

Security¶

• TLS Encryption: Use SSL for all connections
• Authentication: Use strong passwords and SCRAM-SHA-256
• Network Isolation: Separate PgBouncer network
• Access Control: Limit admin access

Performance¶

• Pool Sizing: Size pools based on workload
• Connection Limits: Set appropriate limits
• Monitoring: Track performance metrics
• Testing: Load test with realistic workloads

Next Steps¶

• PostgreSQL HA - Patroni cluster configuration
• Backups - pgBackRest backup strategy
• Observability - Monitoring and alerting