ottyl/ideas/0005-additional-read-indexes.md

Idea: additional indexes to speed up reads

Original note

"Ask AI to come up with additional indexes that will help speed up reads." — an open-ended research request rather than a scoped change; this note captures what's already known so a future deep-dive doesn't start from zero.

Current index surface (confirmed in code)

• IndexKey (src/engine/index_key.rs) — the engine's indexing key space includes at least IngestedLog { sequence, timestamp }, Bucket (metric bucket data), and LastMetricEvent. Ordering is primary-sequence for logs (see 0003-timeline-ordering-invariant for detail).
• SortHelperKey (src/engine/snapshot/sort.rs) — a secondary index keyed by a declared param's value (plus sequence as tie-break), built to support sort_by queries; has explicit staleness handling (purge_stale_sort_helper_indexes, referenced from ticket 081's history) tied to new bucket ingestion.
• .claude/CLAUDE.md's repowise health summary already flags "19 open static I/O-in-loop / N+1 findings" and calls out src/engine.rs's handle_query as a function/brain-method hotspot — likely overlaps with whatever a fresh performance pass on read paths would surface, worth checking those findings first (get_health() / get_risk(['src/engine.rs']) via the repowise MCP tools, once the repo's index is initialized — it wasn't available this session, see note below) rather than starting from a blank slate.
• Related, already-ticketed read-path work referenced in prior ticket history: ticket 077/078 (metric bucket range narrowing — "narrow the range, don't rescan everything") and ticket 081 (PromQL dashboard queries rebuild full series from scratch per poll) — both were framed around the same "avoid full rescans" principle this idea is chasing generally.

Why this is an idea, not a ticket

This is explicitly a "come up with ideas" ask, not a pre-scoped change — it needs a dedicated investigation (likely with actual profiling under a representative workload, per this project's samply-based profiling convention) rather than guesses from reading code structure alone. Ticket 081's history is a useful precedent here: 079/080 (also performance tickets) were removed for lacking "profiling evidence to justify the fix" — so a fix in this space should follow samply record evidence, not speculation.

Note

The repowise MCP tools reported no index for this repo this session ("This repository has no repowise index yet") despite .claude/CLAUDE.md describing one as current — worth running repowise init --yes (or checking why the existing index isn't being picked up) before the next performance-focused session, since get_risk/ get_health/get_change_risk would materially speed up this kind of investigation.

Suggested next step

Profile a representative workload with samply against the read paths (Logs, Timeline, dashboard PromQL panel queries) to find actual bottlenecks, then propose specific new index structures (or narrowings of existing ones) grounded in what the flamegraph shows — rather than guessing at indexes with no evidence they'd matter.

Implications and things to consider

Performance

• Any new index is a write-time cost paid on every ingested line in exchange for a read-time win — worth stating explicitly per proposed index, since the engine's ingest path is already doing param extraction + metric bucketing + alert evaluation per line (see 0001-actor-split's findings on how much work already happens synchronously per line); an expensive new index (e.g. a full-text inverted index for regex/text search) could turn ingest into the new bottleneck instead of read.
• A text/regex index in particular is a different kind of structure than the existing IndexKey/SortHelperKey model — those are ordered key→value indexes; full-text search typically wants an inverted index (token → line positions) or a trigram index, which is a new subsystem, not a new IndexKey variant. Worth scoping as its own investigation rather than bundling into "additional indexes" generally.
• Memory cost matters as much as CPU cost here — this is a for-the-life-of-the-process in-memory tool (per CLAUDE.md's description), so any new index's memory footprint competes directly with how much log history a user can retain before running out of RAM. A samply CPU profile alone won't surface this — a memory profile of retained-line-count vs. RSS would be needed too.

Code structure

• New indexes need to hook into the same lifecycle the existing ones do: built incrementally at ingest, invalidated/pruned when retention drops old lines, and rebuilt on backfill (declare/profile-switch) the same way SortHelperKey's purge_stale_sort_helper_indexes does. A proposal that only covers "how to build it" without covering eviction/backfill parity with existing indexes is incomplete.
• If 0001-actor-split happens first, new indexes would likely be scoped to whichever worker owns the data they index (e.g. a profile-specific index lives with that profile's worker, not the primary log worker) — worth sequencing this investigation after that architectural decision rather than before, so new indexes aren't designed against a data-ownership model that's about to change.

Functionality

• Read-path speedups are invisible to users unless something is currently slow enough to notice — without a concrete complaint or profiling evidence of an actual slow query, this risks becoming effort spent on indexes nobody was blocked on. The samply-first approach in "Suggested next step" is meant to guard against exactly this.

Not yet considered

• No investigation yet into what "reads" actually means for the intended scope — dashboard panel polling (already covered by ticket 081's history), the log timeline's default view, ad-hoc filter-bar queries, and the MCP server's query surface (src/api/mcp_server.rs) are all "reads" with potentially different bottlenecks; a single "additional indexes" effort may need to be split once profiling shows which of these is actually slow.
• No check on whether retention limits already bound the problem — if there's a hard cap on retained log lines, some read-path costs may already be bounded in practice regardless of indexing, which changes how much this is worth investing in.