investigate: root cause of Postgres TooManyConnectionsError driving pool exhaustion

[amrtgaber]

Background

On 2026-04-18 a large volume of asyncpg.exceptions.TooManyConnectionsError events burned through Sentry's monthly quota. The error text:

remaining connection slots are reserved for roles with privileges of the "pg_use_reserved_connections" role

indicates Postgres's instance-level max_connections was exhausted. The failing call site is asyncpg/connect_utils.py __connect_addr — the error occurs when the app tries to establish a new connection, not at checkout time from the SQLAlchemy pool. One representative event: /v1/keys transaction, IP 3.20.233.150.

What has already shipped (mitigations, not root-cause fixes)

#77 added:

• pool_pre_ping=True + pool_recycle=1800 in app/database.py — makes transient dead-connection issues self-heal instead of surfacing as errors
• A before_send rate limiter in app/observability/sentry.py — caps reporting at 5/60s/process (tracked for removal separately)

These address resilience and cost control. Neither answers why the connection cap is being hit. Pool capacity was intentionally left at defaults (pool_size=5 + max_overflow=10 = 15 max connections per process).

Hypotheses to investigate

1. Aggregate demand > Cloud SQL max_connections

If Cloud Run runs N instances at peak, aggregate connections = up to 15×N. Cloud SQL max_connections depends on tier; small tiers (db-f1-micro, db-g1-small) are 25–50. On a small tier, 3–4 instances is enough to hit the cap.

• Check Cloud SQL instance tier and max_connections setting in the GCP console
• Check Cloud Run max instance count during the storm window
• Compare 15×peak_instances against max_connections

2. Connection leak in request handlers

The canonical pattern is FastAPI's get_async_session dependency (app/database.py:32-35) which uses async with — correct. But any code creating sessions outside this dependency chain is suspect, and leaked sessions tie up pool slots indefinitely.

• Grep for async_session_maker() / AsyncSession( calls outside app/database.py and the scripts
• Verify all background-task / lifespan / middleware session usage releases properly

3. Long-running queries holding connections

/v1/loadout is the heaviest endpoint. Long queries there hold a pool slot, forcing other requests to overflow into new connections.

• Cloud SQL Insights: p95/p99 query duration by endpoint, correlated with storm onset
• Check for missing indexes on loadout-relevant joins

4. Connection churn from serverless cold starts

Cloud Run scale-up spins new instances; each warms its own pool. If traffic bursts cause rapid scale-up, aggregate connection creation rate can momentarily exceed Postgres's accept rate.

• Correlate storm windows against Cloud Run scaling events

Suggested investigation order

1. Pull Cloud SQL tier and max_connections — cheapest signal, likely decisive
2. Pull Cloud Run max instance count during the storm
3. If the cap is tight: reduce max_overflow in app/database.py, upgrade the SQL tier, or introduce pgbouncer
4. Audit session usage for leaks (step 2 of hypotheses)
5. After fixing: remove the Sentry rate limiter (companion issue)

Data worth pulling before anyone debugs

• Cloud SQL instance tier + current max_connections
• Cloud Run max instance count during storm window
• Cloud SQL Insights: active connection count over time around 2026-04-18
• Sentry: top endpoints by error count during the storm window

Why this was not resolved inline with the quota-relief PR

The quota-relief PR was scoped to stop the bleed (Sentry cost + connection resilience). Root-cause analysis needs live GCP data (instance tier, connection metrics, scaling history) that wasn't accessible during that work.