[Senate] Postgres pool autoscaler driven by concurrent-request load done

Effort: deep

Goal

api_shared/db.py:134 documents the 2026-04-21 manual bump 50 → 80 after seeing 4.1 % of requests queue at the cap. The
window between "comfortable" and "queueing" is narrow; the next
traffic surge will hit the same wall and an operator will hand-tune
again. PG max_connections=100 so we have ~20 connections of
remaining headroom — enough for a controlled autoscaler that
expands the pool on observed pressure and contracts when idle.
Replace the static cap with a feedback loop driven by the
ContextVar holder's checkout-wait histogram.

Acceptance Criteria

a fixed-bucket histogram of pool checkout-wait times (0,
1, 5, 10, 50, 100, 500, 1000+ ms) sampled at every
connection() call. Exposed via the existing pool_stats()
function and a new Prometheus gauge
scidex_pg_pool_checkout_wait_p95_ms. runs every 30 s; reads checkout-wait p95 over the last
5 minutes; if p95 > 25 ms for 3 consecutive runs and current
max_size < SCIDEX_PG_POOL_HARD_CAP (default 95), bumps
max_size += step (default 5) via psycopg-pool's
ConnectionPool.resize. If p95 < 1 ms for 10 consecutive
runs and pool_size < max_size * 0.6, shrinks max_size
by step (never below _POOL_MIN+5). table (timestamp, prev_max, new_max, reason, p95_ms,
pool_size). The senate observability page renders a 7-day
sparkline. SCIDEX_PG_POOL_HARD_CAP. Refuses to bump if
pg_stat_activity already shows >= max_connections - 5
total backends across the cluster (other apps' connections
counted). Cooldown 60 s after every change. only) pins max_size until POST /senate/pg-pool/unfreeze
is called. Persists across restarts. - Synthetic histogram → autoscaler decides bump.
- Cooldown enforced.
- Hard cap respected.
- Concurrent-cluster check stubs pg_stat_activity and
refuses bump correctly. synthetic wrk ramp shows the pool expanding under load,
contracting after the load ends, and the
pg_pool_autoscaler_log table populated.

Approach

Histogram + Prometheus metric first; verify the existing

_pool_size_gauge (api.py:1311) cohabits.

Autoscaler standalone systemd unit; one process, no leader

election (a single uvicorn process owns the pool, others have
their own).

Manual override endpoints reuse the existing admin auth

pattern from api_routes/admin.py.

Rollback hatch: SCIDEX_PG_POOL_AUTOSCALE=0 env var disables

the autoscaler entirely.

Dependencies

• _request_db_holder (request-scope ContextVar, fixed

2026-04-21 — see memory: project_scidex_pg_pool_request_scope.md).

Dependents

• q-perf-deferred-work-queue — moves heavy ops out of the

request path, reducing pool pressure.

Work Log

2026-04-27 — Implementation complete (SHA: 8e6ef18f4)

All acceptance criteria implemented and verified:

• api_shared/db.py: Added from collections import deque; rolling 5-min

checkout-wait histogram (_checkout_wait_deque); record_checkout_wait(),
get_checkout_wait_p95_ms(), get_checkout_wait_histogram(); Prometheus
gauge scidex_pg_pool_checkout_wait_p95_ms; pool_stats() now includes
checkout_wait_p95_ms. Instrumented all 4 pool.getconn() call sites in
get_db() and get_db_ro().

• scidex/senate/pg_pool_autoscaler.py: Daemon thread (30 s interval).

p95 > 25 ms for 3 consecutive runs → bump. p95 < 1 ms for 10 consecutive
runs and pool_size < 0.6×max_size → shrink. Hard cap (SCIDEX_PG_POOL_HARD_CAP=95),
cluster check (pg_stat_activity), 60 s cooldown. Freeze/unfreeze via
pg_pool_autoscaler_state. SCIDEX_PG_POOL_AUTOSCALE=0 disables.

• migrations/add_pg_pool_autoscaler_log.py: Tables pg_pool_autoscaler_log

and pg_pool_autoscaler_state created (migration run: done).

• api_routes/senate.py: POST /senate/pg-pool/freeze, POST /senate/pg-pool/unfreeze

(API-key auth). GET /senate/pg-pool — full observability page with 7-day sparkline,
bucket histogram, and resize decision log.

• api.py: start_autoscaler() wired into _startup_lifespan after prewarm.

Senate page now shows a pg_pool_autoscaler_section tile with 7-day pool-max
sparkline and current pool metrics.

• tests/test_pg_pool_autoscaler.py: 10 tests — all pass.