Database Connection Management

Database connections are expensive resources. Each PostgreSQL connection consumes ~10MB of memory. Each MySQL connection consumes ~1-3MB. Opening a connection takes 20-100ms. Connection management determines whether your application scales smoothly or hits a wall of “too many connections” errors under load.

---

Connection Pooling

Application-Level Pooling

# SQLAlchemy connection pool
from sqlalchemy import create_engine

engine = create_engine(
    "postgresql://user:pass@localhost/db",
    pool_size=20,          # Steady-state connections
    max_overflow=10,       # Burst connections above pool_size
    pool_timeout=30,       # Wait time for available connection
    pool_recycle=1800,     # Recycle connections every 30 min
    pool_pre_ping=True,    # Verify connection before use
)

External Pooling (PgBouncer)

# pgbouncer.ini
[databases]
mydb = host=postgres port=5432 dbname=mydb

[pgbouncer]
pool_mode = transaction      # Release connection after each transaction
max_client_conn = 1000       # Accept up to 1000 client connections
default_pool_size = 50       # Only 50 actual database connections
min_pool_size = 10           # Keep minimum 10 connections warm
reserve_pool_size = 5        # Emergency reserve connections
reserve_pool_timeout = 3     # Wait 3s before using reserve pool
server_idle_timeout = 300    # Close idle server connections after 5 min

Pool modes:
  session:     Connection held for entire client session (safest, least efficient)
  transaction: Connection returned after each transaction (best for web apps)
  statement:   Connection returned after each statement (most efficient, limited use)

---

Pool Sizing

Formula

Optimal pool size = (core_count * 2) + effective_spindle_count

For SSD (spindle_count ≈ 1):
  8-core server → (8 * 2) + 1 = 17 connections

For cloud databases:
  AWS RDS db.r6g.xlarge (4 vCPU) → max_connections = 1365
  Recommended pool: 20-50 per application instance
  Total across all instances should not exceed max_connections

Multi-Instance Calculation

Database max_connections: 200
Application instances: 10
PgBouncer servers: 2

Effective pool per app instance: 200 / 10 = 20 connections
PgBouncer max per server: 200 / 2 = 100
Client connections per PgBouncer: 500 → multiplexed to 100 backend

---

Connection Lifecycle

Acquire → Validate → Use → Return → Recycle/Evict

Acquire:
  Check out from pool (or create if pool not full)
  Wait if pool exhausted (up to pool_timeout)

Validate:
  pool_pre_ping = True → Send "SELECT 1" before use
  Catches stale connections (firewall timeout, server restart)

Use:
  Execute queries within transaction
  Keep duration short (< 1 second for web requests)

Return:
  Return to pool after transaction completes
  Connection reset (DISCARD ALL in Postgres)

Recycle:
  pool_recycle = 1800 → Replace connection every 30 min
  Prevents issues from server-side timeouts
  
Evict:
  Remove connections exceeding max_overflow after idle_timeout

---

Common Problems

Problem	Symptom	Fix
Connection exhaustion	”too many connections” error	Pool + PgBouncer + right-size pool
Connection leak	Pool slowly exhausted over time	Connection timeout + leak detection
Stale connections	Random query failures	pool_pre_ping + pool_recycle
High latency on first request	Cold connection setup	min_pool_size > 0 (warm pool)
Thundering herd on restart	All connections opened simultaneously	Gradual pool warm-up, connection backoff

Leak Detection

# SQLAlchemy leak detection
engine = create_engine(
    "postgresql://user:pass@localhost/db",
    pool_size=20,
    pool_timeout=10,           # Fail fast if pool exhausted
    echo_pool="debug",         # Log pool events
)

# Log when connections are checked out too long
@event.listens_for(engine, "checkout")
def checkout_listener(dbapi_conn, connection_record, connection_proxy):
    connection_record.info['checkout_time'] = time.time()

@event.listens_for(engine, "checkin")
def checkin_listener(dbapi_conn, connection_record):
    checkout_time = connection_record.info.get('checkout_time')
    if checkout_time and (time.time() - checkout_time) > 30:
        logger.warning(f"Connection held for {time.time() - checkout_time:.1f}s")

---

Anti-Patterns

Anti-Pattern	Consequence	Fix
No connection pooling	New connection per request (20-100ms overhead)	Application or external connection pool
Pool too large	Exhausts database memory	Pool size based on formula, not guessing
No connection validation	Stale connections cause errors	pool_pre_ping + pool_recycle
Long-held connections	Pool exhaustion during slow queries	Short transactions + async for long operations
No monitoring	Connection issues invisible	Monitor pool utilization + wait time

Connection management is invisible when done right and catastrophic when done wrong. A well-configured pool prevents the most common database-related outages.