Your stateless service is lying: it's the connection pool

The Tuesday revenue report rendered another tenant's numbers. Same code path, same container, same service everyone on the team calls stateless. The next deploy made it vanish, which is why it took me three weeks to believe it was real.

It was real. The service keeps nothing in memory, but it borrows connections from a pool that lives as long as the process. Whatever one request leaves on a connection is inherited by the next request that gets it.

Ours was a session-level SET. One endpoint tagged its connection with the current tenant so a row-level security policy would filter rows:

cur.execute("SET app.tenant_id = %s", (tenant_id,))
rows = cur.execute("SELECT * FROM revenue").fetchall()
conn.commit()
# connection returns to the pool, still tagged

SET lasts for the session. The connection went back to the pool carrying tenant A's tag, and the next request — tenant B — ran its query under tenant A's policy. The fix is one argument:

cur.execute("SELECT set_config('app.tenant_id', %s, true)", (tenant_id,))

That third argument makes the setting transaction-local. If the transaction rolls back, the setting rolls back with it.

The other ways a connection leaks state

It is tempting to call the tenant bleed the loud one and the rest quieter. The opposite is true, and the distinction matters more than it looks.

An unset app.tenant_id is loud. The policy has nothing to compare against, the query comes back empty or errors, and somebody files a bug inside the hour. A stale tag is a well-formed value belonging to a real tenant, so every layer beneath it behaves correctly on the wrong input and nothing anywhere has a reason to complain. That is why this took three weeks instead of an afternoon, and it is a property of carrying an authorization decision as a string on a shared resource rather than of SET.

The failure modes below fail loudly by comparison.

A transaction left open. A code path that raises between BEGIN and COMMIT hands back an idle-in-transaction backend. It keeps holding locks and a snapshot, and it shows up in pg_stat_activity long after the request ended. A helper function with an early return did this to us for weeks.

A prepared statement collision. Drivers that cache prepared statements per connection (asyncpg, pgjdbc) generate names like _pg3. Two code paths sending different SQL under the same generated name on one pooled connection give you prepared statement "..." already exists, or silent reuse of the wrong plan.

A failed transaction. In Postgres one error aborts the whole transaction, and every statement after it fails with current transaction is aborted. Catch the exception and keep going, and you are holding a poisoned connection until someone returns it.

Pool exhaustion from one slow query. This leaks scarcity instead of data. A query missing an index holds every slot for its duration, every other request waits on the pool, and your latency climbs while the database itself sits mostly idle.

How to see it

Pool wait time. Most pools expose it. HikariCP has connection-acquire timeouts, asyncpg has pool.get_size() and get_idle_size() plus per-acquire wait, SQLAlchemy has pool.checkedout() and the pool checkout event. If acquire latency climbs while query latency stays flat, you are short on connections, not slow on queries.

pg_stat_activity, grouped by state:

SELECT state, count(*), max(now() - state_change) AS longest
FROM pg_stat_activity
WHERE datname = current_database()
GROUP BY state;

An idle in transaction row with a longest measured in minutes is a leaked transaction, not a busy one. Measure that gap; do not guess it.

Assertions on check-in, not checkout. I had this backwards in the first version of this post, and a commenter put it better than I managed: checkout is detection, check-in is attribution.

A dirty connection found at checkout tells you the pool is contaminated and gives you no way to find out who contaminated it. The request that did it finished minutes ago, and the one now holding the evidence is innocent. Asserting on return fails the request that actually left the state behind, in its own stack, while the guilty code path is still on it:

conn = pool.acquire()
try:
    yield conn
finally:
    still_set = conn.execute(
        "SELECT current_setting('app.tenant_id', true)"
    ).scalar()
    assert not still_set, "connection returned with session state still set"
    pool.release(conn)

Both if you can. If you only build one, build the one that names the bug.

Design so the state cannot leak

Return connections from one place. A context manager, defer, a finally block — one exit path that commits or rolls back. Never return a connection from inside an exception handler.

Reset the things you set. DISCARD ALL restores a session to defaults, but it is expensive and cannot run inside a transaction. Resetting the two settings you actually touched is cheaper and more precise.

Prefer SET LOCAL, or set_config(..., true), over SET. Scope role changes to the transaction the same way. If session-level state is unavoidable, clear it in the same block that set it.

Size the pool against the server. Postgres defaults to max_connections = 100, and every replica multiplies its pool size by the process count. Twenty pods with a pool of 10 ask for 200 connections before migrations or admin sessions. Budget it as (max_connections - reserved) / replicas, then measure the acquire wait under load and adjust.

That formula is a steady-state answer, and steady state is not the moment you care about. A rolling deploy runs old and new pods at the same time, so replicas is a floor rather than a ceiling, and the surge arrives exactly when the new pods are opening fresh pools against a database still serving the old ones. Budget for the deploy, not for the calm afternoon.

None of this makes a service stateless. It makes the state explicit and short-lived, which is the property I actually wanted.

Thanks to @_firelinks for all three corrections. The check-in point is a real error in the first version of this post, and the post is better for it.

I write about production failures in Postgres, queues, and distributed systems.

Subscribe by email · RSS · Bluesky