PostgreSQL

Postgres Connection Pool Leak With Node or Python App

A connection pool leak means your application is checking out database connections from the pool but not consistently returning them, so the pool gradually shrinks until no connections are left available — eventually surfacing as timeout errors even though the pool was correctly sized for the application's actual concurrent needs.

The Problem

After the application runs for a while — sometimes hours, sometimes days — new database queries start failing with connection pool exhaustion errors:

Error: Connection pool timeout: could not acquire a connection in 30000ms
psycopg2.pool.PoolError: connection pool exhausted

Restarting the application temporarily fixes it, since the pool resets to full capacity, but the same failure gradually reappears over time.

Why It Happens

Nearly every connection pool leak comes down to a code path that checks out a connection but doesn't return it under some specific condition — usually an error path that skips the normal cleanup logic. Common causes:

  • An exception thrown between checking out a connection and returning it, when the return/release call isn't wrapped in a finally block (or equivalent), so an error skips the cleanup step entirely
  • Using a connection outside of the pool's recommended context manager or scoped-usage pattern, manually managing checkout/release in a way that's easy to get wrong under specific error conditions
  • A connection being held open longer than necessary because it's stored in a broader scope (like a global variable or long-lived object) rather than being scoped tightly to the specific operation that needs it
  • Async code where a connection is checked out but the async function is cancelled or times out before reaching the release step, leaving the connection in limbo

The Fix

In Node.js with the pg library, always use a try/finally block to guarantee release, regardless of whether the query succeeds or throws:

const client = await pool.connect();
try {
  const result = await client.query('SELECT * FROM orders WHERE id = $1', [id]);
  return result.rows;
} finally {
  client.release();
}

The finally block is what guarantees this — without it, an error thrown by the query means client.release() is never reached, and that connection is permanently lost from the pool's available count until the application restarts.

In Python with psycopg2's connection pool, use the same pattern, or better, use a context manager if your specific pool implementation supports one, since it handles this automatically:

conn = pool.getconn()
try:
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM orders WHERE id = %s", (order_id,))
    return cursor.fetchall()
finally:
    pool.putconn(conn)

For frameworks or ORMs with their own connection management (like SQLAlchemy), prefer their built-in scoped session or connection context managers over manual connection handling, since these are specifically designed to guarantee correct release even in error conditions:

with engine.connect() as connection:
    result = connection.execute(query)
# connection automatically returned to the pool when the with block exits,
# even if an exception occurred inside it

Audit your codebase specifically for any place a connection is checked out manually outside of a guaranteed-cleanup pattern (a try/finally, a context manager, or a framework's built-in scoping) — these manual checkout points are the most likely source of a leak, since they require the developer to remember to handle every possible exit path correctly, which is easy to get wrong under a specific rare error condition that doesn't show up in normal testing.

Still Not Working?

If you've reviewed the obvious connection-handling code and still see the pool shrinking over time, add logging around checkout and release specifically to track the pool's real-time active connection count, correlating spikes in "checked out but not returned" connections with specific application activity:

console.log(`Pool: total=${pool.totalCount}, idle=${pool.idleCount}, waiting=${pool.waitingCount}`);

Logging this periodically (or on each checkout) over an extended period, especially during whatever traffic pattern seems to correlate with the leak appearing, often reveals which specific endpoint or background job is responsible, even when a full code review doesn't immediately surface the exact leaking code path.

You might also like