Connection pooling: why your app ran out of connections

At 2:14 on a Tuesday the checkout endpoint starts throwing: the connection pool has been exhausted, either raise MaxPoolSize (currently 100) or Timeout (currently 15 seconds). Traffic is normal. The database is at 4 percent CPU. You restart the app, everything works, and forty minutes later it happens again. Nothing in the failing request's code has changed in months, which is your first real clue: the failing request is not the problem. It's the victim.

To see why, start with what the pool is protecting you from.

What a connection actually costs

"Open a connection" sounds like assigning a variable. On the wire it is a TCP handshake, then a TLS handshake, then authentication, then session setup on the server. PostgreSQL forks an operating system process for every connection and gives it several megabytes of memory before it has done any work for you. End to end, establishing a fresh connection costs tens of milliseconds in the same data center, and more across regions.

Paying that on every request would often cost more than the query itself. So drivers cheat, productively: they keep a pool of already-open physical connections, and what your code calls "opening a connection" is really borrowing one. Disposing it does not close the socket; it returns the lease. The pool is a semaphore with plumbing, and like any semaphore it has a fixed number of permits. Npgsql and SqlClient both default to 100 per connection string, per process.

That design is why the happy path is invisible: a borrow from a warm pool costs microseconds. It is also why the failure is so confusing. A fixed number of permits means the resource can run out, and it runs out in ways that point away from the cause.

How a pool drains

Connection pool: 10 slots

waiting: 0 waiting: 1 waiting: 3 waiting: 7

borrowed, then returned leaked, never returned

The useful mental model is Little's law: the number of leases you need at any moment is your request rate multiplied by how long each request holds its lease. Fifty requests per second holding a connection for 5 milliseconds needs about a quarter of one connection on average. The same fifty requests holding for 2 seconds needs 100, which is the entire default pool. Hold time is the lever, and all three classic causes of exhaustion are hold-time problems.

Leaked connections. A connection is opened and never disposed, usually on an early return or an exception path. The lease never comes back, so the pool is permanently one slot smaller. Leak one connection per unlucky request and the pool shrinks slot by slot until nothing is left. The .NET finalizer may eventually rescue some of them, long after your timeouts have already fired. That's cleanup, not a strategy.

Transactions held open across slow work. The code diligently disposes everything, but it opens a transaction, calls a payment provider that takes 8 seconds, then commits. The connection is on lease for the whole 8 seconds. With 100 slots and 8-second holds, your ceiling is 12.5 requests per second. At 20 requests per second, the queue for a free connection grows without bound and the pool timeout starts firing within the minute.

A pool sized below real concurrency. No leaks and no slow transactions, just 300 genuinely concurrent requests contending for 100 permits. This is the rarest of the three in the wild, and the only one that raising the pool size actually fixes. It is also the fix people apply first in all three cases, which is how you end up at a max pool size of 500 and still on fire.

The error fires far from the crime

Pool exhaustion inverts the blame. The request that throws is the one that arrived after the pool drained, and the request that drained it finished minutes ago with a 200.

The timeout works like this: a borrower that finds no free slot waits, and if nothing is returned within the wait window (15 seconds by default in Npgsql), it gives up and throws. So the exception surfaces on whichever innocent request happened to arrive after the last slot vanished, and its stack trace points at an innocent open call in an innocent endpoint. The leaker completed successfully, logged nothing unusual, and kept its connection on the way out.

This asymmetry is why pool exhaustion eats debugging days. Every signal you have, from the failing endpoint to the stack trace to the timing, describes a victim rather than the cause.

Finding the leak

The leak itself is usually mundane:

// the leak: an early return skips cleanup
public async Task<decimal> GetBalanceAsync(int accountId)
{
    var connection = new NpgsqlConnection(_connectionString);
    await connection.OpenAsync();

    var balance = await ReadBalanceAsync(connection, accountId);
    if (balance is null)
    {
        return 0m; // this path never returns the connection
    }

    await connection.CloseAsync();
    return balance.Value;
}

The fix is to make disposal structural instead of hopeful:

public async Task<decimal> GetBalanceAsync(int accountId)
{
    await using var connection = new NpgsqlConnection(_connectionString);
    await connection.OpenAsync();

    var balance = await ReadBalanceAsync(connection, accountId);
    return balance ?? 0m;
}

await using returns the lease on every exit path, including exceptions. Frameworks mostly do this for you (EF Core's DbContext manages its own connection, and Dapper runs on connections you scope yourself), so leaks concentrate in hand-rolled ADO.NET code, long-lived objects that capture a connection, and anything clever involving a static.

Two instruments find the rest. First, your driver's counters: Npgsql and SqlClient both expose busy, idle, and pending-request counts as runtime metrics, and a busy count that climbs without ever falling is a leak announcing itself. Second, ask the database who is holding what:

SELECT pid,
       state,
       now() - xact_start AS transaction_age,
       left(query, 60)   AS last_query
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY xact_start NULLS LAST;

Rows sitting in idle in transaction with a growing transaction_age are your slow-work-inside-a-transaction culprits, and last_query tells you which code to go read. An endpoint with an N+1 query problem shows up here too: it holds its lease for the entire drip of round trips, inflating hold time in a way no single query's duration reveals.

Sizing, and the multiplication nobody does

Max pool size is per process, but the database's connection limit is global. Those two facts collide the day your autoscaler works: six instances with a pool cap of 100 can legally demand 600 connections from a PostgreSQL whose default max_connections is 100, and every attempt beyond the server limit is refused outright, which is a different and worse error than a pool timeout. So do the multiplication. Max pool size times the maximum number of instances, plus headroom for migrations and humans with psql, has to fit inside the database's limit.

When the numbers refuse to fit, that is what a server-side pooler is for. PgBouncer sits in front of PostgreSQL and multiplexes thousands of client connections onto a few dozen real backends, at the cost of some session-level features depending on its pooling mode. Note what it does not do: it can't fix leaks or long transactions. It just moves the queue somewhere cheaper.

Learning to see the lease

Hold time is invisible in code review for the same reason N+1 is: the source reads identically whether a lease lasts 2 milliseconds or 8 seconds. What builds the reflex is watching real round trips and real durations attached to your own code, often enough that you start feeling every await between borrow and return.

Katabench wires that feedback straight into practice. The database track grades submissions on what they actually did to a real PostgreSQL instance, with every query and round trip on screen, and the performance docs explain how wall-clock budgets are enforced. Practice with the round-trip count in your face for a few weeks and data access code stops looking like syntax. It starts looking like leases, and the 2:14 page stops happening to you.