Handling Pool Exhaustion Under Tenant Traffic Spikes

Pool exhaustion is the multi-tenant failure that hurts everyone at once: one tenant's spike consumes every backend, and unrelated tenants time out. This page is a focused part of Connection Pooling in Multi-Tenant Systems: capping per-tenant consumption, reserving headroom by tier, and shedding load deliberately rather than queueing into collapse.

Problem Framing

A connection pool is a shared, strictly bounded resource with no notion of fairness. When a tenant triggers ten thousand requests in a minute — a bulk import, an integration retry storm, a scheduled report across a large dataset — those requests take leases in arrival order and hold them for the duration of their queries. Every other tenant's request then waits behind them.

What makes it a cliff rather than a slope is queueing. As soon as demand exceeds pool size, latency stops degrading linearly: waiters accumulate, each holding memory and an in-flight request, and the queue grows faster than it drains. Clients time out and retry, which adds load, and the system arrives at congestive collapse — high resource use, near-zero useful throughput.

The three controls that prevent it are all about bounding, not about adding capacity. Per-tenant lease caps stop one tenant taking more than its share. Reserve pools keep headroom that only latency-sensitive tiers may draw on. Fail-fast checkout timeouts convert an unbounded queue into a fast, retriable error, which is what stops collapse.

Step-by-Step Guide

1. Cap leases per tenant before the pool is touched

// tenant-limiter.ts
const inflight = new Map<string, number>();

const CAPS: Record<Tier, number> = { free: 2, standard: 6, enterprise: 20 };

export async function withLease<T>(ctx: TenantCtx, fn: () => Promise<T>): Promise<T> {
  const cap = CAPS[ctx.tier];
  const current = inflight.get(ctx.tenantId) ?? 0;
  if (current >= cap) {
    metrics.increment('pool.tenant_cap_hit', { tier: ctx.tier });
    throw new HttpError(429, 'tenant concurrency limit reached', { retryAfter: 1 });
  }
  inflight.set(ctx.tenantId, current + 1);
  try {
    return await fn();
  } finally {
    const n = (inflight.get(ctx.tenantId) ?? 1) - 1;
    if (n <= 0) inflight.delete(ctx.tenantId); else inflight.set(ctx.tenantId, n);
  }
}

The cap has to be enforced before pool.connect(), not after. Once a request is waiting on the pool it already occupies the queue slot that the cap exists to protect.

2. Fail fast on checkout instead of queueing indefinitely

// pool.ts
export const pool = new Pool({
  max: 20,
  connectionTimeoutMillis: 250,      // fail fast: a 250ms wait means the pool is saturated
  idleTimeoutMillis: 10_000,
  statement_timeout: 5_000,          // no single query may hold a lease for long
  query_timeout: 5_000,
});

Two hundred and fifty milliseconds looks aggressive and is the point: if a connection is not available in that time, one will not be available soon either, and a fast 503 lets the client back off instead of holding a socket for thirty seconds.

3. Reserve headroom for tiers that must not be starved

; pgbouncer.ini — reserve pool covers the gap between median and peak demand
pool_mode = transaction
default_pool_size = 40
reserve_pool_size = 10
reserve_pool_timeout = 2        ; a waiter older than 2s may draw on the reserve
max_db_connections = 90
// tier-routing.ts — separate pools mean a free-tier storm cannot touch enterprise capacity
const pools = {
  free:       new Pool({ max: 8,  connectionTimeoutMillis: 150 }),
  standard:   new Pool({ max: 24, connectionTimeoutMillis: 250 }),
  enterprise: new Pool({ max: 24, connectionTimeoutMillis: 750 }),
};

Separate pools per tier are stronger than a shared pool with caps, because they bound the failure by construction rather than by bookkeeping that a bug can bypass.

4. Shed read traffic to replicas under pressure

// shed.ts
export async function runQuery<T>(ctx: TenantCtx, q: Query): Promise<T> {
  const saturated = pool.waitingCount > 0 || pool.idleCount === 0;
  if (saturated && q.readOnly) {
    return replicaPool.query<T>(q);            // same RLS scope, different backend set
  }
  if (saturated && ctx.tier === 'free' && q.deferrable) {
    throw new HttpError(503, 'busy — retry shortly', { retryAfter: 5 });
  }
  return pool.query<T>(q);
}

Replicas must apply the identical tenant scope. Routing a read to a replica that skips set_config would trade an availability problem for an isolation one.

5. Bound the queries themselves, so a lease cannot be held forever

-- Per-role defaults so a forgotten timeout in application code still cannot pin a backend.
ALTER ROLE tenant_app SET statement_timeout = '5s';
ALTER ROLE tenant_app SET idle_in_transaction_session_timeout = '10s';
ALTER ROLE tenant_app SET lock_timeout = '2s';

-- Reporting workloads get their own role, their own pool and a longer leash.
ALTER ROLE tenant_report SET statement_timeout = '120s';

idle_in_transaction_session_timeout is the one that saves you at 3am: a transaction left open by a crashed worker holds its lease and its locks indefinitely, and every other tenant queues behind it.

Verification

-- PgBouncer admin console: waiting clients are the leading indicator.
SHOW POOLS;
--  database | user       | cl_active | cl_waiting | sv_active | sv_idle | maxwait
--  saas     | tenant_app |       118 |          0 |        38 |       2 |       0
-- cl_waiting above zero for more than a few seconds is the alert, not maxwait growing.

-- Which tenants are actually consuming the backends right now?
SELECT current_setting('app.current_tenant_id', true) AS tenant, count(*) AS backends,
       max(now() - query_start) AS longest
FROM   pg_stat_activity
WHERE  state <> 'idle' AND datname = 'saas'
GROUP  BY 1 ORDER BY 2 DESC LIMIT 10;
// exhaustion.spec.ts — prove the cap protects the neighbour, not just the counter
it('keeps a second tenant responsive during a first tenant burst', async () => {
  const burst = Array.from({ length: 500 }, () => asTenant('noisy').get('/api/report'));
  const quiet = await asTenant('quiet').get('/api/me');           // during the burst
  expect(quiet.status).toBe(200);
  expect(quiet.duration).toBeLessThan(300);
  const results = await Promise.allSettled(burst);
  const throttled = results.filter((r) => r.status === 'fulfilled' && r.value.status === 429);
  expect(throttled.length).toBeGreaterThan(0);                    // the cap did engage
});

That test is the one worth keeping in CI. It asserts the property that matters — a neighbour stays fast — rather than the mechanism, so it keeps passing across implementation changes.

Failure Modes & Gotchas

FAQ

What is the right per-tenant cap? Start from the pool: a cap of roughly ten to twenty percent of pool size for the top tier, and low single digits for free tenants. The precise number matters less than its existence — the goal is that no tenant can take a majority of the pool, not that the value is optimal. Derive it from observed per-tenant concurrency at the ninety-fifth percentile and revisit it when tiers change.

Should exhaustion return 429 or 503? 429 when a tenant hit its own cap, because the client's own behaviour caused it and backing off will help. 503 when the shared pool is saturated for reasons outside that tenant's control, because it signals a server-side condition. Both should carry Retry-After, and both should be retried with jitter — synchronised retries are how a brief saturation becomes a sustained one.

Does a pooler like PgBouncer remove the need for these controls? It raises the ceiling and does not change the shape. Transaction pooling multiplexes many clients onto few backends, which absorbs far more concurrency, but the backend count is still finite and a single tenant can still consume it. The pooler's reserve_pool and max_db_connections are exactly the same class of control, applied one layer down; the sizing method behind both is in sizing connection pools per tenant tier.

Should retries be handled by the client or by the server? By the client, with jitter, and the server's job is to say so clearly. A Retry-After header and a distinguishable status code let a well-behaved client back off correctly; server-side retries simply hold the request open and consume the resource the shedding was meant to protect. The one server-side exception worth making is a short internal retry on a transient connection error, bounded to a single attempt, because that failure is genuinely different from saturation.

How does this interact with autoscaling the application tier? Badly, unless the pool size is derived rather than fixed. Scaling out multiplies pool size by instance count, so an autoscaling event triggered by high latency can add exactly the load that caused it — more instances, more pools, more contention for the same backends. Derive the per-instance pool size from a total budget divided by the current instance count, or put a pooler in front so instance count stops determining backend count at all.