Parent: #1936
Problem
The GitHub rate-limit observation table receives one INSERT per outbound GitHub API response and is pruned in daily bulk deletes by the retention job — a high-churn, insert-then-bulk-delete pattern that is exactly the shape known to cause dead-tuple bloat under Postgres's stock autovacuum settings (the codebase already has an alert rule watching for this specific symptom, which implies the risk was anticipated but not pre-empted). Separately, every observation is its own independent single-row INSERT with no batching or buffering, which is fine at today's volume but is unnecessary write amplification that will compound with connection-pool pressure at higher scale.
This is explicitly a before-larger-scale item, not an active production problem today.
Requirements
- Tune autovacuum aggressiveness specifically for the rate-limit observation table (a lower dead-tuple scale factor than the Postgres default), applied in a way that's a no-op on the SQLite backend.
- Evaluate lightweight batching/buffering for the observation INSERT (e.g. a short in-process buffer flushed on an interval or size threshold) without weakening the freshness the rate-limit admission-control read depends on.
Deliverables
- A Postgres-only migration or startup step setting per-table autovacuum storage parameters for the observation table.
- Either an implemented batching layer for the observation writes, or a documented decision that the current per-call INSERT pattern is acceptable at the scale this system targets, with the reasoning written down.
Acceptance criteria
- No behavior change to the SQLite backend.
- No regression to rate-limit admission-control accuracy — whatever batching window is chosen must not let a stale-by-more-than-a-few-seconds observation drive an admission decision.
- Dead-tuple ratio on the observation table stays materially below the existing alert threshold under sustained high-volume testing.
Expected outcome
The database layer scales cleanly well past today's volume without needing reactive intervention when the existing dead-tuple alert eventually fires.
Parent: #1936
Problem
The GitHub rate-limit observation table receives one INSERT per outbound GitHub API response and is pruned in daily bulk deletes by the retention job — a high-churn, insert-then-bulk-delete pattern that is exactly the shape known to cause dead-tuple bloat under Postgres's stock autovacuum settings (the codebase already has an alert rule watching for this specific symptom, which implies the risk was anticipated but not pre-empted). Separately, every observation is its own independent single-row INSERT with no batching or buffering, which is fine at today's volume but is unnecessary write amplification that will compound with connection-pool pressure at higher scale.
This is explicitly a before-larger-scale item, not an active production problem today.
Requirements
Deliverables
Acceptance criteria
Expected outcome
The database layer scales cleanly well past today's volume without needing reactive intervention when the existing dead-tuple alert eventually fires.