gs: bound the load of sheet exports on /gs/db (slow full-scans, process stalls)

[Danswar]

Problem

POST /v1/gs/db serves the sheet-export tooling (~100 distinct consumers, identified via the identifier field in the slow-query logs). The syncs run on timed triggers around the clock against PRD, and their query shape makes them far heavier than they need to be:

• 10,754 queries >3s in the last 7 days (~1,500/day), runtimes up to 12.6s, PRD only.
• Every observed sync passes updatedSince: 1970-01-01 — the DTO's incremental capability is unused, so each run re-scans from epoch with joins and DESC sorts (maxLine 100–500 + multi-table joins).
• Requests up to 8 MB response size were observed; one query ran 98s.

Impact

1. Ingress errors: exports that outlive the origin's patience die mid-flight and surface as 502/EOF at the edge (tracked infra-side in DFXServer/server#744, which asks for this exact follow-up triage).
2. Process stalls: of 4 clean (non-deploy) container restarts checked, 2 were preceded by a ~5x burst of slow /gs/db queries in the prior 10 minutes (27 resp. 25 vs ~5 baseline). Contributing stressor — not proven sole cause.
3. Steady DB load on the serving path for reporting reads.

Fix options (not mutually exclusive)

1. Consumer-side (biggest win, no API change): make the sheet syncs pass a real updatedSince so pulls are incremental. Needs an owner for the sheet scripts + a doc note in the GS module.
2. Server-side guardrails: limit concurrent /gs/db exports (queue, e.g. 2 in flight), and/or per-identifier rate limit; consider a hard runtime cap with a clear 429/503 so a heavy query fails fast instead of stalling the process.
3. Structural: serve GS reads from a read replica so reporting load cannot touch the serving path.

Acceptance

• Slow-query volume (>3s) on /v1/gs/db reduced by an order of magnitude (baseline: ~1,500/day).
• No /gs/db-attributable ingress EOF/502s in the edge logs (verification via DFXServer/server#744).
• Container restarts show no correlation with export bursts over a 2-week window.

[Danswar]

Follow-up measurements (07-19 → 07-22, PRD access/DB logs) — two findings that reframe the load picture, plus a concrete lead:

1. Total volume is much larger than the slow-query count suggested. POST /v1/gs/db runs at a constant ~15k requests/hour (~360k/day), flat around the clock — the ~1.5k/day figure above only counted queries >3s. Observed syncs still pass updatedSince: 1970-01-01, so effectively every consumer re-exports from epoch on every trigger. Standing cost: ~46 MB/s of continuous DB reads, and ~300 queries/h over 3s even in the best state.

2. The API goes through day-long "degraded regimes" in which all DB-backed endpoints slow 2–3× — not just /gs/db. Two windows in the observation period: up to 07-20 ~09:30 UTC, and 07-21 18:35 → 07-22 ~16:00 UTC (sharp onset, within ~5 min). During these windows:

• /gs/db >3s count rises to 700–850/h at unchanged request volume (same consumers, same tables — only service time changes);
• interactive/internal endpoints go from p95 ≈ 1s to p95 ≈ 2–3.4s, individual requests up to 27s — this is what makes the internal tooling feel unusable;
• even trivial endpoints (e.g. GET /v1/fiat) exceed 2s hundreds of times per day;
• the connection-pool monitor shows average open connections up ~40% with zero waiters — per-query service time degrades; nothing queues at the pool.

Ruled out as the regime trigger (all flat across calm vs degraded): export volume itself, deployments (no deploy at either onset; a mid-regime app restart did not clear it), DB restarts/checkpoint activity, host CPU, host disk throughput, per-container CPU and block-IO.

Lead for resolution: DB-side service time degrades while every resource counter stays flat — which points at something currently invisible: a long-lived transaction pinning cleanup (dead-tuple accumulation on hot tables), lock contention, or storage latency below the throughput metrics. The DB currently logs neither slow statements nor lock waits, so episodes cannot be attributed. Suggested first step: enable log_min_duration_statement ≈ 3000 and log_lock_waits = on (optionally a postgres exporter for pg_stat_activity transaction age) — the next episode then identifies the offender directly instead of requiring another elimination hunt.

Independently of the regime question, incremental updatedSince remains the biggest lever on the standing load, and would shrink the blast radius of these windows for interactive users.