Skip to content

Add SQLite indexes for post-session queries #17

Description

@Abhay512

backend/sinks/storage.py creates thirteen tables and not one index. Every post-session query — reconstructing a ride, pulling a day of rankings, auditing signals — is a full table scan, and these tables grow by thousands of rows per session.

What to do

Add indexes in the existing additive-migration style (the _migrate_* methods show the pattern — CREATE INDEX IF NOT EXISTS, safe to run on an existing database):

  • signals(ts), signals(underlying, ts)
  • rankings(ts), rankings(underlying, ts)
  • oneway_movers(underlying, entry_ts), oneway_movers(ts)
  • whatever the other boards are actually queried by

Acceptance

  • Migrations run cleanly on a fresh database
  • Migrations run cleanly on an existing one and are idempotent
  • EXPLAIN QUERY PLAN before/after in the PR for at least one query
  • No write-path regression (these tables take one batch insert per minute)

Notes

Measure, do not guess — an index that is never used still costs write time. Say which queries you optimised for.

Metadata

Metadata

Assignees

No one assigned

    Labels

    backendPython engine, feed, storage or APIgood first issueWell-scoped, self-contained, a great place to startperformanceLatency, memory or throughput

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions