Database Pool Sizing — Deep Dive

Pool architecture in Python

Python’s database ecosystem offers several pool implementations, each with distinct sizing behaviors. Understanding their internals is essential for correct tuning.

SQLAlchemy’s QueuePool

SQLAlchemy’s default pool uses a thread-safe queue internally:

from sqlalchemy import create_engine

engine = create_engine(
    "postgresql+psycopg2://user:pass@localhost/mydb",
    pool_size=5,          # persistent connections
    max_overflow=10,       # temporary connections under load
    pool_timeout=30,       # seconds to wait before giving up
    pool_recycle=1800,     # recycle connections after 30 minutes
    pool_pre_ping=True,    # verify connection is alive before use
)

The pool starts empty and creates connections on demand up to pool_size. Under burst traffic, it creates up to max_overflow additional connections that are discarded (not kept) when returned. Total maximum is always pool_size + max_overflow — in this case, 15.

A critical detail: pool_timeout controls how long a thread waits for an available connection. If all 15 are checked out and a 16th request arrives, it blocks for up to 30 seconds, then raises TimeoutError. In production, this timeout should be shorter (3–5 seconds) to fail fast rather than pile up waiting threads.

asyncpg for async applications

For async Python apps (FastAPI, Starlette), asyncpg provides a native async pool:

import asyncpg

pool = await asyncpg.create_pool(
    dsn="postgresql://user:pass@localhost/mydb",
    min_size=5,       # always keep 5 connections
    max_size=20,      # allow up to 20 under load
    max_inactive_connection_lifetime=300.0,  # close idle conns after 5 min
)

async with pool.acquire() as conn:
    rows = await conn.fetch("SELECT * FROM orders WHERE status = $1", "pending")

Unlike SQLAlchemy’s overflow model, asyncpg keeps connections between min_size and max_size alive, gradually closing idle ones that exceed min_size.

Django’s persistent connections

Django doesn’t use a traditional pool — it opens one connection per thread and optionally keeps it alive:

DATABASES = {
    "default": {
        "ENGINE": "django.db.backends.postgresql",
        "CONN_MAX_AGE": 600,  # keep connections for 10 minutes
        "CONN_HEALTH_CHECKS": True,  # verify before use (Django 4.1+)
    }
}

With CONN_MAX_AGE=600 and Gunicorn running 4 workers × 2 threads, Django opens up to 8 persistent connections. For real pooling, Django projects typically add django-db-connection-pool or front the database with PgBouncer.

Sizing methodology

Step 1: Establish the database-side ceiling

Check your PostgreSQL max_connections (default is usually 100):

SHOW max_connections;  -- typically 100
SELECT count(*) FROM pg_stat_activity;  -- current connections

Reserve 5–10 connections for superuser access, monitoring, and migrations. Your application pool total across all instances should not exceed max_connections - reserved.

Step 2: Apply the concurrency formula

For CPU-bound database workloads:

optimal_connections ≈ CPU_cores × 2

For mixed workloads (some queries wait on disk I/O):

optimal_connections ≈ (CPU_cores × 2) + effective_disk_parallelism

With SSDs, effective disk parallelism is typically 1–2. For a 4-core database server with SSD: (4 × 2) + 1 = 9 total active connections across all clients.

Step 3: Distribute across instances

If you run N application instances, divide the ceiling:

# 4 app instances, database ceiling of 20 connections
pool_size_per_instance = 20 // 4  # = 5
max_overflow_per_instance = 3      # allow brief spikes

Step 4: Measure and adjust

Key metrics to monitor:

# SQLAlchemy pool status
pool = engine.pool
print(f"Pool size: {pool.size()}")
print(f"Checked out: {pool.checkedout()}")
print(f"Overflow: {pool.overflow()}")
print(f"Checked in (idle): {pool.checkedin()}")

Watch for:

• High checkedout/total ratio → pool may be too small
• Frequent overflow → pool_size is too low for sustained load
• Non-zero queue waits → connections aren’t being returned fast enough

Connection lifetime and recycling

Long-lived connections can encounter problems: stale server-side state, memory leaks in database backends, or connections killed by firewalls/load balancers (common in cloud environments where idle TCP connections timeout after 350 seconds on AWS).

engine = create_engine(
    "postgresql+psycopg2://...",
    pool_recycle=1800,     # force reconnect every 30 min
    pool_pre_ping=True,    # test before use (adds ~1ms latency)
)

pool_pre_ping sends a lightweight query (SELECT 1) before handing out a connection. This catches connections killed by firewalls at the cost of minimal latency.

PgBouncer: external pool multiplexing

When you have many application instances or serverless functions, PgBouncer decouples application-side and database-side connection counts:

; pgbouncer.ini
[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb

[pgbouncer]
pool_mode = transaction        ; release connection after each transaction
default_pool_size = 20         ; connections to the real database
max_client_conn = 1000         ; connections from applications
reserve_pool_size = 5          ; emergency connections for admin

In transaction mode, PgBouncer assigns a database connection only for the duration of a transaction. Between transactions, the connection is available for other clients. This means 1000 application connections can be served by 20 database connections — if transactions are short.

Caution with transaction pooling: Features that rely on session state — prepared statements, SET commands, advisory locks, LISTEN/NOTIFY — break in transaction mode because the underlying connection changes between transactions.

Anti-patterns and their fixes

Too large pool with no monitoring

# Anti-pattern: "more is better"
engine = create_engine("postgresql://...", pool_size=50, max_overflow=50)

100 connections from a single instance will overwhelm most databases. Start with 5–10 and increase only when metrics justify it.

Missing timeout configuration

# Anti-pattern: default 30-second timeout
engine = create_engine("postgresql://...", pool_timeout=30)

# Better: fail fast, let the load balancer retry
engine = create_engine("postgresql://...", pool_timeout=3)

Connection leaks

The most common sizing problem isn’t the size — it’s connections that are never returned:

# Anti-pattern: manual connection management
conn = engine.connect()
result = conn.execute(text("SELECT ..."))
# if an exception occurs here, conn is never closed

# Correct: context manager guarantees return
with engine.connect() as conn:
    result = conn.execute(text("SELECT ..."))

Not accounting for background tasks

If your web app also runs Celery workers, cron jobs, or management commands, each of those opens its own connections. Total pool sizing must include all processes that connect to the same database.

Production checklist

1. Set pool_size to database_ceiling / number_of_instances (round down)
2. Set max_overflow to 2–5 for absorbing brief spikes
3. Set pool_timeout to 3–5 seconds to fail fast
4. Enable pool_pre_ping in cloud environments
5. Set pool_recycle below your infrastructure’s idle connection timeout
6. Monitor pool utilization and database pg_stat_activity continuously
7. Use PgBouncer when you have more than 5 application instances or any serverless components

One thing to remember: Pool sizing is a system-level decision. The optimal size depends on the database server’s hardware, query complexity, transaction duration, and total client count — not on any single application’s needs.