db: raise max_connections to 200, cap work_mem at 64MB, trim async pool overflow #124

Merged

admin_emi merged 3 commits from fix/db-connection-headroom into dev

2026-07-26 19:48:51 +00:00

admin_emi commented

2026-07-26 19:43:28 +00:00

Owner

Prod's Postgres saturated its connection ceiling on 2026-07-26 (~14:28–14:35 UTC), refusing clients with FATAL: sorry, too many clients already and stalling the subscription notifier.

The ceiling was never configured — it was the TimescaleDB image default of 100. One shared instance serves both prod and beta (separate databases/roles, not separate containers), and every backend process opens three pools, so the configured worst case is ~170 today and ~240 if web autoscales to its max of 3 replicas. Nothing has to misbehave for that to blow up.

Three changes, all in one go so the autoscale-max case actually fits rather than the cliff just moving:

1. max_connections = 200 (infra/deploy/db/init/20-tuning.sh). Restart-only — this covers a fresh volume; the live instance needs the same ALTER SYSTEM applied by hand plus a db restart. ~+1 GB of per-connection overhead against a 16 GB container cap and 40 GB free on the box.

2. work_mem capped at 64 MB (same file). The existing DB_MEMORY/128 derivation yields 128 MB at prod's 16g budget. That is the sharp edge at 200 connections: work_mem is per sort operation, and a burst of heavy sorts across many backends can walk into the container's hard 16 GB limit. An OOM-killed Postgres is a much worse day than a refused connection. Dev (8g) is unchanged at 64 MB — only prod moves.

3. max_overflow 5 → 2 on the async engines (backend/accounts/db.py). The structural fix, and the cheap one: rolling deploy, no database restart. Takes prod web's configured worst case from 4x20 to 4x14 (56), so the headroom bought above is not immediately spent.

pool_size is untouched at 5, so steady-state behaviour is identical — only the burst ceiling narrows. The sync notifier pool (2+3) is left alone: it is one process cluster-wide.

Follow-up not in this PR: applying the live ALTER SYSTEM SET max_connections = 200 + db restart, which is a few seconds of downtime for both prod and beta and wants a quiet window.

Prod's Postgres saturated its connection ceiling on 2026-07-26 (~14:28–14:35 UTC), refusing clients with `FATAL: sorry, too many clients already` and stalling the subscription notifier. The ceiling was never configured — it was the TimescaleDB image default of 100. One shared instance serves both prod and beta (separate databases/roles, not separate containers), and every backend process opens three pools, so the configured worst case is ~170 today and ~240 if web autoscales to its max of 3 replicas. Nothing has to misbehave for that to blow up. Three changes, all in one go so the autoscale-max case actually fits rather than the cliff just moving: **1. `max_connections = 200`** (`infra/deploy/db/init/20-tuning.sh`). Restart-only — this covers a fresh volume; the live instance needs the same `ALTER SYSTEM` applied by hand plus a db restart. ~+1 GB of per-connection overhead against a 16 GB container cap and 40 GB free on the box. **2. `work_mem` capped at 64 MB** (same file). The existing `DB_MEMORY/128` derivation yields 128 MB at prod's 16g budget. That is the sharp edge at 200 connections: work_mem is *per sort operation*, and a burst of heavy sorts across many backends can walk into the container's hard 16 GB limit. An OOM-killed Postgres is a much worse day than a refused connection. Dev (8g) is unchanged at 64 MB — only prod moves. **3. `max_overflow` 5 → 2 on the async engines** (`backend/accounts/db.py`). The structural fix, and the cheap one: rolling deploy, no database restart. Takes prod web's configured worst case from 4x20 to 4x14 (56), so the headroom bought above is not immediately spent. `pool_size` is untouched at 5, so steady-state behaviour is identical — only the burst ceiling narrows. The sync notifier pool (2+3) is left alone: it is one process cluster-wide. **Follow-up not in this PR:** applying the live `ALTER SYSTEM SET max_connections = 200` + db restart, which is a few seconds of downtime for both prod and beta and wants a quiet window.

admin_emi added 1 commit 2026-07-26 19:43:29 +00:00