TheHiddenWayAsyncCodeQuietlyDrainsYourConnectionPool

The Problem Nobody Talks About Until It's Too Late

Your application is humming along fine in development. Tests pass. Code looks clean. You deploy to production, traffic picks up, and then — mysteriously — your app starts throwing database timeout errors. Requests pile up. Users see spinning loaders. Your database server is barely sweating, but your app is on its knees. You check CPU. Normal. Memory. Fine. Database query times. Totally reasonable.

What's actually happening is that your connection pool — the managed collection of pre-established database connections your app reuses to avoid the overhead of opening a new connection every time — is completely exhausted. Empty. Every connection is checked out, and new requests are just waiting in line like people at the DMV on a Monday morning.

I've seen this exact scenario play out on three separate teams I've worked with. Each time, the culprit was the same thing: async code that looked efficient but was silently holding onto database connections far longer than necessary. The tragedy is that the code was often written with good intentions, by experienced developers who understood async programming. The problem isn't ignorance. The problem is that the failure mode is nearly invisible until it becomes catastrophic.

Understanding the Connection Pool First

Before we get to the async part, let's make sure we're on the same page about what a connection pool actually does. Opening a fresh TCP connection to a database is expensive. We're talking about multiple network round trips, authentication handshakes, and memory allocation on both ends. If your app opened a brand new connection for every single query, you'd waste enormous time just establishing connections before any real work happened.

A connection pool solves this by keeping a set of connections alive and ready to go. When your code needs to run a query, it borrows a connection from the pool. When it's done, it hands the connection back. The pool has a maximum size — let's say 10 connections — and if all 10 are checked out, any code that needs a connection has to wait until one gets returned. This is the finite resource at the heart of everything we're about to discuss.

Think of it like a library with exactly 10 copies of a popular book. Most of the time, this is fine. People borrow a book, read it quickly, and return it. But if 10 people borrow books and then leave town for a week, nobody else can read anything, no matter how many people are waiting.

How Async Programming Changes the Rules

Synchronous code is sequential and predictable. You call a function, it does its work, it returns. The time a resource is held is directly proportional to how long that function actually needs it.

Async programming is different. When you await something, your current execution is suspended. The thread (or event loop worker) is free to go do other things. This is the whole point — you can handle many concurrent operations without needing a thread per operation. It sounds efficient. It is efficient, in many cases. But here's the trap: if you hold onto a database connection across an await point, that connection stays checked out while your code is suspended, waiting for something completely unrelated to the database.

In my experience, the most dangerous pattern looks completely innocent. You fetch some data from the database, then you do something async — maybe call an external API, send an HTTP request, process a file — and then you use the database again. The connection you grabbed at the start is locked up during that entire middle section, even though your code isn't touching the database at all during that time.

The Async Leak in Action

Let me show you a concrete example. Here's a Python async function using SQLAlchemy's async session that demonstrates the problem pattern:

import asyncio
import httpx
from sqlalchemy.ext.asyncio import AsyncSession

async def process_user_order(session: AsyncSession, user_id: int):
    # Connection is checked out from the pool HERE
    result = await session.execute(
        select(User).where(User.id == user_id)
    )
    user = result.scalar_one()

    # ⚠️ DANGER ZONE: Connection is STILL held while we call an external API
    # This could take 200ms, 500ms, or 5 seconds if the API is slow
    async with httpx.AsyncClient() as client:
        payment_data = await client.get(
            f"https://payments.example.com/user/{user_id}/balance"
        )

    # Connection is still checked out all this time
    # Now we do another DB operation
    await session.execute(
        update(Order).where(Order.user_id == user_id).values(
            balance=payment_data.json()["balance"]
        )
    )
    await session.commit()
    # Connection is returned to the pool only NOW

See the problem? The database connection is held for the entire duration of that HTTP call to the payments API. If that API takes 500ms and you have 10 concurrent users hitting this endpoint, every connection in your pool is tied up waiting on an external service. New requests queue up. Timeouts start firing. Your app looks broken even though your database is perfectly healthy.

The fix is conceptually simple but requires thinking about your resource lifecycle more carefully. You want to release the database connection before you do work that doesn't need it:

async def process_user_order_fixed(session: AsyncSession, user_id: int):
    # First database operation — get what we need
    result = await session.execute(
        select(User).where(User.id == user_id)
    )
    user = result.scalar_one()
    user_id_copy = user.id  # Grab what we need from the ORM object

    # ✅ Explicitly close or commit early to return the connection
    await session.close()  # Returns connection to pool RIGHT HERE

    # Now do the slow external call — no DB connection held
    async with httpx.AsyncClient() as client:
        payment_data = await client.get(
            f"https://payments.example.com/user/{user_id_copy}/balance"
        )

    # Open a fresh session (gets a new connection) only when needed
    async with get_async_session() as new_session:
        await new_session.execute(
            update(Order).where(Order.user_id == user_id_copy).values(
                balance=payment_data.json()["balance"]
            )
        )
        await new_session.commit()

Yes, this means two separate connection checkouts instead of one continuous one. But each checkout is short and focused. The pool can breathe.

The JavaScript Version Is Just as Sneaky

Node.js developers aren't immune to this. The problem shows up just as readily in Express or Fastify apps using connection pools with pg or Knex. Here's a Node.js example showing the same trap:

const pool = require('./db'); // pg Pool with max: 10

// ❌ The problematic version
async function getUserReport(userId) {
  const client = await pool.connect(); // Pool connection checked out
  
  try {
    const userResult = await client.query(
      'SELECT * FROM users WHERE id = $1', [userId]
    );
    const user = userResult.rows[0];

    // Connection is held while we wait for this slow operation
    const reportData = await generateLargeReport(user); // Takes 2-3 seconds
    
    // Now use the connection again
    await client.query(
      'INSERT INTO reports (user_id, data) VALUES ($1, $2)',
      [userId, JSON.stringify(reportData)]
    );
    
    return reportData;
  } finally {
    client.release(); // Only released here, after all the slow work
  }
}

// ✅ The fixed version
async function getUserReportFixed(userId) {
  let user;

  // Scope 1: Quick DB read, connection returned immediately
  const client = await pool.connect();
  try {
    const userResult = await client.query(
      'SELECT * FROM users WHERE id = $1', [userId]
    );
    user = userResult.rows[0];
  } finally {
    client.release(); // Returned to pool before the slow work
  }

  // Slow work happens here, no connection held
  const reportData = await generateLargeReport(user);

  // Scope 2: Quick DB write, fresh connection, returned immediately
  const writeClient = await pool.connect();
  try {
    await writeClient.query(
      'INSERT INTO reports (user_id, data) VALUES ($1, $2)',
      [userId, JSON.stringify(reportData)]
    );
  } finally {
    writeClient.release();
  }

  return reportData;
}

The pattern is identical across languages. Async code makes it easy to span slow operations inside a single connection lifetime without realizing you're doing it.

Why This Doesn't Show Up in Testing

The reason this problem hides so well in development and staging environments comes down to concurrency. When you're testing locally, you're probably running a handful of requests at most. Your pool has 10 connections and you're using 2 of them. There's plenty of slack. Everything works fine.

But in production, you have real concurrent users. Imagine 50 users all triggering that orders endpoint at the same time. Even if your pool is sized to 20 connections, if each connection is held for an average of 800ms (including that payment API call), your throughput is severely bottlenecked. The math is unforgiving.

Load testing helps here, but only if you test with realistic concurrency and realistic external dependency latency. I've found that most load tests use mocked external services that respond in 5ms, which completely masks this class of problem. You need to inject realistic latency — or even occasional slowness — to expose connection starvation under load. Tools like Toxiproxy are great for this: they let you add artificial latency or packet loss to downstream services so your tests reveal what happens when the world isn't perfect.

The Transaction Trap Is Even Worse

There's a more severe version of this problem: holding a transaction open across async operations. A transaction not only holds a connection, it can also hold database-level locks — meaning other queries against the same rows will block, not just wait for a pool slot.

If your async code starts a transaction, does some work, awaits something slow, then tries to finish the transaction, you've created a landmine. Any other query touching those locked rows will hang. This can cascade into full table scans waiting on locks, which slows down completely unrelated operations, which holds their connections longer, which exhausts the pool faster.

The rule of thumb I follow is: transactions should be as short as possible, and they should never contain an await on anything that isn't a database operation. Do all your external calls, all your business logic, all your data preparation before you open a transaction. Then enter the transaction, do your writes, commit, and get out. Fast, focused, done.

Diagnosing the Problem in a Live System

If you suspect connection pool starvation is already happening in production, there are a few places to look. Most connection pool libraries expose metrics. For pg in Node.js, you can listen to events on the pool to track totalCount, idleCount, and waitingCount. When waitingCount is non-zero, requests are literally queuing for a connection. That's your smoking gun.

On the database side, PostgreSQL's pg_stat_activity view shows you every active connection and what state it's in. If you see a bunch of connections in the idle in transaction state — meaning a transaction is open but no query is actively running — that's a dead giveaway that code is holding connections open across slow async work. Run this query and see what comes back:

SELECT pid, state, wait_event_type, wait_event, 
       now() - query_start AS duration, query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND query_start < now() - interval '1 second'
ORDER BY duration DESC;

If you see rows here, especially ones that have been sitting for several seconds, you have async code that is leaking connection hold time. The query column will often give you a clue about which operation preceded the idle state, helping you trace back to the offending code.

Sizing the Pool Isn't the Answer

The obvious instinct when you hit connection pool exhaustion is to just increase the pool size. More connections means more capacity, right? This is a short-term fix that often makes things worse.

First, databases have their own connection limits and resource costs. PostgreSQL spawns a separate backend process per connection. Each one uses memory. Cranking your pool to 100 connections puts real pressure on the database server, and if your underlying queries are slow, you've just created 100 slow queries instead of 10.

Second, increasing the pool size just delays the problem. If your code is leaking connection time to slow external calls, a bigger pool will eventually starve too — it'll just take slightly more traffic to get there. You're treating the symptom, not the cause.

The real fix is always about reducing the time connections are held. Tighter scopes, smarter use of async boundaries, and explicit thinking about what work actually requires a database connection versus what doesn't. A connection should be checked out, used, and returned as quickly as possible. That's it. That's the whole philosophy.

Building Good Habits Around Connection Scope

Once you internalize the core problem, it changes how you think about structuring async functions. I've started thinking about every database operation as a critical section — a piece of code that should be as brief and focused as possible, with the resource released the moment it's no longer needed.

A useful mental model is to imagine a stopwatch that starts ticking the moment you check out a connection. Every millisecond on that stopwatch is borrowed time. Every await inside that scope is a risk. Ask yourself: does this await involve the database? If not, do it before you check out the connection or after you return it.

With this mindset, your async functions start to naturally decompose into clean phases: gather data from external sources, then open a short-lived database session to read, then close it, then do computation, then open another short-lived session to write. It feels like more code at first. But it's code that won't silently drain your pool at 3am on a Tuesday when traffic spikes and your payment provider has a slow night.