Oversized max_connections

Oversized max_connections
Type: incident simulation · Topic: Connections & Pooling · Level: L3 · Duration: 10–15 min
Launch: ride postgres start stage-03/10-oversized-max-connections

POSTMORTEM (root cause · how it was found · the fix · lesson)
Root cause: an earlier connection incident was 'fixed' by cranking
max_connections very high. Each connection costs backend memory (work_mem
allocations, per-backend overhead), so a high ceiling plus an app that opens
many direct connections trades connection errors for memory pressure and
scheduler overhead. The high limit was the problem, not the cure.

How it was found: SHOW max_connections was far above what the host can safely
back; pg_stat_activity showed one app holding many mostly-idle connections with
no pooling layer in front.

The mitigation: reduce the app's connection footprint (shed the excess
connections). The durable fix is a connection pool (PgBouncer / app-side) and a
bounded pool size — not a higher ceiling.

Lesson: raising max_connections is not a connection strategy; it just moves the
failure from 'too many clients' to memory exhaustion. Put a pool in front, bound
the app pool, and keep max_connections modest. Don't raise it further and don't
reach for indexes.

INVESTIGATION HINTS (the staged path to diagnose and fix)
1. max_connections has been cranked very high to 'fix' earlier connection errors, and the app keeps opening direct connections. Check it: SHOW max_connections; — it's oversized. Each backend costs memory, so this is the anti-pattern, not the fix.
2. Find the footprint: SELECT application_name, state, count(*) FROM pg_stat_activity GROUP BY application_name, state ORDER BY count(*) DESC; one app (app_pool) holds a large number of mostly-idle connections.
3. Reduce the app's connection footprint (terminate the excess app_pool connections); the durable fix is a connection pool and a sane app pool size. Do NOT raise max_connections further — more backends means more memory pressure, not more capacity.