Skip to content
GRPNR.

05 — Postgres-for-everything vs multi-store: comparison and ruling

2026-07-19. Triggered by Robert’s challenge to the committed design (“why not use Postgres for everything, why have Grafana, SQLite etc?”). Full second research + steelman pass: 6 web packets + both-sides court (13 agents, ~858k tokens), raw output evidence/r3-pg-only-court-raw.json. This doc AMENDS 00-SYNTHESIS.md — see §6. Constitution rule #2 (“Postgres for everything is the default; deviate only on concrete evidence”) is the standard being applied, in both directions.

1. The two sides as argued

  • Side P — Postgres only. Hook events in the shared Postgres (partitioned, synchronous_commit=off, partition-drop 30-day retention); metrics either via a custom OTLP→PG receiver or dropped in favor of ccusage/JSONL; VictoriaMetrics not run; Grafana PG-only or dropped.
  • Side M — multi-store as committed. Postgres = SSOT only; SQLite WAL embedded in the Go monitor; vmsingle for OTLP metrics; one Grafana over three datasource types.

2. What the research established (per axis, strongest evidence)

For consolidation (P):

  • The “just use Postgres” school has real production proof far beyond our scale: Cloudflare 55M req/s on 15 PG clusters; Tantan 2.5M TPS with no Redis; Zerodha 20TB+/hundreds of billions of rows on boring PG patterns; Plexigrid collapsed 4 DBs→1 and measured 350× faster queries, 95% storage cut (amazingcto.com/postgres-for-everything, zerodha.tech/stack, tigerdata.com Plexigrid case).
  • Agent legibility: zero-shot PromQL accuracy for LLMs is ~2.6% vs a far more mature text-to-SQL ecosystem (~80% on complex queries) (arxiv 2503.03114). Weighted low in the ruling: our deployment has exactly 8 documented Claude Code metrics and a prebuilt dashboard — agents rarely author novel PromQL.
  • Cross-domain joins: cost-per-merged-unit — the program’s north-star metric — is literally a hook-events × ledger-units join. One engine makes it a SQL view; two stores make it an app-level join (FlightAware precedent: cross-metric queries 6.4s→30ms after consolidating into PG-family store).
  • The churn objection dies on partition-drop: GitLab’s vacuum saturation (100%→15% after splitting churny CI writes out) and the Aurora autovacuum storm (85–90% CPU, 3–4h) are dead-tuple problems from UPDATE/DELETE-heavy workloads. An insert-only event table with partition-drop retention produces near-zero dead tuples; a batched pg_cron DELETE at ~1K writes/sec measured <35ms/run. The blast-radius attack on P’s events leg does not survive cross-examination.
  • Bonus: putting events in PG eliminates the committed design’s weakest dependency — the frser-sqlite-datasource Grafana plugin (solo maintainer, 159 stars, community-signed, version-collision exposure against dashboard 25255’s Grafana-11+ floor).

Against consolidation (M):

  • No maintained OTLP→Postgres path exists. CONFIRMED 2026-07-19: Promscale dead since 2023-04 (Timescale’s own announcement); zero PostgreSQL metrics exporter in OTel Collector contrib (donation PR closed stale 2024-12; reopened issue still stuck 2026-07-18); pg_prometheus archived 2020. A hand-rolled OTLP/HTTP receiver is bounded (~150–400 LOC, 1–3 days — Claude Code exports http/protobuf, only 8 counters) but is permanent custom code owned by us across every CLI release, replacing a maintained 50–100MB binary that does it natively.
  • Zerodha — this research’s own PG-maximalist poster child — runs VictoriaMetrics + Grafana for observability, not Postgres (zerodha.tech/stack). The most PG-committed named team draws the line exactly where Side M draws it.
  • PG-family metrics stores hit a measured correctness cliff at high label cardinality (~27K cardinality: Timescale/Prometheus-family times out, VM answers in ~8s; 13× compute cost, 6.7× disk in the same load test — abiosgaming.com). Unlikely to bite at our series count; real at the margin.
  • Dashboard 25255 = ~25 panels + 3 template variables, free, Grafana 11+, Prometheus-family datasource. Rebuilding it as SQL panels or Go-board charts has only single-purpose precedents (tori-cli: TUI-only; simon: basic Chart.js graphs) — nobody has replicated a cross-filtered 25-panel board by hand and kept it maintained.
  • The isolation argument FOR SQLite specifically was weaker than plan 006 assumed: SRE literature argues loose coupling of monitoring components, not “different datastore than the monitored system”; the embedded-SQLite school (fly.io/litestream, PocketBase, Beszel) chooses it for latency (~10–20µs vs ~1ms) and zero-server simplicity — not blast-radius. On a 2-operator localhost box with sub-second PG crash recovery, fate-sharing is a degradation window, not an outage class.

3. Steelman court — sharpest surviving points

Side P (PG-only) Side M (multi-store)
Advocate’s best SQL-vs-PromQL agent legibility (2.6% zero-shot) + native north-star join; one operational surface No maintained OTLP→PG path — “drop vmsingle” is not simplification, it’s adopting a permanent custom-receiver liability in exchange for losing 25 free panels
Prosecutor’s best Same-instance schema/DB split shares one shared_buffers/WAL/checkpointer/autovacuum budget — no true isolation; Aurora/GitLab storms The Grafana-SQLite bridge: solo-maintained community plugin colliding with 25255’s Grafana-11+ floor — the “isolated” SQLite leg goes dark exactly when the shared board is needed
Cross-exam outcome Storm attack neutralized for insert-only + partition-drop; custom-receiver attack stands for the metrics leg Plugin attack stands against the SQLite leg; receiver attack stands for M against P

Residual open (both sides): real hook-event write rate at hundreds-of-agents scale (spike V2 — unchanged); Grafana’s actual idle RSS in minimal config; whether ccusage-only (no OTLP at all) would suffice if the stall signal proves unused.

4. Decision matrix

Weights: build+maintenance burden and robustness carry the most (this is a solo-operator system maintained by AI agents); footprint stays material; rule-2 compliance scored as “one-engine benefit”.

Criterion Wt P: PG-only (incl. metrics) M: committed (PG+SQLite+VM) H: hybrid (events→PG, VM stays, no SQLite)
One-engine ops + agent legibility 20% 5 2 4
Always-on footprint 15% 4 4 4
Build + permanent maintenance burden 20% 1 (custom OTLP receiver or lose stall signal; rebuild 25 panels) 4 5 (nothing custom; one plugin dropped)
Robustness / failure modes 20% 3 (all eggs one instance; storm risk bounded but real) 3.5 (plugin fragility; three stores) 4
North-star join fit (cost/unit) 15% 5 2 5
Ecosystem reuse (25255, VM OTLP) 10% 1 5 5
Weighted 3.10 3.28 4.45

Sensitivity: H wins under every tested weight shift. P overtakes H only if the ecosystem-reuse and build-burden criteria are both near-zeroed — i.e. only if we’re willing to own an OTLP receiver forever or abandon OTel cost/stall truth. M beats P narrowly but loses to H on every axis M itself argued (H keeps M’s vmsingle argument and concedes P’s events argument).

5. RULING — amend plan 006 to the hybrid

  1. Hook events, questions, watch items move from SQLite into the shared Postgres — separate fleetmon schema, insert-only partitioned tables (daily partitions, 30-day partition-drop via pg_cron), synchronous_commit=off on the event path (600ms max loss window on throwaway telemetry — acceptable by construction). The Go binary swaps rusqlite→pgx; single writer goroutine unchanged (now also batching inserts). Board degrades honestly when PG is unreachable: fleetops-snapshot-only view + “event history unavailable” banner (same honest-degradation pattern plan 004 already mandates for the ledger).
  2. VictoriaMetrics stays. The metrics leg of Postgres-only is rejected on decisive evidence (no maintained ingestion path; Zerodha precedent; free 25255; 50–100MB).
  3. Grafana stays, minimal config (anonymous Viewer, no alerting rules, no community plugins) — now exactly two datasource types, both first-class: VM (Prometheus-compatible) + Postgres (Grafana’s most mature datasource). frser-sqlite-datasource is dropped from the design entirely.
  4. SQLite exits the architecture. (The Rust fleetops TUI keeps whatever it uses internally — its seam is JSON, not the DB.)
  5. Net stores: Postgres (everything durable or joinable) + VictoriaMetrics (the one workload PG has no maintained path for). Constitution rule #2 satisfied in substance: every deviation now names its concrete evidence and its revisit trigger.

What changed since 00-SYNTHESIS scored SQLite, honestly stated: (a) the frser-plugin fragility was not weighed in the original scoring; (b) the partition-drop rebuttal neutralized the churn objection that made PG-events look risky; (c) the isolation argument for SQLite proved to be a latency/simplicity argument in the literature, not a blast-radius one — and latency is irrelevant for a 60s-cadence board; (d) the north-star join surfaced as structurally PG-shaped. New evidence, new ruling — that’s what the court is for.

Revisit triggers: PG event writes measurably interfere with the claim path (checkpoint/autovacuum pressure at spike-V2-measured rates) → second PG instance for fleetmon (true isolation; same engine, ~50–150MB) — NOT back to SQLite. VM series cardinality stays trivial after 3 months and the stall signal goes unused → consider dropping OTLP for ccusage and folding cost panels into PG.

5b. AMENDMENT 2 — Robert’s ruling 2026-07-19: drop VictoriaMetrics, ONE database

Robert accepted the DIY trade (“worth 1–3 build days”). Supersedes §5 point 2:

  1. VictoriaMetrics is dropped. The Go binary gains an OTLP/HTTP-protobuf receiver endpoint (~150–400 LOC; Claude Code emits 8 documented counter metrics at 60s cadence) writing into the PG fleetmon schema. Counter-delta/reset handling as SQL views (LAG()-based, session-scoped resets) — the one fiddly bit, covered by a golden test.
  2. Final store count: ONE — Postgres. (Plus harness fixture files on disk, not a DB.)
  3. Grafana cascades to deferred/optional. Its strongest keep-argument was the free dashboard 25255, which requires a Prometheus-family datasource — dead without VM. The Board renders the ~6 metric panels that matter + the ledger’s 5 views; ad-hoc exploration = psql (already the ledger docs’ named fallback). Add Grafana back only if board panels prove insufficient.
  4. Always-on budget improves again: ≈250–450MB (PG 130–150 + Encore binary 100–150 + Go binary 15–40 + zero observability daemons).
  5. New spike V5: OTLP receiver golden test — recorded protobuf fixture → expected rows, plus counter-reset case. Ownership named: the receiver tracks Claude Code CLI releases; any OTLP schema break shows up as V5 failure, not silent data loss.
  6. Revisit trigger: if the receiver becomes a maintenance sink (>1 day/quarter) or series cardinality explodes, vmsingle comes back exactly as specced in §5 — additive, no migration (telemetry is throwaway).

6. Consequences for the committed docs

Doc Change
00-SYNTHESIS.md Amendment banner added pointing here; stack table row for FleetOps DB reads “Postgres (fleetmon schema)”; budget unchanged (±0MB)
03-options.md candidate C Ingest store detail superseded by this doc (Go binary unchanged otherwise)
Plan 004 ADR-6 (SQLite ingest store) SUPERSEDED by this ruling (its own reversibility note: “migration is cheap”)
Validation spikes V3 becomes: pgx batched-insert golden test against partitioned fleetmon schema (burst 1k events, assert claim-path p99 unaffected); V2 unchanged and now also sizes PG partitions