Scaling Failure Modes: Connection Pools, Timeouts, Cache Stampedes, N+1 and Queues

Five ordinary mistakes cause scaling trouble in web apps: a pool that runs dry, a call with no timeout, a hot key that expires under load, a loop of queries, slow work done while the user waits. This post reproduces each on one laptop, with a complete script and the output that exact script printed, so you can recognise the symptom and apply the fix.

The first example shows the shape of it. A pool of 5 connections serving 20 requests that each hold a connection for 1 second lost 5 of them to a 2-second wait timeout in my runs, and 9 to 10 when the timeout was 1.5 seconds. With max_waiting=5, the 10 requests that couldn't be served were rejected in under a millisecond (the script printed 0.000s) instead of after 2 seconds.

What I ran and what it can't tell you

One Windows 11 laptop (8 logical CPUs, 8 GB given to Docker): Python 3.12.10, psycopg 3.3.6 with psycopg-pool 3.3.3, redis-py 5.2.1, Postgres 16 and Redis 7 in Docker. Everything runs over localhost with thread-based pools and an in-process queue, on a machine shared with other containers, so timings vary between runs (where it matters I show several) and illustrate mechanisms rather than predict production. I never measured database CPU, and the queue demo's "sync" figures are computed from sleep(0.5) and a worker count, not measured against a real mail server. Setup:

docker run -d --name pgdemo -e POSTGRES_PASSWORD=pw -p 55432:5432 postgres:16-alpine
docker run -d --name redisdemo -p 56379:6379 redis:7-alpine
python -m venv v && v/Scripts/activate      # v/bin/activate on Linux/macOS
pip install "psycopg[binary]==3.3.6" psycopg-pool==3.3.3 redis==5.2.1

Save the scripts under these names: d_pool.py and d_activity.py (section 1; d_activity.py is listed in full there), d_timeout.py and d_http.py (section 2), d_stampede.py and d_redis.py (section 3), d_n1.py (section 4), d_queue.py (section 5). The Postgres demos assume a fresh postgres:16-alpine container: d_n1.py drops and recreates tables named posts and authors in the postgres database, so don't point it at a database you care about. Sections 1 and 2 use threads and psycopg's synchronous pool.

Which failure do you have? Decision rule by symptom

• Requests time out or queue while the queries themselves are fast → pool wait or a stuck statement (sections 1 and 2). Tell them apart with the pg_stat_activity query in section 1.
• The same query text with the same parameters appears many times at the same moment (for example right after a deploy or cache flush) → stampede (section 3): many requests are recomputing one key.
• One request logs many queries with the same text but different ids (where id = 1, where id = 2, ...) → N+1 (section 4): one request is looping over rows.
• An endpoint is slow only when it calls a slow third party → slow work in the request path (section 5).

1. Connection pool exhausted: how to diagnose and fix PoolTimeout

A pool hands out a fixed number of database connections. When all are busy the next request waits, and what happens then is your config. The psycopg-pool defaults are a 30-second wait and an unlimited wait queue. Checked on 3.3.3 by constructing a pool without options and printing it (ConnectionPool(dsn, open=False)):

timeout = 30.0 | max_waiting = 0

max_waiting=0 means no limit. A saturated pool doesn't fail fast; it makes every request wait up to 30 seconds.

d_pool.py: 20 concurrent requests, pool of 5, each holding a connection with pg_sleep, 2-second wait timeout. The error count at wait=2.0 is a race, which is why it repeats five times; wait=1.5 pins it (the three runs after those: wave 3 starts at 2.0s, so it always times out). The last three runs set max_waiting=5, the fix:

import time, threading
from psycopg_pool import ConnectionPool, PoolTimeout, TooManyRequests

DSN = "postgresql://postgres:pw@localhost:55432/postgres"

def run(pool_size, hold, n=20, wait=2.0, max_waiting=0):
    pool = ConnectionPool(DSN, min_size=pool_size, max_size=pool_size, timeout=wait,
                          max_waiting=max_waiting, open=True)
    pool.wait()
    lat, errors, lock = [], [], threading.Lock()

    def req():
        t = time.perf_counter()
        err = None
        try:
            with pool.connection() as c:
                c.execute("select pg_sleep(%s)", (hold,))   # the "query"
        except (PoolTimeout, TooManyRequests) as e:
            err = type(e).__name__
        dt = time.perf_counter() - t
        with lock:
            lat.append(dt)
            if err: errors.append((err, dt))

    ts = [threading.Thread(target=req) for _ in range(n)]
    start = time.perf_counter()
    for t in ts: t.start()
    for t in ts: t.join()
    lat.sort()
    kinds = {k for k, _ in errors}
    slowest = max((d for _, d in errors), default=0)
    print(f"pool={pool_size} hold={hold}s n={n} max_waiting={max_waiting}: total={time.perf_counter()-start:.2f}s "
          f"p50={lat[n//2]:.2f}s max={lat[-1]:.2f}s errors={len(errors)} {sorted(kinds)} slowest_error={slowest:.3f}s")
    pool.close()

if __name__ == "__main__":
    run(5, 0.5)
    for _ in range(5): run(5, 1.0)     # repeated: the error count is a race
    for _ in range(3): run(5, 1.0, wait=1.5)       # wait=1.5: wave 3 starts at 2.0s, so it always fails
    run(20, 1.0)
    for _ in range(3): run(5, 1.0, max_waiting=5)   # bounded wait queue: overload fails fast

Output (hold=1.0s repeats on purpose; the three wait=1.5 lines show slowest_error near 1.5s, their timeout):

pool=5 hold=0.5s n=20 max_waiting=0: total=2.09s p50=1.51s max=2.07s errors=0 [] slowest_error=0.000s
pool=5 hold=1.0s n=20 max_waiting=0: total=3.06s p50=2.01s max=3.00s errors=5 ['PoolTimeout'] slowest_error=2.018s
pool=5 hold=1.0s n=20 max_waiting=0: total=3.13s p50=2.00s max=2.94s errors=5 ['PoolTimeout'] slowest_error=2.014s
pool=5 hold=1.0s n=20 max_waiting=0: total=3.13s p50=2.01s max=2.80s errors=5 ['PoolTimeout'] slowest_error=2.019s
pool=5 hold=1.0s n=20 max_waiting=0: total=3.10s p50=2.00s max=2.85s errors=5 ['PoolTimeout'] slowest_error=2.016s
pool=5 hold=1.0s n=20 max_waiting=0: total=3.06s p50=2.00s max=2.95s errors=5 ['PoolTimeout'] slowest_error=2.014s
pool=5 hold=1.0s n=20 max_waiting=0: total=2.04s p50=1.52s max=1.99s errors=10 ['PoolTimeout'] slowest_error=1.568s
pool=5 hold=1.0s n=20 max_waiting=0: total=2.05s p50=1.59s max=1.99s errors=10 ['PoolTimeout'] slowest_error=1.692s
pool=5 hold=1.0s n=20 max_waiting=0: total=3.04s p50=1.52s max=2.53s errors=9 ['PoolTimeout'] slowest_error=1.527s
pool=20 hold=1.0s n=20 max_waiting=0: total=1.23s p50=1.01s max=1.02s errors=0 [] slowest_error=0.000s
pool=5 hold=1.0s n=20 max_waiting=5: total=2.26s p50=1.01s max=2.20s errors=10 ['TooManyRequests'] slowest_error=0.000s
pool=5 hold=1.0s n=20 max_waiting=5: total=2.19s p50=1.01s max=2.17s errors=10 ['TooManyRequests'] slowest_error=0.000s
pool=5 hold=1.0s n=20 max_waiting=5: total=2.04s p50=1.01s max=2.03s errors=10 ['TooManyRequests'] slowest_error=0.000s

Reading it:

• hold=0.5s, no errors: 20 requests ÷ 5 connections × 0.5s = 2s to drain the queue, right at the 2s wait limit. The last wave got its connection at 1.5s, inside the 2s limit, and finished its 0.5s query (max=2.07s in the output).
• hold=1.0s, wait=2.0: 5 errors in all five runs shown. Requests run in waves of five. Waves 1 and 2 finish at 1s and 2s. Wave 4 can never be served in time, so at least 5 errors are guaranteed. Wave 3 gets a connection at about 2.0s, exactly when its 2s timeout fires, so it is a coin flip per request; all five runs here came out the same, but I wouldn't count on that. The stable facts are "at least a quarter fail" and "nothing in the database was slow".
• wait=1.5: waves 3 and 4 can't be served in time, so about 10 errors; I got 10, 10 and 9 (a request can still sneak in as a connection is released).
• pool=20: every request gets a connection immediately and the total is the 1s hold.
• max_waiting=5: 5 requests run, 5 wait, and the other 10 are rejected with TooManyRequests in under a millisecond (slowest_error 0.000s) instead of after 2 seconds. The 10 accepted requests all succeed, and the whole burst is over in about 2s. Overload becomes a fast, countable error you can turn into a 503 and a retry hint.

Nothing in the database is slow; the waiting happens in your app. A stuck statement looks similar from the outside, so check pg_stat_activity while it is happening. d_activity.py runs two 4-second pg_sleep statements and one connection left open mid-transaction, then queries it after 2 seconds:

import time, threading, psycopg

DSN = "postgresql://postgres:pw@localhost:55432/postgres"

def sleeper():
    with psycopg.connect(DSN, autocommit=True) as c:
        c.execute("select pg_sleep(4)")

ts = [threading.Thread(target=sleeper) for _ in range(2)]
for t in ts: t.start()

idle = psycopg.connect(DSN)            # not autocommit: the next statement opens a transaction
idle.execute("select 1")               # ...and we never commit, so it stays "idle in transaction"

time.