Timed Out Fetching a New Connection From the Pool: Why Raising the Limit Makes It Worse

Databases · Intermediate · 6 min read · published

This article was written by Claude (Anthropic) and published automatically.

What this solves: Your app throws pool timeout errors under load while Postgres sits at 20% CPU. Learn why the queue is in your app, not the database, and how to fix it.

The Problem

At 200 requests/second your API starts returning 500s and the logs fill with Timed out fetching a new connection from the connection pool (pool_timeout=10, connection_limit=10). Meanwhile Postgres is bored: 12% CPU, 40 of 200 max_connections in use, and the slowest query in pg_stat_statements is 6ms.

So you have a queue somewhere that Postgres knows nothing about. That message does not come from the database — it comes from your driver's client-side pool, and it means: ten seconds passed and not one of my 10 connections came back. With 6ms queries, ten connections should serve ~1,600 queries per second. Something is holding connections while doing something that isn't a query.

Why the Obvious Fix Falls Short

The error text names connection_limit, so the reflex is to raise it. connection_limit=10 becomes 50, and pool_timeout goes from 10s to 30s. It works in staging with one instance. In production it makes things strictly worse, for three compounding reasons.

1. You multiply by replica count. 40 containers × 50 = 2,000 connections at a server with max_connections=200. Your soft client-side queue becomes hard FATAL: sorry, too many clients already errors, and those hit health checks and migrations too.

2. Each Postgres connection is a process. A backend costs ~5–10MB of base memory plus up to work_mem per sort node. Going from 200 to 2,000 backends doesn't add throughput past the point where CPU and disk saturate; it adds context switching and lock contention. Throughput curves over-connection-count are humped, not monotonic — beyond roughly cores × 2 + effective_spindle_count you go backwards.

3. A longer pool_timeout converts fast failures into slow ones. If checkout latency is 9s, a 30s timeout means requests now sit for 30 seconds behind your load balancer's 30s idle timeout, so clients see 504s and retry, which adds load. The queue grows without bound — classic bufferbloat.

The deeper issue: pool exhaustion is almost never a capacity problem. It's a hold-time problem. Little's Law: concurrent connections needed = arrival rate × hold time. You can only fix it by attacking hold time or arrival rate, and raising the limit attacks neither.

How It Actually Works

There are two independent queues, and confusing them is the whole bug.

flowchart TD
    R[Incoming request] --> Q{Free connection<br/>in client pool?}
    Q -- yes --> C[Checkout connection]
    Q -- no --> W[Wait in app-side queue]
    W -- pool_timeout expires --> E["Timed out fetching a new<br/>connection from the pool"]
    W -- slot frees --> C
    C --> T[BEGIN]
    T --> S1[SELECT ... 6ms]
    S1 --> X["await paymentApi.charge()<br/>800ms — connection still held,<br/>Postgres shows 'idle in transaction'"]
    X --> S2[UPDATE ... 4ms]
    S2 --> CM[COMMIT + release to pool]
    CM --> Q
    style X fill:#f9d0d0,stroke:#c00
    style E fill:#f9d0d0,stroke:#c00

The metric that matters is checkout duration, not query duration. If queries take 10ms but checkout takes 850ms, 98% of the time a connection is held it is doing nothing. Ten connections then deliver ~12 ops/sec, not 1,600.

Common hold-time inflators, in the order you should hunt for them:

Diagnosis, in order:

-- 1. Are connections held between statements?
SELECT state, count(*), max(now() - xact_start) AS oldest_xact
FROM pg_stat_activity WHERE datname = current_database()
GROUP BY state;
-- Lots of 'idle in transaction' => app-side hold, not DB slowness.

-- 2. Or are they genuinely waiting on locks?
SELECT wait_event_type, wait_event, count(*)
FROM pg_stat_activity WHERE state = 'active' GROUP BY 1,2;

Then add the guardrails: a server-side idle_in_transaction_session_timeout so a leak fails loudly, and a transaction-mode pooler (PgBouncer, RDS Proxy, Supavisor) if you have many short-lived instances. Transaction mode lets 500 client connections share 20 server backends, because a backend is only bound to a client for the duration of a transaction — which only helps if your transactions are short, so hold time still has to be fixed first.

Before and After

// BEFORE — connection held for ~850ms per request
// The HTTP call sits inside the transaction, so the pooled connection
// is 'idle in transaction' for 800 of those 850ms.
await prisma.$transaction(async (tx) => {
  const order = await tx.order.findUniqueOrThrow({ where: { id } }); // 6ms
  const receipt = await payments.charge(order.total);                // 800ms  <-- holds connection
  await tx.order.update({
    where: { id },
    data: { status: 'PAID', receiptId: receipt.id },                 // 4ms
  });
});
// 10 connections / 0.85s hold = ~12 orders/sec before pool timeouts.

// AFTER — connection held ~10ms per request; limit untouched at 10
// 1. Read and charge outside any transaction.
// 2. Transaction wraps only the writes that must be atomic.
// 3. Idempotency key makes the retry-after-charge case safe,
//    which is what the long transaction was really protecting against.
const order = await prisma.order.findUniqueOrThrow({ where: { id } });

const receipt = await payments.charge(order.total, {
  idempotencyKey: `order-${order.id}-v${order.version}`, // no DB connection held
});

await prisma.$transaction(async (tx) => {
  const { count } = await tx.order.updateMany({
    where: { id, version: order.version },        // optimistic concurrency
    data: { status: 'PAID', receiptId: receipt.id, version: order.version + 1 },
  });
  if (count === 0) throw new ConflictError('order changed concurrently');
});
// ~10ms hold => the same 10 connections now sustain ~1,000 orders/sec.

Also worth setting, once hold time is fixed:

# Small pool per instance + pooler absorbs the fan-in.
DATABASE_URL="postgresql://...:6432/app?pgbouncer=true&connection_limit=5&pool_timeout=3"

ALTER ROLE app_worker SET idle_in_transaction_session_timeout = '5s';

When NOT to Use This

Gotchas

Key takeaway: A pool timeout means connections are held too long, not that the pool is too small — measure checkout duration before you touch the limit.

Real-world challenge

A nightly reconciliation job starts at 02:00. Within a minute, your customer-facing API begins returning 500s with 'Timed out fetching a new connection from the connection pool (pool_timeout=10)'. Postgres CPU is 15%, active backends are 60 out of max_connections=200, and pg_stat_activity shows many rows in state 'idle in transaction' lasting 3–9 seconds. The batch job processes 50k records. How do you diagnose and fix it?

Diagnose

  1. idle in transaction for seconds is the smoking gun: the batch opened transactions and is doing non-DB work inside them.
  2. Confirm which statement is stuck:
SELECT pid, state, now() - xact_start AS xact_age, query
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY xact_age DESC LIMIT 10;
  1. If the top rows show a SELECT that already returned plus a long xact_age, the app is holding the connection between statements — almost always an external call (payment API, S3, another service) inside the transaction block.
  2. Note that API and batch share the same pool. 60 active backends against max_connections=200 proves Postgres is not the bottleneck; the queue is in the app.

Fix

Verify: checkout-duration p99 should drop to roughly query duration, and pool timeouts disappear without changing the pool size.