Connection Pooling

The engine holds a pool of open DB connections, reusing them across requests instead of opening fresh ones. Pool tuning is one of the few SQLAlchemy knobs that matters for production performance.

For pool sizing principles and pgbouncer interaction, see ../09_connection_pooling.md. This file is the SQLAlchemy-specific config.

Why pool

Opening a Postgres connection costs ~10–50ms (TCP + TLS + auth + server-side fork). Doing this for every HTTP request:

• 50ms per request floor.
• Postgres’s max_connections (often 100–500) exhausted quickly.
• TIME_WAIT sockets pile up.

Pool: keep N connections open, hand them out, return them when done.

engine = create_engine(
    "postgresql+psycopg://...",
    pool_size=10,
    max_overflow=20,
    pool_timeout=30,
    pool_recycle=3600,
    pool_pre_ping=True,
)

Pool parameters

The total available connections = pool_size + max_overflow. Beyond that, requests wait up to pool_timeout seconds.

Sizing the pool

total_connections ≤ db_max_connections / app_replica_count

Postgres’s default max_connections=100. With 5 app replicas:

per_replica = 100 / 5 = 20 connections
pool_size = 10, max_overflow = 10

Leave room for admin/migrations/monitoring. Don’t size the pool larger than the DB can handle — connections in queue waste latency without speeding up the DB.

For high-concurrency apps with many short queries, pgbouncer in front of the DB lets you keep many app-side connections without blowing up the DB. See ../09_connection_pooling.md.

Pool types

from sqlalchemy.pool import NullPool, StaticPool

# Disable pooling entirely
engine = create_engine("...", poolclass=NullPool)

# For SQLite :memory: with multi-threading
engine = create_engine(
    "sqlite:///:memory:",
    poolclass=StaticPool,
    connect_args={"check_same_thread": False},
)

When to use NullPool:

• Behind pgbouncer in transaction pooling mode (pgbouncer handles pooling; SQLAlchemy pool is redundant and can cause prepared-statement issues).
• AWS Lambda / serverless (process is short-lived; pool is wasted).
• One-off scripts that exit quickly.

pool_pre_ping=True — the safety net

engine = create_engine("...", pool_pre_ping=True)

Before handing out a pooled connection, run SELECT 1 (or DB-specific equivalent) to verify it’s still alive. If dead, throw it away and try the next one.

Why: pooled connections go stale when:

• The DB restarts.
• The DB’s idle_in_transaction_session_timeout kills them.
• A firewall / load balancer times out idle TCP.
• Network blips.

Cost: ~0.5ms extra per checkout. Almost always worth it.

Alternative: pool_recycle=3600 recycles connections older than 1 hour — covers the MySQL wait_timeout=28800 default, prevents most stale-connection errors. Combine with pool_pre_ping for belt-and-suspenders.

fork() and the connection pool — the silent killer

If you fork your process after creating the engine (common with gunicorn --preload, uWSGI, celery prefork), the child processes inherit the parent’s open TCP connections to the DB. Two children writing to the same socket = corrupted state.

The fix: dispose the pool in each child after fork.

import os
from sqlalchemy import event

@event.listens_for(engine, "connect")
def receive_connect(dbapi_conn, connection_record):
    connection_record.info["pid"] = os.getpid()

@event.listens_for(engine, "checkout")
def receive_checkout(dbapi_conn, connection_record, connection_proxy):
    pid = os.getpid()
    if connection_record.info.get("pid") != pid:
        connection_record.dbapi_connection = None
        raise DisconnectionError(
            "Connection record belongs to pid %s, attempting to check out in pid %s"
            % (connection_record.info["pid"], pid)
        )

Or simpler: call engine.dispose() in each worker’s startup hook. Gunicorn:

# gunicorn config
def post_fork(server, worker):
    from myapp.db import engine
    engine.dispose()

Celery:

@worker_process_init.connect
def init_worker(**kwargs):
    engine.dispose()

This is one of the most common production bugs in fork-based servers.

Asyncio and pooling

from sqlalchemy.ext.asyncio import create_async_engine

engine = create_async_engine(
    "postgresql+asyncpg://...",
    pool_size=10,
    max_overflow=20,
)

Async engines use AsyncAdaptedQueuePool by default. Sizing is the same as sync. See 11_async_sqlalchemy.md.

Inspecting the pool

print(engine.pool.status())
# QueuePool: size: 10  connections in pool: 8  current overflow: 0  current checked out: 2

Or via events:

@event.listens_for(engine, "checkout")
def on_checkout(dbapi_conn, conn_record, conn_proxy):
    print(f"checkout, in_use: {engine.pool.checkedout()}")

For production monitoring, instrument engine.pool.checkedout() and engine.pool.size() to Prometheus / Datadog.

Pool exhaustion

TimeoutError: QueuePool limit of size 10 overflow 20 reached, connection timed out, timeout 30

Means: 30 requests waiting, no free connections, all 30 timed out. Causes:

1. Connections leaked — code paths that don’t close sessions.
2. Long-running queries holding connections.
3. Pool too small for the request rate.
4. Deadlocks — connections stuck waiting on each other.

Diagnose with engine.pool.status() in metrics, slow query logs, and pg_stat_activity on the DB:

SELECT pid, state, query_start, query FROM pg_stat_activity WHERE state != 'idle';

Fix: close sessions properly, optimize slow queries, add connection limits per query (statement timeout), or just raise pool size if the DB can handle it.

pgbouncer — the layer below SQLAlchemy’s pool

Pgbouncer is a Postgres connection pooler that sits between your app and the DB. Three modes:

With transaction pooling (the common production setup), you can have 1000 app connections to pgbouncer sharing ~50 DB connections. Critical caveats:

• No prepared statements with the default driver (each transaction may get a different DB connection).
• No session-level features (SET, LISTEN/NOTIFY, advisory locks).
• Per-transaction state is fine.

Configure SQLAlchemy for transaction-mode pgbouncer:

engine = create_engine(
    "postgresql+psycopg://app:pw@pgbouncer:6432/mydb",
    poolclass=NullPool,                                # SQLAlchemy doesn't pool — pgbouncer does
    # or small pool: pool_size=5, max_overflow=0
    connect_args={"prepare_threshold": None},          # disable psycopg auto-preparing
)

For asyncpg:

engine = create_async_engine(
    "postgresql+asyncpg://...",
    connect_args={"statement_cache_size": 0},          # disable prepared statement cache
)

Common pitfalls

• No pool_pre_ping + DB restart → first requests after restart fail with connection invalidated errors. Always enable.
• fork() after engine creation without dispose() in each child → corrupted shared connections.
• Long-running transactions holding connections → other requests block on pool exhaustion.
• Sizing pool > DB max_connections across all app replicas → DB rejects connections, app fails.
• NullPool in synchronous app without pgbouncer → every request opens a fresh connection (slow).

Common interview confusions

• “pool_size=10 means max 10 simultaneous connections.” — no, it’s the persistent count. Max simultaneous = pool_size + max_overflow.
• “pgbouncer replaces SQLAlchemy’s pool.” — yes operationally, but you still want a small SQLAlchemy pool to avoid the latency of reconnecting to pgbouncer. With transaction pooling, set poolclass=NullPool or small pool_size.
• “pool_pre_ping is always on.” — default is False. Easy fix that turns “DB restart breaks app for a minute” into “DB restart is invisible.”

Interview angle

• “What’s connection pooling and why do you need it?” — opening a DB connection costs ~10–50ms (TCP+TLS+auth). Pooling reuses open connections across requests, avoiding the handshake cost and respecting DB’s max_connections.
• “How do you size SQLAlchemy’s pool?” — pool_size + max_overflow per replica × number of replicas ≤ DB max_connections minus headroom. Typical pool_size=10, max_overflow=20 per replica.
• “What’s pool_pre_ping and why use it?” — runs SELECT 1 before handing out a pooled connection; throws away dead ones. Handles DB restarts and stale connections transparently. ~0.5ms overhead per checkout; almost always worth it.
• “What goes wrong when you fork() after creating an engine?” — child processes inherit open TCP connections; two processes sharing one socket corrupts the protocol. Fix by calling engine.dispose() in each worker’s post-fork hook.
• “How does SQLAlchemy interact with pgbouncer in transaction pooling mode?” — pgbouncer assigns a different DB connection per transaction, so prepared statements break. Use NullPool or a small pool, disable prepared statement caching (statement_cache_size=0 for asyncpg, prepare_threshold=None for psycopg).
• “What’s the difference between pool_size and max_overflow?” — pool_size is the persistent count (kept open between uses); max_overflow is extra connections opened on demand (closed when returned). Total cap = pool_size + max_overflow.