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

Tenant bleed is the loud version. These are quieter.

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 checkout. In staging, log what the connection looks like before you use it: SELECT current_setting('app.tenant_id', true) plus the driver's transaction status. If a fresh checkout is dirty, the return path is wrong, and now you have a failing test that proves it.

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.

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

---

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

Subscribe by email · RSS · Bluesky

Comments

[Mike Dabydeen]

The transaction-local fix is the right one, and I would put a layer under it, because the two failure modes in your post are not the same shape and only one of them is loud.

An unset app.tenant_id fails visibly. The policy has nothing to compare against, the query comes back empty, and somebody files a bug inside the hour. A stale one 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 most of your three weeks, and it is a property of the design rather than of SET. Anything that carries an authorization decision as a string on a shared resource has it. The cheap mitigation is that the tenant predicate also appears in the query itself, with the policy as the second line rather than the only one. Redundant on every request that was already correct, which is not the request you are buying it for.

On the staging assertion, I would move it from checkout to check-in. A dirty connection caught at checkout tells you the pool is contaminated and gives you no way to find out who contaminated it, because 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. Checkout is detection, check-in is attribution. Both if you can, and if you only build one, build the one that names the bug.

On sizing, (max_connections - reserved) / replicas gives you the steady state, and the moment you care about is not steady state. A rolling deploy runs old and new pods at the same time, so replicas in that formula is a floor and not a ceiling, and the surge arrives exactly when the new pods are opening fresh pools against a database still serving the old ones. It is the same arithmetic as your point about each replica multiplying its pool by process count, one deploy later.

[晖莫]

You have changed my mind on the staging assertion, and I am going to reorder the post because of it. "Checkout is detection, check-in is attribution" is the whole argument in six words, and I had it backwards. A dirty connection found at checkout has already outlived its culprit — the request holding the evidence is innocent. Asserting on return fails the request that left the state behind, in its own stack, which is the only place the bug is still reachable.

On the two failure modes: agreed, and I under-sold it. Unset is loud because the policy has nothing to compare against. Stale is well-formed, belongs to a real tenant, and every layer beneath it behaves correctly on the wrong input, so nothing anywhere has a reason to complain. That asymmetry is most of your three weeks, and it is a property of carrying an authorization decision as a string on a shared resource rather than of SET.

The redundant tenant predicate is the part I would now write down explicitly: the query carries the predicate, and the policy is the second line rather than the only one. Dead weight on every request that was already correct — which is exactly the point, because those are not the requests you are buying it for.

On sizing: rolling deploy is the case I did not carry through. (max_connections - reserved) / replicas is a steady-state answer, and replicas is a floor rather than a ceiling during a deploy, because old and new pods hold pools at the same time and the surge lands exactly when the new pods are opening fresh connections. Same arithmetic as the per-replica multiplication, one deploy later.