How to Fix a Postgres Connection Pool Leak

What a connection pool leak looks like

Your API responds normally for a few minutes, then all requests hang indefinitely - no errors, no timeouts from the app, just silence. Restarting the process fixes it for a few minutes, then the same thing happens again.

The giveaway is a /status endpoint (or a direct pg_stat_activity query) that shows all connections in use even when no requests are actively running:

curl http://localhost:8080/status
# {"connections_used": 10, "connections_max": 10}

# Fire some requests, especially ones that hit error paths
for i in $(seq 1 10); do curl -s -o /dev/null http://localhost:8080/products; done
curl http://localhost:8080/status
# {"connections_used": 10, "connections_max": 10}  <- never goes down

The root cause: release_conn missing from the error path

The typical raw psycopg2 pool pattern looks like this:

from psycopg2 import pool

db_pool = pool.SimpleConnectionPool(1, 10, dsn="...")

def get_conn():
    return db_pool.getconn()

def release_conn(conn):
    db_pool.putconn(conn)

@app.route("/products")
def list_products():
    conn = get_conn()
    try:
        cur = conn.cursor()
        cur.execute("SELECT id, name FROM products LIMIT 50")
        rows = cur.fetchall()
        cur.close()
        release_conn(conn)        # called on the happy path
        return jsonify(rows)
    except Exception as e:
        return jsonify({"error": str(e)}), 500   # BUG: release_conn never called

When an exception fires before release_conn, the connection is checked out but never returned. After 10 such errors the pool is dry and every call to getconn() blocks, taking all healthy requests down with it.

The fix: move release_conn into a finally block

finally runs on every exit from the try - success, exception, or early return:

@app.route("/products")
def list_products():
    conn = get_conn()
    try:
        cur = conn.cursor()
        cur.execute("SELECT id, name FROM products LIMIT 50")
        rows = cur.fetchall()
        cur.close()
        return jsonify(rows)
    except Exception as e:
        return jsonify({"error": str(e)}), 500
    finally:
        release_conn(conn)    # runs on every exit path

Every handler that calls get_conn() needs the same pattern - including 404 early returns and any code path that raises before the happy-path putconn.

The better pattern: context managers

Raw getconn/putconn is error-prone because the programmer has to remember to release on every exit. Modern psycopg2 and psycopg3 support context managers that handle this automatically:

# psycopg3 - connection is returned to the pool on __exit__
with pool.connection() as conn:
    cur = conn.execute("SELECT id, name FROM products LIMIT 50")
    rows = cur.fetchall()
    return jsonify(rows)

ORMs (SQLAlchemy, Django ORM) also manage connection lifecycle for you. Raw pool APIs are footgun-shaped - context managers are the right long-term fix.