Connection Pooling for AI Workloads

A web request that takes 40 ms and a web request that takes 40 seconds need the same number of database connections, because the database work in both is the same 5 ms. Pool sizing intuition says otherwise, which is how applications end up with two hundred connections, an exhausted Postgres, and the wrong conclusion that they need a bigger database.

What breaks

The pattern is nearly universal in code written before anyone thought about it: acquire a connection at the top of the handler, use it, continue to use the surrounding scope, release at the end.

# The bug. Reads as ordinary, careful code.
async def answer(question, tenant):
    async with pool.acquire() as conn:                 # connection checked out
        chunks = await conn.fetch(RETRIEVAL_SQL, tenant, embed(question))
        reply  = await model.complete(prompt(chunks))  # 8–40 seconds
        await conn.execute(LOG_SQL, tenant, reply.usage)
    return reply                                       # released, at last

The connection is held for the whole model call. Two queries totalling five milliseconds hold a connection for thirty seconds, so the pool must be as large as your concurrency, and concurrency for an AI application is high precisely because each request is slow.

What follows is not gradual. Postgres max_connections defaults to 100; each connection is an operating system process with several megabytes of private memory; and throughput against a Postgres instance peaks at a small multiple of its core count and then declines as more backends contend for locks and buffers. So the pool fills, then the application blocks waiting for the pool, then requests time out waiting for the block, and the errors are:

FATAL:  sorry, too many clients already
TimeoutError: connection acquisition timed out after 30.0s
FATAL:  remaining connection slots are reserved for
        non-replication superuser connections

The last one is the nastiest, because it means you also cannot connect to diagnose the problem.

The right pool size, derived

Little’s law: the average number of items in a system equals the arrival rate multiplied by the average time each spends there. Applied to database connections, the items are queries and the time is the database service time, not the request duration.

connections_needed = arrival_rate × db_service_time

WORKED, a busy AI application:
  200 concurrent in-flight requests
  each request: 30 s total, of which 5 ms is database work
  arrival rate = 200 / 30 s = 6.7 requests per second
  db work per request = 2 queries × 2.5 ms = 5 ms

  connections = 6.7 × 0.005 = 0.033

So the steady-state demand is a thirtieth of one connection.
Round up for burstiness — arrivals are not uniform — and for
the occasional slow query, and a pool of 10 is generous.

Compare with holding the connection across the model call:
  connections = 6.7 × 30 = 200.

A factor of six thousand. That is the whole of this page in one calculation, and it is why the fix is architectural rather than a matter of raising a limit.

Two adjustments make the estimate honest. Arrivals are bursty, so size for the peak second rather than the average — if traffic arrives in bursts of fifty, you need enough connections to absorb fifty × service time without queueing badly. And service time is not uniform; use your p95 database query time, not the mean, for the same reason described in measuring p50, p95 and p99.

pool_size = ceil(peak_arrival_rate × p95_db_service_time × safety)

  peak 50 req/s × 0.008 s p95 × 3 = 1.2  ->  round up

Then apply the floor that matters more:
  pool_size ≥ 2 × application_worker_threads_that_can_block
  and         ≤ (max_connections − reserved) / number_of_app_instances

Most AI applications land between 5 and 20 per instance.
If your answer is over 50, the model call is inside the
checkout, and no arithmetic fixes that.

The one rule that matters

Never hold a database connection across a network call to anything else. Every other recommendation on this page is a consequence or a mitigation.

# The fix: three short checkouts instead of one long one.
async def answer(question, tenant):
    qvec = embed(question)

    async with pool.acquire() as conn:                # ~3 ms
        chunks = await conn.fetch(RETRIEVAL_SQL, tenant, qvec)

    reply = await model.complete(prompt(chunks))      # no connection held

    async with pool.acquire() as conn:                # ~2 ms
        await conn.execute(LOG_SQL, tenant, reply.usage)

    return reply

The objection is that the two queries are no longer in one transaction. Examine whether they ever needed to be: a retrieval and a telemetry write have no invariant between them, and wrapping them in a transaction was incidental to how the code was written rather than a decision anybody made. Where an invariant genuinely exists, it is almost always between two database operations, which can still share a short transaction that does not span the model call.

• Do the embedding call before the checkout. It is another network call, typically 50–200 ms, and it is easy to leave inside the block by accident.
• Streaming responses are the worst case. A handler that streams tokens to a browser may live for minutes. If it holds a connection to write progress, you have the original bug with a longer fuse. Buffer the writes and flush at the end, or take a connection per flush.
• Background jobs need their own pool. A batch re-embedding job and the interactive path competing for one pool means the batch job causes user-visible timeouts. Separate pools, separate limits, and give the interactive one priority.

PgBouncer, and what transaction mode costs

A connection pooler in front of Postgres multiplexes many client connections onto few server connections. In transaction mode a server connection is assigned for the duration of a transaction and returned immediately after, which is what makes a thousand clients viable on twenty backends.

[databases]
app = host=127.0.0.1 port=5432 dbname=app

[pgbouncer]
pool_mode = transaction
max_client_conn = 2000
default_pool_size = 20
reserve_pool_size = 5
server_idle_timeout = 60
max_prepared_statements = 200   ; PgBouncer 1.21+, for protocol-level prepares

Transaction mode takes things away, and every one of them has broken somebody’s application after the pooler was introduced as a performance change:

The first row is the dangerous one and it connects directly to row-level security for multi-tenant retrieval: SET app.tenant_id under a transaction-mode pooler hands your tenant identity to the next customer’s request. SET LOCAL app.tenant_id does not. One word.

Timeouts, in three layers

Pool exhaustion is usually a symptom of something else running long. Three timeouts bound it, and they must be ordered so the innermost fires first — otherwise the outer one fires and leaves the inner operation running.

-- 1. On the server, per role. A query that cannot finish in 15 s
--    against a retrieval workload is a bug, not a slow day.
ALTER ROLE app_user SET statement_timeout = '15s';

-- 2. Kill sessions idle inside an open transaction. This is the one
--    that saves you from a client that crashed mid-transaction while
--    holding locks and a connection.
ALTER ROLE app_user SET idle_in_transaction_session_timeout = '30s';

-- 3. And the lock timeout, so DDL and heavy statements fail rather
--    than queue every other query behind them.
ALTER ROLE app_user SET lock_timeout = '3s';

In the client, the acquisition timeout should be short — one or two seconds. Waiting thirty seconds for a connection converts a capacity problem into a latency problem and then into a cascade. Failing fast lets a circuit breaker or a load shedder do its job while the system is still recoverable.

-- What is actually holding connections, right now.
SELECT state, wait_event_type, wait_event, count(*),
       max(now() - state_change) AS longest
FROM pg_stat_activity
WHERE datname = current_database()
GROUP BY 1,2,3
ORDER BY count(*) DESC;

-- The specific query, if 'idle in transaction' is high:
SELECT pid, now() - xact_start AS open_for, query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY xact_start;

A large idle in transaction count with a long open_for is the signature of the bug at the top of this page: transactions open across a model call, doing nothing, holding everything.

Serverless and connection storms

Serverless functions break pooling by design: each instance has its own pool, instance count scales with traffic, and the pools are not coordinated. A hundred concurrent invocations with a modest pool of ten each is a thousand connection attempts against a database configured for a hundred.

1. Pool size 1 per instance. A serverless invocation usually handles one request; more than one connection per instance is pure multiplication. This is counter-intuitive and correct.
2. Put a pooler in the middle, always. PgBouncer, a managed data proxy, or your provider’s equivalent. The whole point is to have one place where the total is bounded.
3. Reuse the connection across invocations. Create it outside the handler so it survives on a warm instance rather than being made and torn down per request — a Postgres connection setup is a process fork and a TLS handshake, which is tens of milliseconds you pay on every cold call.
4. Consider an HTTP data API if your provider offers one. It gives up transactions and session state, which the rule earlier on this page says you should not have been relying on across a model call anyway.

All of this depends on knowing your database service time, and most teams do not. It is the one real input to the sizing formula and it is measurable in five minutes with the statement statistics extension, which is already available on most managed Postgres instances.

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Where the database time goes, and how much of it there is.
SELECT round(total_exec_time)::bigint      AS total_ms,
       calls,
       round(mean_exec_time::numeric, 2)   AS mean_ms,
       round(stddev_exec_time::numeric, 2) AS stddev_ms,
       left(query, 70)                     AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 15;

-- The number the formula wants. Total database busy time divided by
-- the observation window IS the average number of connections in use.
SELECT round(sum(total_exec_time) / 1000 / 3600, 3) AS avg_connections_busy
FROM pg_stat_statements;   -- after a reset and one representative hour

That second query is Little’s law computed for you: busy milliseconds over the window, divided by the window, is the average number of connections doing work. If it returns 0.4 and your pool is 100, you have your answer, and it is not that the database needs to be larger.

Two caveats before shrinking anything on that basis. The statistics are cumulative since the last reset, so call pg_stat_statements_reset() and observe a representative hour rather than a figure that averages in a quiet weekend. And a mean hides the burst — pair it with the p95 from your application-side timing, because the pool has to survive the worst second rather than the average one.

A smaller pool is not merely adequate here; it is better. Postgres throughput against a fixed number of cores rises with concurrency to a point and then falls, as backends contend for lightweight locks, buffer mapping and the same cache lines. A pool of ten servicing a queue is faster in aggregate than a pool of two hundred all making progress slowly, and it fails more legibly — a full pool is a queue you can measure, while two hundred contending backends look like a database that has mysteriously become slow.