/informes fn_getInformesBundle 25s exec + pg-pool connection exhaustion (469 perf regression)

Sub-tickets

Questions

Activity

• wi-cli-venus created · 6w ago
• wi-cli-venus note · 6w ago
  Real-time applog errors 2026-07-02 18:12 UTC on /informes (deploy dpl_7SwH2vGSMZA1kzyMyPAf3fkvHQfB). Vercel runtime-log facts: fn_getInformesBundle ran 25184ms once (18:12:49) holding a pg connection 25s; pg-pool 'timeout exceeded when trying to connect' (digest 2914597568) at 18:12:21 = pool exhaustion under 7 rapid /informes hits in 80s; same fn ran 280ms on another call = plan-unstable/bimodal not uniformly slow. Filter-option queries also slow (2390ms adjuntoId 203 rows, 2418ms vComisiones 6 rows). Operator-alert send timed out 3500ms on /soporte same window. Leading hypothesis (to verify, not concluded): plan regression from PLUTO-469 045/046 ANY(array) clauses on assignmentJtpId/assignmentComisionId - missing index / bad plan; plus no statement_timeout so a pathological run holds a connection. db-pluto-cc investigating (EXPLAIN ANALYZE app 18-arg call under jefe+comision vs NULL; pool/statement_timeout config); investigate+propose only, audit pre-apply gates the apply (fn/schema change on live prod). Target: fn p95 <~2s + no pool exhaustion under rapid filter nav. Only Elazar tests /informes now (no real-user impact). audit-pluto-cc heads-upped for pre-apply.
• wi-cli-venus note · 6w ago
  FIX SHIPPED (primary): migration 047 ALTER FUNCTION fn_getInformesBundle SET statement_timeout='4s' — applied live + pushed SHA 16222ec, version 1.97.13, deploy dpl_2dEkojBHainEtfcLSqh6EtCJ5qEj READY, prod alias attached, 0 runtime errors. Live proconfig={statement_timeout=4s}, overload=1, normal call 176ms (no clip). 4s ceiling sits ~1s BELOW pool connectionTimeoutMillis(5s) → a stalled run releases its slot before any concurrent caller's connect-timeout fires = pool-exhaustion cascade FULLY CLOSED (not just shrunk). Root cause was config-level (no statement_timeout + DB_POOL_MAX=2), NOT a 469 plan regression (indexes adequate, generic-plan trap tested + did not reproduce, 25s unreproducible from any param combo = compute-tier contention signature). Flow: db-pluto-cc investigate+draft, audit-pluto-cc pre-apply PASS(8s->4s recut on audit caveat #1)+PTD PASS. WI STAYS OPEN for 2 deeper-fragility follow-ups: (1) evaluate raising DB_POOL_MAX=2 for the /informes heavy-analytics-fn path (coder/pool.ts lane, needs evidence - one slow run = 50% of pool); (2) capture the original 25s incident's exact filter params on any recurrence (Vercel logs did not surface query string/bound values this time) to enable a definitive EXPLAIN.
• wi-cli-venus assigned · 6w ago
  db-pluto-cc / db
• wi-cli-venus statusChanged · 6w ago
  Elazar greenlit ('471 do'). Root fix beyond the 4s-timeout containment: EXPLAIN + collapse fan-out; coordinate w/ coder on 188 (same /informes route). Audit pre-apply gate on the migration.
• wi-cli-venus completed · 6w ago
  Verified-fast, already-mitigated — NO migration (db pushed back on the rewrite premise, correctly). MEASURED on full prod (307 practicas/249 students/614 facts): every param shape sub-400ms (heaviest admin+both-comparativas 8-call session = 420/333/325/326/323/341/259/259ms, flat, no generic-plan degradation; teacher-scoped 345ms; server-side ~150-250ms, rest is venus→Supabase RTT). NO reproducible 25s / no algorithmic blow-up. Logs (14d): ZERO statement-timeout (the 4s fn bound has never errored a real user); exactly 3 pool-connect-timeouts, ALL predate 047 (last 07-02 18:12, 047 landed 18:25) — zero since; 2 of 3 were /practicas not /informes → the fragility is the SHARED DB_POOL_MAX=2 pool (cross-route), not an /informes-fn defect. Rewriting the 1084-line rosterScope authz fn (load-bearing PII containment per 456/458) = regression risk for zero gain. Keep 4s bound as standing blast-radius guard. The 25s was one-off compute-tier CPU contention (noisy-neighbor), unreproducible from params. Genuine lever = pool headroom → parked on PLUTO-146 (scaling, needs Supavisor txn-pooler analysis, not a blind bump); safe pool-pressure reduction = 102's fan-out collapse (fewer queries, not more connections). db optional-offered a 10-20x synthetic scale test — deferred, not needed now.