How to Fix a Connection Pool Leak (Like a Node JS Memory Leak)

Why the API dies only under load

A connection pool caps how many database connections exist at once. This FastAPI service uses a small psycopg2 pool so the bug shows up fast:

from psycopg2 import pool
db_pool = pool.SimpleConnectionPool(minconn=1, maxconn=5, dsn=DATABASE_URL)

Each handler borrows a connection with db_pool.getconn() and is expected to give it back with db_pool.putconn(conn). The trap is the error path. Look at get_user:

@app.get("/api/users/{user_id}")
def get_user(user_id: int):
    conn = db_pool.getconn()
    cur = conn.cursor()
    cur.execute("SELECT id, name, email FROM users WHERE id = %s", (user_id,))
    row = cur.fetchone()
    cur.close()

    if row is None:
        # BUG: connection is never returned to the pool
        raise HTTPException(status_code=404, detail="User not found")

    db_pool.putconn(conn)
    return {"id": row[0], "name": row[1], "email": row[2]}

When the user exists, putconn runs and the connection returns. When it does not, the raise fires first and putconn is skipped. That one connection is leaked forever. Just like an unreleased buffer in a node js memory leak, each leaked connection is small on its own - but they accumulate. After 5 requests for a missing ID the pool is empty, and every new request blocks or 500s until something times out.

Step 1: reproduce the exhaustion

Hammer an endpoint that always raises, then check whether the API still answers:

# Trigger the leak by hitting non-existent user IDs
for i in $(seq 1 10); do curl -s http://localhost:8000/api/users/9999; done

# A working endpoint should still respond - after the leak it hangs or 500s
curl -s http://localhost:8000/api/users

Five bad requests drain a maxconn=5 pool. The healthy /api/users route now fails even though its own code is correct - it simply cannot borrow a connection.

Step 2: find every leaking handler

Any handler that calls getconn without a finally is suspect:

grep -n "getconn\|putconn\|finally" /workspace/main.py

In this service three handlers leak: get_user (404 on missing row), create_user (unique-violation on a duplicate email), and create_order (foreign-key violation on a bad user_id). Each acquires a connection, then hits a raise before putconn.

Step 3: wrap acquire/release in try/finally

The finally block runs whether the body returns or raises, so the connection always goes back:

@app.get("/api/users/{user_id}")
def get_user(user_id: int):
    conn = db_pool.getconn()
    try:
        cur = conn.cursor()
        cur.execute("SELECT id, name, email FROM users WHERE id = %s", (user_id,))
        row = cur.fetchone()
        cur.close()
        if row is None:
            raise HTTPException(status_code=404, detail="User not found")
        return {"id": row[0], "name": row[1], "email": row[2]}
    finally:
        db_pool.putconn(conn)

Apply the same shape to create_user and create_order. Then restart the server so the change takes effect:

pkill -f 'uvicorn main:app'
cd /workspace && uvicorn main:app --host 0.0.0.0 --port 8000 &

Re-run the error storm from Step 1 and the pool recovers - /api/users returns 200 every time because the failed requests hand their connections back.

The durable fix: a context manager

try/finally is correct but easy to forget on the next handler. Prefer a context manager that returns the connection for you, so no future error path can leak:

from contextlib import contextmanager

@contextmanager
def get_conn():
    conn = db_pool.getconn()
    try:
        yield conn
    finally:
        db_pool.putconn(conn)

Now with get_conn() as conn: is impossible to leak - cleanup is guaranteed by the language, not by remembering it at every return and raise.