You Gave an Agent Your Database via MCP, and Now the Connection Pool Is on Fire
The traffic graph is flat. Nobody deployed a schema change, nobody launched a
campaign, the request rate to your API looks exactly like it did last week. And
yet the database is throwing FATAL: sorry, too many clients already,
and pg_stat_activity is full of sessions parked in the
idle in transaction state. The one thing that changed: yesterday you
wired an AI agent to the database through an MCP server so it could answer
questions over your data.
This is becoming a common way to melt a perfectly healthy Postgres. The Model
Context Protocol makes it a few lines of config to hand an agent a
query tool backed by AlloyDB, Cloud SQL, or plain Postgres. What the
tutorial doesn't mention is that an LLM is a very strange database client, and
the connection pool you sized for a web app makes assumptions the agent quietly
violates.
I have seen this movie before, without the AI
Early in my career I ran a web API backend on GCP that behaved fine in staging and then, under real load, started slow and eventually began timing out requests — not at startup, but after it had been serving traffic for a while. Two things were wrong at once. The connection pool had been sized against an estimate of load rather than measured concurrency, so it ran out of connections under real traffic; and it was handing out stale, dead connections without validating them first, so some requests waited on a socket that was never going to answer. The fix was unglamorous: size the pool against concurrency proven by a load test, add connection validation and eviction so dead connections got cleared instead of served, and add a connect timeout so a request failed fast instead of hanging.
Every one of those failure modes is back with the agent, plus a new one that is specific to how LLMs work. Worth walking through, because the fixes are the same old database hygiene aimed at a new kind of caller.
Why an agent is a hostile database client
A normal request/response client borrows a connection, runs a query, and hands it back in milliseconds. The pool math works because the connection is checked out only for the duration of the query. An agent breaks that assumption in three ways.
It thinks between queries, and thinking is slow. An agent's
turn is: call the query tool, get rows back, then feed them to the
model, which spends two to ten seconds reasoning about what to ask next. If the
MCP server holds a connection — or worse, an open transaction — across that gap,
the connection sits idle in transaction for the entire inference step. The query
took 20ms; the connection was pinned for four seconds.
It fans out. Modern agents issue parallel tool calls, and you often run several agent sessions at once. Ten concurrent sessions each holding a connection through their think time is ten connections gone, none of them doing any work. A pool sized for a web app's actual query concurrency never planned for callers that hold connections while idle.
It writes queries you didn't. An agent can emit a query with
no LIMIT, a cartesian join, or a sequential scan over a table it
misjudged the size of. That query runs for minutes, holding its connection and a
chunk of your shared buffers the whole time.
Confirm it before you change anything
Don't guess at which failure mode you have. Postgres will tell you. This query shows what your sessions are actually doing right now, grouped by state:
SELECT state,
count(*),
max(now() - state_change) AS longest
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY state
ORDER BY count DESC;
If you see a wall of idle in transaction with a
longest of several seconds, that is the think-time problem: the MCP
server is holding transactions open across the model's reasoning steps. If you
see active sessions with a longest in the minutes, an
agent has launched a runaway query. Either way you now know which knob to reach
for instead of blindly bumping max_connections, which just moves the
cliff.
The fix, in layers
Put a transaction-mode pooler in front of the database
The single highest-leverage change is to stop letting the agent hold a real database connection during its think time. A transaction-mode pooler — PgBouncer, or AlloyDB's and Cloud SQL's managed poolers — only binds a server connection to a client for the duration of a transaction, then returns it to the pool. The agent can keep its client handle open across a five-second reasoning gap; behind the pooler, the actual Postgres connection was released the instant the query committed.
# pgbouncer.ini
[databases]
appdb = host=10.0.0.5 port=5432 dbname=appdb
[pgbouncer]
pool_mode = transaction ; NOT session — that defeats the whole point
max_client_conn = 1000 ; agents can hold many cheap client handles
default_pool_size = 25 ; but only this many real server connections
server_idle_timeout = 60
One caveat that bites people: transaction mode is incompatible with
session-level state — server-side prepared statements, SET that
outlives a transaction, session advisory locks. A read-only analytics agent
almost never needs those, so this is usually a clean win. Confirm your driver
isn't silently relying on prepared statements.
Give the database permission to defend itself
Assume the agent will eventually emit a pathological query, because it will. Bound the blast radius at the server, so no client-side politeness is required:
-- A role the MCP server connects as. Least privilege, self-limiting.
CREATE ROLE agent_ro LOGIN PASSWORD '...';
GRANT CONNECT ON DATABASE appdb TO agent_ro;
GRANT USAGE ON SCHEMA public TO agent_ro;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO agent_ro; -- read-only
ALTER ROLE agent_ro SET statement_timeout = '5s';
ALTER ROLE agent_ro SET idle_in_transaction_session_timeout = '10s';
ALTER ROLE agent_ro SET default_transaction_read_only = on;
statement_timeout kills the runaway join before it becomes an
incident. idle_in_transaction_session_timeout is the direct answer
to the think-time problem: if a transaction somehow stays open across the model's
reasoning, Postgres reclaims the connection instead of letting it rot. Scoping
these to the agent's role means a wandering agent can't degrade the human traffic
sharing the same instance.
Cap the agent's own fan-out
Finally, don't let a single agent run open unbounded parallel queries. If the MCP server exposes the query tool, gate it behind a semaphore so the number of in-flight queries can never exceed the pool you provisioned for it:
import asyncio, asyncpg
pool = await asyncpg.create_pool(
dsn, min_size=5, max_size=25,
max_inactive_connection_lifetime=30, # evict stale/dead connections
command_timeout=5, # client-side backstop
)
gate = asyncio.Semaphore(20) # never outrun default_pool_size
async def run_query(sql: str):
async with gate: # bound concurrency at the source
async with pool.acquire() as conn: # borrowed only for this block
return await conn.fetch(sql)
Two of these settings are the same lessons from that early-career incident,
transplanted: max_inactive_connection_lifetime is the connection
validation/eviction that stops dead connections being handed out, and
max_size is the pool sized against real concurrency rather than
optimism. The semaphore is the new piece — it exists because the caller is now a
thing that will happily ask for fifty connections at once if you let it.
The lesson
Handing an agent a database connection feels like a permissions decision, but
it's a capacity decision. An LLM is a client that holds resources during the long
gaps when it isn't querying at all, and that breaks the arithmetic every pool is
built on. None of the fixes are new — transaction-mode pooling, a
statement_timeout, a read-only role, eviction of stale connections,
a cap on concurrency. They're the same database hygiene we've always needed,
finally forced into the open by a caller impatient enough to expose every corner
we used to get away with cutting.
Hitting something like this in production? I help teams with performance engineering, SRE/observability, and AI-driven root cause analysis — work with me.
Comments
Post a Comment