DB Retention Proposal — the 23 GB Postgres

2026-08-17. Source: full cross-repo reader/writer inventory (backend + pipeline agents) + live age profiles. Status: ✅ EXECUTED 2026-08-17 (backend #134 + pipeline #49 + destructive batch). Result: 23 GB → 7.4 GB. This is "Phase 6" of legacy-deprecation-spec.md (discovered during Phase 3).

Finding

The shared Postgres is 23 GB; four tables hold ~87%. None of the four has ever had an automated prune. Two have no writers at all. Postgres memory is the largest line on the Railway bill, so this is the bill's root cause.

Per-table verdicts (from the reader inventory)

scanner_activity — DROP (reclaims 10 GB)

DB persistence was deliberately removed 2026-06-01 (activity_log.go:38-41: "write-only, ~2M rows/day, zero readers — the UI reads this ring, not the table"). Zero readers since. Sole code refs: a table-existence check (migration_service.go:371) and the boot DDL (scanner_schema.sql). Prereq: remove both refs first, or the verify call reports a missing table and boot recreates an empty one.

ticker_data_snapshots — TRUNCATE (reclaims 3 GB)

Zero writers anywhere; frozen 2026-04-11. All production readers use 1h–48h freshness windows — they have returned empty for four months already. The unbounded latest-per-ticker fallbacks (market_data_service.go:1198, report handlers) currently serve stale April prices, which is worse than serving nothing. Deepest reader: a manual CLI audit at 90 days — also already empty. TRUNCATE keeps the table + all reader code intact and changes no observable behavior except removing stale-price fallbacks. (Full DROP would mean surgery across the report service — not worth it.)

lgbm_test_predictions — run-keyed prune (reclaims ~2.8 GB)

Retention here is run-based, not time-based: every reader resolves a run_id first — backtest + cohort-compare read the latest completed run per cohort; /lightgbm/calibrate reads the latest promoted run (promoted=TRUE filter is load-bearing — the 2026-08-05 Platt incident). No reader ever touches an older run, and the 13 retired cohorts' runs are unconditionally dead. Policy: keep the latest 3 completed runs + the latest promoted run per ACTIVE cohort; delete everything else. Plus a pipeline change: auto-prune to this policy after each training run persists, so it never regrows. DELETE needs a VACUUM FULL lgbm_test_predictions afterward to return space to the OS (brief lock; nothing hot reads this table).

action_decisions — 400-day retention cron, dormant until 2027 (reclaims 0 today)

Load-bearing core ledger; no prune exists. Deepest legitimate reads:

• Backtest API family: handler-capped at 365 days
• horizon_edge (via resolved_at ≥ 90d joins): ~150 days of decided_at
• Resolver cron: unbounded but self-limiting (60d max horizon)
• book_equity_status random baseline: back to book inception
• action_resolutions has ON DELETE CASCADE — pruning decisions deletes their resolutions, which the 90d/30d hit-rate readers consume. The 400-day horizon comfortably clears all of this.

Policy: nightly DELETE FROM action_decisions WHERE decided_at < NOW() - INTERVAL '400 days' (batched). Oldest row today is 2026-05-08, so the cron deletes nothing until ~mid-2027 — installing it now is cheap insurance against the next silent 10 GB. Two prereqs shipped with it:

1. Clamp ?since= on /api/action-engine/backtest/preset-stats (action_engine_backtest_handler.go:315) to the retention horizon — it currently accepts any date unbounded.
2. Accept that all-time DISTINCT ticker universe readers (data_collector_sweep, macro_environment, backfill/stale sources) become 400-day-activity windows — fine in practice; note it in code.

Rejected alternative: pruning is_latest = FALSE rows younger than 400d would reclaim more sooner, but the resolution-join readers and the backtest API legitimately read superseded rows well inside a year — not worth the risk for a table growing only ~1.2 GB/month with a working cap installed.

Execution order

1. Backend PR: drop scanner_activity refs (existence check + DDL); add the action_decisions retention cron + ?since= clamp.
2. Pipeline PR: post-training auto-prune for lgbm_test_predictions.
3. After both deploy — the destructive batch (~16 GB): DROP TABLE scanner_activity; TRUNCATE ticker_data_snapshots; one-time lgbm_test_predictions prune to policy + VACUUM FULL on it.
4. Verify DB size (expect ~23 GB → ~7 GB) and watch the Railway memory line over the following week.

Expected impact

~16 GB reclaimed immediately (70% of the DB); working set small enough that Postgres's resident memory should drop meaningfully — this compounds with the private-networking fix (2026-08-12) on the same bill. All four tables end up with a stated policy: two gone/empty, two capped.