SQLAlchemy pool tuning: five parameters that matter

Capsule overview

You already know what a connection pool is, its lifecycle, and why it exists (capsule 02). Now we go to the operational detail: the five SQLAlchemy 2.0 pool parameters you'll be tuning constantly in production.

This capsule is the one you'll come back to consult the most later. Each parameter has a reasonable default for development, a clear behavior in production, and a trade-off you need to understand to choose it well:

• pool_size: how many connections to keep "hot" at all times.
• max_overflow: how many extra it can open under momentary load.
• pool_timeout: how long to wait when the pool is saturated.
• pool_pre_ping: detect dead connections before using them.
• pool_recycle: recycle connections after a certain time.

By the end you'll be able to configure create_engine or create_async_engine with well-founded values, and you'll know when each parameter should not be touched from the default.

Mental model: regulating the flow of a team of waiters

Imagine a restaurant. The connection pool is the waiters. The parameters are the rules for how the manager assigns them:

• pool_size = how many waiters you have on the fixed shift (always available).
• max_overflow = how many extra waiters you can call for a peak hour (they leave afterward).
• pool_timeout = how long a customer can wait at the door before leaving.
• pool_pre_ping = the manager asks "are you ready?" to each waiter before sending them to a table.
• pool_recycle = "no waiter works more than N hours straight, we rotate them".

Each rule costs something. More fixed waiters = a bigger payroll all the time. Active pre_ping = you lose 5 seconds per customer asking. A very low recycle = the waiters never settle in.

Correct tuning depends on the restaurant: a 20-table cafe with steady traffic doesn't need the same defaults as a 200-table food court with midday peaks.

Base setup: SQLAlchemy 2.0 with AsyncEngine

All the examples in this capsule assume the canonical FastAPI setup with SQLAlchemy 2.0 async. As a reminder:

# app/db.py
from sqlalchemy.ext.asyncio import create_async_engine, async_sessionmaker, AsyncSession

DATABASE_URL = "postgresql+asyncpg://bookstore:bookstore@localhost:5432/bookstore"

engine = create_async_engine(
    DATABASE_URL,
    # ↓ Here go the pool parameters we're going to tune:
    pool_size=10,
    max_overflow=5,
    pool_timeout=30,
    pool_pre_ping=False,
    pool_recycle=-1,
    echo=False,
)

SessionLocal = async_sessionmaker(engine, expire_on_commit=False, class_=AsyncSession)


async def get_db() -> AsyncSession:
    async with SessionLocal() as session:
        yield session

The pool parameters are passed to create_async_engine (or create_engine in sync). SQLAlchemy 2.0 uses QueuePool by default for sync and AsyncAdaptedQueuePool for async — both respect the same parameters.

pool_size: the heart of the pool

What it is: the number of connections SQLAlchemy keeps permanently open in the pool, ready to be handed out.

Default: 5.

How it works:

Initial state: 0 connections open (lazy).
First request arrives → opens connection #1.
Second parallel request → opens connection #2.
...
Request #5 → opens connection #5.
Request #6 with the 5 busy → if max_overflow > 0, opens #6 as overflow.
                            → if max_overflow = 0, waits up to pool_timeout.

When a request finishes:
  - If total open <= pool_size: the connection returns to the pool (stays idle).
  - If total open > pool_size (overflow): the connection is closed.

How to choose it:

Starting with the classic formula (HikariCP, originally for sync Java):

pool_size = ((core_count * 2) + effective_spindle_count)

For an API on a 4-core server with an SSD:

pool_size = (4 * 2) + 1 = 9

But there are big caveats for async (FastAPI + asyncpg):

• The formula assumes synchronous CPU-bound queries. In async, the queries don't compete for the client thread's CPU — they just wait on I/O. You can have a higher pool_size without saturating the client's CPU.
• Each FastAPI instance multiplies pool_size. If you have 4 instances, that's pool_size × 4 total potential connections.
• PostgreSQL handles connections in a limited way (capsule 02). If pool_size × instances > max_connections, you saturate.

Practical recommendation for async FastAPI without PgBouncer:

pool_size=10  # reasonable base

In production, measure and adjust — capsule 07 covers the complete methodology.

Recommendation with PgBouncer in transaction mode:

pool_size=20  # can be higher, PgBouncer multiplexes

Because the connections don't go directly to PostgreSQL — they go to PgBouncer, which does handle the multiplexing. Capsules 05-06 cover this.

Behavior under load

pool_size=10, max_overflow=0, no load:
  - 0 connections open initially.
  - 0 RAM consumed.

pool_size=10, max_overflow=0, after 100 requests:
  - 10 connections open (grew to the limit).
  - 10 idle connections between requests (waiting for reuse).

pool_size=10, max_overflow=0, 11 simultaneous requests:
  - 10 served.
  - 1 waiting, will raise TimeoutError after pool_timeout seconds.

max_overflow: the cushion for peaks

What it is: extra connections the pool can open under load, above pool_size. These connections are closed when the request finishes (they don't return to the pool).

Default: 10.

Why it exists: pool_size is sized for average load. Momentary peaks (a burst of requests, a load spike) would saturate the fixed pool. Overflow gives margin without having an enormous permanent pool_size.

pool_size=10, max_overflow=20:
  - Normal load: 10 connections alternating idle/active. Stable memory.
  - Peak: up to 30 simultaneous connections (10 fixed + 20 overflow).
  - After the peak: back to 10 (the 20 overflow are closed).

How to choose it

# Conservative setup
pool_size=10
max_overflow=5
# Max total: 15. Good when max_connections is tight.

# Elastic setup
pool_size=10
max_overflow=20
# Max total: 30. Good when max_connections has room and there are predictable peaks.

# Strict setup (no overflow)
pool_size=20
max_overflow=0
# Fixed total: 20. Good behind PgBouncer because the external pool absorbs peaks.

Heuristic:

• Without PgBouncer and with variable traffic: use elastic overflow (max_overflow >= pool_size).
• Behind PgBouncer: use small or zero overflow (max_overflow = 0 and raise pool_size).
• In tests/CI: pool_size=2, max_overflow=0 to detect leaks fast.

The cost of overflow

Overflow connections open on demand → each one pays the full handshake (50-200ms). If your peak generates 20 new connections, the first 20 requests of the peak have additional latency. It's not a problem if the peak lasts seconds. It IS a problem if the peak is sustained — in that case pool_size is badly sized.

pool_timeout: how long to wait before failing

What it is: the seconds a request waits for a connection from the pool before raising a TimeoutError.

Default: 30.

How it works:

Saturated pool (all connections in use).
Request #N arrives, asks for a connection.
Waits up to pool_timeout seconds.
  - If one frees up before: it gets it.
  - If the timeout passes: it raises sqlalchemy.exc.TimeoutError.

Why the value matters

The default of 30 seconds is too much for an HTTP API. If the pool is saturated and your HTTP client waits 30 seconds to fail, the HTTP client has probably already closed the connection (default timeout of 10-15s in typical clients). You're blocking a pool slot for no reason.

Recommendation:

pool_timeout=10  # low, fails fast

Reason: you'd rather return a 503 to the client in 10 seconds than have it waiting 30 — the app can degrade gracefully (retry, queue, a clear error message) instead of an HTTP timeout on the client side.

In batch systems (jobs, not HTTP) the default 30 can make sense — it waits longer because there's no impatient client on the other side.

How the timeout manifests

# If pool_timeout fires:
sqlalchemy.exc.TimeoutError: QueuePool limit of size 10 overflow 5 reached,
connection timed out, timeout 10.00 (Background on this error at: https://sqlalche.me/e/20/3o7r)

In a FastAPI endpoint without handling, this turns into HTTP 500. You'll want to intercept it and return 503 (Service Unavailable):

from fastapi import FastAPI, Request, status
from fastapi.responses import JSONResponse
from sqlalchemy.exc import TimeoutError as SAErrorTimeout

app = FastAPI()

@app.exception_handler(SAErrorTimeout)
async def db_timeout_handler(request: Request, exc: SAErrorTimeout):
    return JSONResponse(
        status_code=status.HTTP_503_SERVICE_UNAVAILABLE,
        content={"error": "Database temporarily overloaded, please retry."}
    )

pool_pre_ping: detect dead connections

What it is: runs a SELECT 1 before handing out each connection from the pool. If it fails, it discards that connection and opens a new one.

Default: False.

Why it exists: idle connections in the pool can die without SQLAlchemy noticing:

• Corporate firewalls close inactive connections after N minutes.
• Load balancers (AWS NLB, GCP) have idle timeouts.
• PostgreSQL can restart (deploy, failover) and lose all connections.
• Transient network issues.

Without pool_pre_ping, the first request after the problem fails with psycopg.OperationalError: connection has been closed unexpectedly — and the user sees it.

Enabling it

engine = create_async_engine(
    DATABASE_URL,
    pool_size=10,
    pool_pre_ping=True,  # ← detects dead connections
)

Behavior with pool_pre_ping=True:

1. App asks for a connection from the pool.
2. SQLAlchemy hands out connection #5 (idle for 10 minutes).
3. Before handing it out, it runs "SELECT 1" on it.
   3a. It works → hands out the connection. Extra latency: ~1-5ms (round-trip).
   3b. It fails → discards the connection, opens a new one, hands that out. Extra latency: 50-200ms (handshake).
4. App runs its normal query.

The trade-off

• With pool_pre_ping=True: each query pays 1-5ms of extra overhead. Your API is more reliable behind proxies/firewalls. Recommended in production.
• With pool_pre_ping=False: zero overhead, but the first request after a network glitch fails. Acceptable only if:
  • DB and app are in the same VPC without intermediate firewalls.
  • Latency matters more than reliability (low-internal-latency apps).
  • You have automatic client-level retries that cover the case.

Recommendation for typical production:

pool_pre_ping=True

The extra 1-5ms per query is acceptable in exchange for not having unpredictable errors. In apps with a p99 SLO < 50ms, consider turning it off and handling reconnection at the retry level.

pool_recycle: rotate old connections

What it is: the maximum lifetime of a connection in seconds. After that time, SQLAlchemy discards it and opens a new one (recycle).

Default: -1 (never recycles).

Why it exists: old connections accumulate problems:

• Memory leaks in some drivers (uncommon with asyncpg/psycopg but it exists).
• Intermediate servers (proxies, balancers) have a non-negotiable idle timeout.
• PostgreSQL can have connections that accumulate session state (caches, prepared statements).
• In cloud environments with DB autoscaling, old connections can end up pointing at old backends.

How to choose it

pool_recycle=3600  # 1 hour. Recommended default for production.

Reasons:

• 1 hour is less than the typical idle timeout of most proxies/balancers (they're usually 5-30 minutes for idle, but "max lifetime" is usually 1-2 hours).
• It's enough to amortize the handshake cost (60 minutes of queries + 200ms of reconnection = negligible overhead).
• It doesn't cause perceptible operational problems.

Lower (15 minutes = 900):

pool_recycle=900

Useful if:

• Connections behind an NLB/ELB with a very short idle timeout.
• You detected a memory leak attributable to old connections.

Higher (4 hours = 14400):

pool_recycle=14400

Only if:

• You measured and pool_recycle=3600 adds perceptible overhead (rare).
• Your setup has no intermediate proxies.

Default -1 (never): only valid in local development. In production it's a trap waiting to happen.

Recommended configuration for typical production

# app/db.py — complete recommended setup for async FastAPI without PgBouncer
from sqlalchemy.ext.asyncio import create_async_engine

engine = create_async_engine(
    "postgresql+asyncpg://bookstore:bookstore@db:5432/bookstore",

    # Pool sizing
    pool_size=10,           # permanent base
    max_overflow=20,        # margin for peaks

    # Behavior under load
    pool_timeout=10,        # fail fast under saturation

    # Reliability
    pool_pre_ping=True,     # detect dead connections
    pool_recycle=3600,      # rotate after 1 hour

    # Others
    echo=False,             # disable SQL log in production
)

When you add PgBouncer (capsules 05-06):

engine = create_async_engine(
    "postgresql+asyncpg://bookstore:bookstore@pgbouncer:6432/bookstore",  # ← points at PgBouncer

    pool_size=20,           # higher, PgBouncer multiplexes
    max_overflow=0,         # PgBouncer absorbs peaks
    pool_timeout=10,
    pool_pre_ping=True,
    pool_recycle=3600,

    # Critical when PgBouncer is in transaction mode (capsule 06):
    connect_args={"statement_cache_size": 0},
)

Why this matters in real work

1. It's one of the first tunings you touch in any production FastAPI app. In staging with the pool_size=5 default it works. In production it doesn't. Knowing what to change and why saves you incidents from the first week.

2. SQLAlchemy's defaults are for development. pool_size=5, pool_timeout=30, pool_pre_ping=False, pool_recycle=-1 are reasonable for running tests, not for serving real traffic. Bringing these defaults to production is one of the most common junior mistakes.

3. pool_pre_ping can save you from a long postmortem. "After the DB deploy, the first request always fails with 'connection closed'." If you don't know pool_pre_ping exists, you debug for hours. With this, you add one line and it's solved.

4. pool_recycle is the difference maker in the cloud. AWS/GCP/Azure have NAT gateways, NLBs, security groups with variable timeouts. Without recycle, you'll have intermittent errors you reproduce only in production. With recycle, they don't exist.

5. The parameters matter more than the framework. A team moving from FastAPI to Litestar or from SQLAlchemy to another ORM will use the same concepts: pool size, overflow, pre-ping, recycle. The intuition transfers.

Traps and common mistakes

Mistake 1 (conceptual): raising pool_size without thinking about the rest of the cluster

Symptom: "I raised pool_size from 10 to 50 and now PostgreSQL tells me 'too many connections for role'."

Why it happens: you forgot that your app runs on N instances. If you have 4 instances × pool_size=50 = 200 connections. PostgreSQL with max_connections=100 rejects half.

How to distinguish: verify pool_size × num_instances × (1 + overflow_ratio) vs max_connections - reserved.

How to fix it: either raise max_connections, or add PgBouncer (capsule 05) so the real cap is PgBouncer and not PostgreSQL.

Mistake 2 (operational): leaving pool_pre_ping=False in cloud production

Symptom: "Every so often (15-30 minutes) the first request after inactivity fails with 'connection closed'. If I retry, it works."

Why it happens: the load balancer / corporate firewall silently closes idle connections. Without pool_pre_ping, SQLAlchemy hands them out believing they're alive.

How to distinguish: logs show psycopg.OperationalError or asyncpg.exceptions.ConnectionDoesNotExistError after inactivity. A manual retry works.

How to fix it: enable pool_pre_ping=True. The overhead of 1-5ms per query is acceptable.

Mistake 3 (conceptual): assuming that max_overflow "fixes" an under-sized pool

Symptom: "The pool saturates. I raise max_overflow from 10 to 100. Now it doesn't saturate but the p95 latency is horrible."

Why it happens: max_overflow is meant for momentary peaks (seconds). If the load is sustained, the overflow connections open and close constantly — each opening pays the handshake (50-200ms). Your p95 rises because each "extra" request pays that overhead.

How to distinguish: monitoring shows many connections opening and closing constantly (not stable). p95 latency rises linearly with load.

How to fix it: raise pool_size (not max_overflow). Permanent connections amortize the handshake. max_overflow should be a valve, not the main capacity.

Mistake 4 (operational): a pool_timeout too high blocks workers

Symptom: "The pool saturates. The requests don't fail fast — they hang for 30 seconds. Meanwhile, the uvicorn workers are blocked."

Why it happens: the default pool_timeout=30 means each blocked request waits 30 seconds. While it waits, that uvicorn worker is busy. Your total throughput drops to zero because all the workers are waiting for the pool instead of serving new requests.

How to distinguish: during saturation, almost all the workers are in a "waiting for pool" state. Throughput drops much more than when the pool is simply "full".

How to fix it: lower pool_timeout to 5-10 seconds. Requests fail fast with 503, the workers free up, and the system degrades gracefully instead of freezing.

Mistake 5 (conceptual): not understanding that pool_recycle affects only idle connections

Symptom: "I configured pool_recycle=900 (15 min). But I see connections from 2 hours ago in pg_stat_activity."

Why it happens: pool_recycle is checked when the connection returns to the pool or when it's about to be handed out. If a connection is being used actively all the time (rare but possible in very high-concurrency apps), it's never "offered" for recycling.

How to distinguish: the long-lived connections you see are the active ones, not the idle ones.

How to fix it: pool_recycle is a good practice. If you need a strict guarantee of "no connection older than X", PostgreSQL has idle_session_timeout and connection-killing tools by age. But it's rarely necessary.

Exercises

Exercise 1: tune your bookstore's pool

Your FastAPI app runs on 2 instances (uvicorn 1 worker each). PostgreSQL has max_connections=100. You have margin to reserve 80 connections for your app. You don't have PgBouncer yet. Design the pool configuration.

Total constraint: 80 connections for 2 instances = 40 per instance.

Suggested distribution:
- pool_size = 20 (permanent base load)
- max_overflow = 15 (margin for peaks up to 35 per instance)
- Total with overflow at maximum: 2 × 35 = 70 connections (fits in 80).

Final configuration:

engine = create_async_engine(
    "postgresql+asyncpg://bookstore:bookstore@db:5432/bookstore",
    pool_size=20,
    max_overflow=15,
    pool_timeout=10,
    pool_pre_ping=True,
    pool_recycle=3600,
    echo=False,
)

-- After deploy, monitor:
SELECT count(*), state
FROM pg_stat_activity
WHERE datname = 'bookstore'
GROUP BY state;

Exercise 2: measure the impact of pool_pre_ping

Configure two engines, one with pool_pre_ping=True and another with False. Measure the average latency of 100 SELECT 1 queries on each one.

# bench_preping.py
import asyncio
import time
from sqlalchemy.ext.asyncio import create_async_engine
from sqlalchemy import text

DSN = "postgresql+asyncpg://bookstore:bookstore@localhost:5432/bookstore"


async def benchmark(pre_ping: bool):
    engine = create_async_engine(
        DSN,
        pool_size=5,
        pool_pre_ping=pre_ping,
    )
    # Warm up
    async with engine.connect() as conn:
        await conn.execute(text("SELECT 1"))

    # Measure 100 queries
    start = time.perf_counter()
    for _ in range(100):
        async with engine.connect() as conn:
            await conn.execute(text("SELECT 1"))
    elapsed = time.perf_counter() - start

    await engine.dispose()
    return elapsed


async def main():
    no_ping = await benchmark(pre_ping=False)
    with_ping = await benchmark(pre_ping=True)

    print(f"Without pre_ping: {no_ping*1000:.1f}ms total ({no_ping*10:.2f}ms per query)")
    print(f"With pre_ping:    {with_ping*1000:.1f}ms total ({with_ping*10:.2f}ms per query)")
    print(f"Overhead: {(with_ping - no_ping)*10:.2f}ms per query")


asyncio.run(main())

Without pre_ping: 60.5ms total (0.61ms per query)
With pre_ping:    162.3ms total (1.62ms per query)
Overhead: 1.01ms per query

Exercise 3: simulate pool saturation and observe the behavior

Configure pool_size=2, max_overflow=0, pool_timeout=3. Launch 5 async queries in parallel, each with a SELECT pg_sleep(2). Predict what happens with each one.

# saturation.py
import asyncio
import time
from sqlalchemy.ext.asyncio import create_async_engine
from sqlalchemy import text

DSN = "postgresql+asyncpg://bookstore:bookstore@localhost:5432/bookstore"

engine = create_async_engine(
    DSN,
    pool_size=2,
    max_overflow=0,
    pool_timeout=3,
)


async def slow_query(idx: int):
    start = time.perf_counter()
    try:
        async with engine.connect() as conn:
            print(f"[{idx}] got connection in {time.perf_counter()-start:.2f}s")
            await conn.execute(text("SELECT pg_sleep(2)"))
            print(f"[{idx}] finished")
    except Exception as e:
        print(f"[{idx}] ERROR after {time.perf_counter()-start:.2f}s: {type(e).__name__}")


async def main():
    await asyncio.gather(*[slow_query(i) for i in range(5)])
    await engine.dispose()


asyncio.run(main())

[0] got connection in 0.01s
[1] got connection in 0.01s
[0] finished
[1] finished
[2] got connection in 2.05s
[3] got connection in 2.05s
[4] ERROR after 3.01s: TimeoutError
[2] finished
[3] finished

Exercise 4: detect leaked connections manually

Write a FastAPI endpoint that (on purpose) doesn't close the session correctly. Make 20 requests to it. Verify with pg_stat_activity that the connections stay hung.

# leak_endpoint.py
from fastapi import FastAPI
from sqlalchemy.ext.asyncio import create_async_engine, AsyncSession, async_sessionmaker
from sqlalchemy import text

app = FastAPI()
engine = create_async_engine(
    "postgresql+asyncpg://bookstore:bookstore@localhost:5432/bookstore",
    pool_size=5,
    max_overflow=20,
)
SessionLocal = async_sessionmaker(engine)


# Antipattern on purpose: doesn't close the session
@app.get("/leaky")
async def leaky():
    session = SessionLocal()  # ← opens, never closes
    await session.execute(text("BEGIN"))
    await session.execute(text("SELECT 1"))
    # Returns a response without closing the session or doing commit/rollback
    return {"status": "ok"}


# Correct pattern for comparison
@app.get("/clean")
async def clean():
    async with SessionLocal() as session:
        await session.execute(text("SELECT 1"))
    return {"status": "ok"}

# Terminal 1: bring up the app
uvicorn leak_endpoint:app --port 8000

# Terminal 2: fire 20 requests at the bad endpoint
for i in {1..20}; do curl -s http://localhost:8000/leaky > /dev/null; done

# Terminal 3: inspect
docker exec -it bookstore-pg psql -U bookstore -d bookstore -c "
SELECT count(*), state
FROM pg_stat_activity
WHERE datname = 'bookstore' AND application_name LIKE '%asyncpg%'
GROUP BY state;
"

 count |        state
-------+---------------------
    20 | idle in transaction

SELECT pid, query, state_change, application_name
FROM pg_stat_activity
WHERE state = 'idle in transaction';

-- Kill idle in transaction connections older than 60 seconds:
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND state_change < NOW() - INTERVAL '60 seconds';

ALTER SYSTEM SET idle_in_transaction_session_timeout = '60s';
SELECT pg_reload_conf();

Exercise 5: predict behavior under real load

For this configuration:

engine = create_async_engine(
    DATABASE_URL,
    pool_size=5,
    max_overflow=10,
    pool_timeout=5,
)

If your API constantly receives 30 requests per second, each request runs a query that takes 100ms, will the pool saturate? Justify it with a Little's Law calculation.

λ = 30 req/s
W = 0.1s (100ms)
L = 30 × 0.1 = 3 average connections

If the query took 500ms instead of 100ms:
L = 30 × 0.5 = 15 average connections.
Pool max: 5 + 10 = 15. Right at the limit. Any peak → saturation.

If you reached 100 req/s with 100ms per query:
L = 100 × 0.1 = 10 average connections.
Pool max: 15. Reduced margin. Peaks saturate fast.

Summary and next step

In this capsule you:

• Internalized the five core parameters of the SQLAlchemy pool: pool_size, max_overflow, pool_timeout, pool_pre_ping, pool_recycle.
• Learned the defaults and why almost none apply directly to production.
• Configured the recommended setup for async FastAPI without PgBouncer.
• Identified the real trade-offs: pre_ping overhead, extended overflow cost, the risk of a very high pool_timeout.
• Practiced sizing with Little's Law to predict saturation before it occurs.

Before moving on, you should be able to:

• Configure create_async_engine with well-founded values, not copied ones.
• Diagnose "pool exhausted" and decide whether to raise pool_size or add max_overflow or reduce slow queries.
• Apply pool_pre_ping and pool_recycle in cloud production by default.
• Calculate expected average connections with known throughput and latency.

Next capsule — asyncpg and AsyncEngine in detail. We saw the pool parameters in general. Now we go to the specific driver (asyncpg) and the peculiarities of async: how asyncpg handles prepared statements internally, what happens when connect_args varies, the FastAPI patterns of session-per-request with Depends(get_db) that avoid leaks, and why expire_on_commit=False is almost always the right thing in async. It's the capsule that lands the pool theory in your app's real code.

Resources

1. SQLAlchemy 2.0 — QueuePool reference — the detailed reference for the parameters.
2. SQLAlchemy 2.0 — Engine and Connection Use — context on how the engine manages the pool.
3. HikariCP — About Pool Sizing — the classic pool sizing formula and why.
4. Mike Bayer — Connection Pooling deep dive (PyCon) — talks by the author of SQLAlchemy.
5. Brandur Leach — Postgres connection pooling — an architectural view.
6. PostgreSQL idle_in_transaction_session_timeout — a safety net for transaction leaks.

Module 6 — Database Performance & Query Tuning Guide