Supabase DB Index Audit — Shared-Project Hotspots + Crypto Index Hygiene (2026-07-09)

Full index/statement audit of the shared Supabase project (one project hosts the CRM, pmv2’s public/pm_* tables AND our isolated crypto_shortterm schema — crypto-shortterm-empire-ui-crm-topology-2026-07-06). Full report: docs/superpowers/db-index-audit-2026-07-09.md in the crypto repo; babylon pmv2 #1162 (question) + #1163 (findings + per-owner DDL).

For Agents — headline

DB 8.4GB (crypto_shortterm 4.3 / public 4.0). Top-10 statements ≈ 80% of server exec time; PM’s schema owns ~65% of it. Crypto’s writers are healthy (0.1–0.2ms). The real crypto lever is retention/partitioning, not indexes — decision left to Andras.

Biggest sinners (mostly PM-owned)

statement	cost	note
TEXT end_date regex sweeper	6.5s × 1,371 calls (16.9%)	regex over a TEXT date column
pm_trades fat select	10,465 rows/call × 20k calls (19.8%)	index fine — app query shape
pm_trader_metrics GROUP BY MAX	2.6B tuples read via 19,928 seq scans
pmv2_market_resolutions DISTINCT ON	549ms × 3,927 calls	carries a useless 155MB index (4 lifetime scans) on an ~800k-rows/day insert table

Proposed DDL per owner (posted to babylon #1163)

• PM: 2 partial indexes + 3–4 index drops + 2 query rewrites.
• deploy: realtime WAL polling ~2.8% of exec time; dead event_2026_05/event_2026_06 partitions (131MB).
• weather: snapshots seq-scan pattern.

Crypto side

• migrations/0010_index_hygiene.sql written, NOT applied: obs_asof_idx (126MB, 946 scans) btree → BRIN.
• Writers healthy: 0.1–0.2ms per insert.
• The real lever = retention/partitioning on observations + pm_book_snaps — the 912MB pm_book_snaps pkey IS the ON CONFLICT dedup key over 77-char TEXT token_ids, so no index change shrinks it; only retention does.
• Decision left to Andras (apply 0010, set retention policy).

binance_ticks pkey has NO asset column — unresolved correctness question

pkey = (trade_ts, price). If multi-asset ticks ever land in this table, cross-asset ticks at the same price in the same instant silently dedup (row loss, not an error). Same asset-blind-key family as the cross-asset join trap. Unresolved: confirm whether binance_ticks is BTC-only by construction or needs an asset column.

Repro

zsh scripts/db_index_audit.sh

duckdb postgres ATTACH, read-only, masked connection string — the same live-DB access pattern as crypto-shortterm-data-sources (2026-07-02 append).

Related

• crypto-shortterm-empire-ui-crm-topology-2026-07-06 — why one Supabase project (mkofmdtdldxgmmolxxhc) holds CRM + pmv2 + crypto_shortterm.
• crypto-shortterm-observations-window-id-not-asset-unique — the sibling asset-blind-key gotcha (window_id not asset-unique; emitted_signals has no asset column).
• crypto-shortterm-data-sources — the duckdb → Supabase read-only ATTACH analysis pattern the audit script reuses.
• crypto-shortterm-emit-publish-first-latency-2026-07-09 — the week’s other DB-cost finding: the synchronous claim INSERT (cross-region RTT) on the fire path.