Connection pooling

A connection pool keeps a fixed set of TCP+auth sessions open and hands them out to application requests. Without it, every request pays a full TCP handshake + TLS + auth roundtrip — typically 5–50ms before the first query.

Why pool

• Latency: connection setup is expensive. Reusing a warm connection cuts per-request overhead to ~0.
• Backpressure: a pool with max_size = N caps concurrent DB load. Without one, a request spike opens hundreds of sockets and the DB falls over.
• Resource limits: Postgres default max_connections = 100. Each connection costs ~10MB RAM. Without a pool, 1000 concurrent requests = OOM.

Sizing the pool

The naive rule: cores × 2 + spindles (HikariCP heuristic). On modern SSD-backed cloud DBs, cores × 2 to 4 is typical.

Why so low? Postgres processes are heavyweight; adding more than ~2× cores worth of active connections hurts throughput due to context switching and lock contention. PgBouncer benchmarks consistently show throughput peaking at ~2–3× cores.

For a 4-core DB:

• pool_size = 10 (steady-state)
• max_overflow = 10 (burst capacity)
• Total cap: 20

Across N app instances, total connections = N × pool size. Always check N × pool_size < max_connections.

SQLAlchemy parameters

from sqlalchemy import create_engine

engine = create_engine(
    url,
    pool_size=10,           # steady-state connections kept open
    max_overflow=10,        # extra connections allowed under burst (closed when idle)
    pool_recycle=1800,      # recycle connections older than 30min (avoids stale)
    pool_pre_ping=True,     # SELECT 1 before checkout — detects dead connections
    pool_timeout=30,        # how long to wait for a free connection before raising
)

• pool_recycle: critical when DB or load balancer drops idle connections (AWS RDS proxy, GCP Cloud SQL — typically 5–10 min idle timeout). Set this below the server-side timeout.
• pool_pre_ping: small overhead per checkout, but eliminates “MySQL server has gone away” / “SSL connection has been closed unexpectedly” surprises.
• pool_timeout: when this fires, you either need a bigger pool or you have leaked connections somewhere.

PgBouncer modes

PgBouncer is a separate process that pools at the network level — useful when you have many app instances and want one shared pool.

Transaction mode is the sweet spot — high concurrency, manageable footprint. The trade-off: features that span statements (server-side cursors, LISTEN/NOTIFY, prepared statements pre-PG14) break.

Diagnosing pool exhaustion

Symptoms: requests hang, then fail with QueuePool limit of size X overflow Y reached, connection timed out.

Causes (in order of frequency):

1. Leaked connections — code path that doesn’t release: missing with block, exception path that skips cleanup, long-running task holding the session.
2. Slow queries — a query taking 5s holds a connection 5s. Check pg_stat_activity for long-running queries.
3. Pool too small for load — genuine sizing issue. Bump after eliminating 1 and 2.

-- See what's currently open
SELECT pid, state, query_start, query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY query_start;

Statement timeout

Defense-in-depth: cap how long any single statement can run.

-- Server-side, per session
SET statement_timeout = '30s';

-- Postgres default: 0 (unlimited).  Always set it.

In SQLAlchemy, set per-engine via connect_args:

engine = create_engine(url, connect_args={"options": "-c statement_timeout=30000"})

Without this, one runaway query can block migrations, vacuum, and exhaust the pool.

Interview angle

• Q: “Why use a connection pool?” — latency, backpressure, resource limits.
• Q: “How would you size one?” — cores × 2–4; check N × pool < max_connections.
• Follow-up: “What’s PgBouncer transaction mode and when can’t you use it?” — releases per transaction; breaks prepared statements (pre-PG14), session-scoped state.
• Follow-up: “Pool exhausted in production — how do you debug?” — check pg_stat_activity for stuck queries first, then look for leaks (always with-block sessions), only then bump pool size.

See 07_n_plus_1.md — N+1 queries are a common cause of pool exhaustion.