Evaluate sqlite-vec as a vector index behind the existing search API

[himanshu231204]

Problem

Every search scores every row. That's exact — no recall loss from approximate indexing — and deliberate. But it's linear, and a shared or long-lived memory store will eventually find the ceiling.

Blocked on #21. Don't start here until the benchmarks exist. If embedding time dominates scoring time, an index buys nothing and this issue should be closed as unnecessary.

What to investigate

sqlite-vec is the natural candidate: it's a SQLite extension, so it holds the one-file property that makes this project what it is. A separate vector database is an explicit non-goal.

Acceptance criteria

• Benchmarks from Build a benchmark harness for store and search at 1k / 10k / 100k memories #21 showing scoring time actually dominates — otherwise close this
• Index sits behind the existing MemoryStore.search() signature. The public API must not change to accommodate a storage strategy
• Recall measured against the current exact scan, not assumed. Report how often top-5 results differ
• Graceful fallback when the extension isn't available — a pip install that can't load it must still work, exactly as FTS5 already degrades to pure vector search
• The hybrid keyword bonus still applies; this changes candidate generation, not scoring semantics
• Migration path for existing databases — vectors are already stored, so this should be index construction, not re-embedding
• Install footprint measured. Install-to-working under 30 seconds is a project goal

Why this needs discussion first

Three things could make this the wrong call:

1. Recall loss. For memory, a missed result isn't a slightly worse search — it's the agent confidently saying it doesn't know something it was told.
2. Install weight. A platform-specific binary extension complicates the "pip install and it works" story on every OS the CI matrix covers.
3. Complexity. An index needs maintaining, rebuilding, and invalidating on model change. The current design has none of that.

Please comment with findings before writing much code.

Pointers

• src/localmem_mcp/store.py — search() and _keyword_hits(); the FTS5 try/except fallback is the pattern to copy for optional-extension handling
• ROADMAP.md — 0.3

[himanshu231204]

Findings before code, as asked. Harness and full write-up in #60 (benchmarks/sqlite_vec_eval.py, benchmarks/SQLITE_VEC.md); summary here.

Answer: yes, but not first, and never as a requirement. Land #23 first — it is most of the win at the sizes real stores have. sqlite-vec is worth adopting after it, as an optional accelerator past ~50k memories.

Method

Same seeded corpus and queries through three strategies: today's exact scan, a vectorised numpy scan (#23), and vec0 KNN for candidate generation feeding the existing hybrid scorer. Recall@5 and ranking agreement measured against the exact scan every run. numpy+ is a cached in-memory matrix — the ceiling a cache would buy, not like-for-like, since it skips the per-query row fetch the other two pay.

Linux x86_64, CPython 3.11.15, sqlite-vec 0.1.9, stub embedder, limit=5, oversample=8:

memories	exact p50	numpy p50	numpy+ p50	vec0 p50	vec0 p95
1,000	43.9 ms	6.5 ms	1.9 ms	5.6 ms	6.2 ms
10,000	466.0 ms	69.3 ms	17.4 ms	20.8 ms	24.2 ms
100,000	4,815.3 ms	1,313.0 ms	149.2 ms	134.6 ms	191.9 ms

Against the acceptance criteria

• Benchmarks showing scoring dominates — Build a benchmark harness for store and search at 1k / 10k / 100k memories #21 has it (91–93%), but that alone doesn't justify an index: scoring cost per row and number of rows scored are different terms. Hence the comparison above.
• Recall measured, not assumed — 100% recall@5 and identical top-5 ordering at every size, filtered and unfiltered. Not luck: vec0 in 0.1.x is brute force in C, ANN is a tracked pre-1.0 goal. The speedup is a constant factor, not a change of complexity class.
• Graceful fallback — mandatory, and more load-bearing than expected. See risks below.
• Hybrid keyword bonus still applies — yes, but only if keyword hits are unioned into the candidate set explicitly. A KNN of exactly limit cannot reproduce current ranking even with a perfectly exact index, because the FTS5 bonus can promote a row the KNN never returned.
• Migration path — index construction, no re-embedding. The stored float32 blobs are exactly what vec0 wants: 3.5 s for 100k, one time.
• Install footprint — measured, and it inverts the assumption. sqlite-vec is 176 KB / 0.9 s cold; the numpy Speed up cosine scoring without changing results #23 wants is 45 MB / 3.2 s. Wheels cover the whole CI matrix (macOS x86_64 + arm64, manylinux x86_64 + aarch64, Windows amd64); no musllinux, so Alpine has no binary. The 30-second goal is untouched either way.

On the three risks

Recall loss — not a risk in the released version, but a reason to pin. A version bump that ships ANN would change this silently.

Install weight — the opposite of what we assumed; sqlite-vec is by far the cheaper of the two optimisations to install.

Complexity — the real cost, and not where the issue expected it. Not index maintenance, which SQLite handles. Two other things:

1. Extension loading can't be relied on, and can't be detected at install time. Python's sqlite3 is not built with loadable-extension support by default — on macOS system Python, enable_load_extension raises AttributeError outright. A user can pip install sqlite-vec successfully and still not load it. The fallback isn't a nicety for unusual setups; on some platforms it's the main path, and it has to catch ImportError, AttributeError, and sqlite3.OperationalError.

2. Candidate generation isn't scoring, and filters notice. With a tag filter at 10k, varying only the oversample factor:

   oversample	recall@5	identical order	queries returning short
   1	99.0%	95.0%	5.0%
   2	99.0%	95.0%	5.0%
   4	100.0%	100.0%	0.0%
   8	100.0%	100.0%	0.0%

   Latency is flat across all four (17–18 ms), so oversampling is nearly free — but the factor it needs depends on filter selectivity, which is data-dependent. A sufficiently selective tag outruns any fixed factor, and this is exactly the failure mode risk Scaffold localmem-mcp: local-first, zero-API memory for AI agents #1 in the issue describes: the agent confidently saying it wasn't told something.

Why #23 comes first, and what's left for #22 after it

At 10k a plain vectorised scan is 69 ms — inside budget, exact, no binary extension, no new knob. #23 alone moves the ~100 ms crossover from ~1,500 memories to north of 15,000, which covers essentially every personal store.

What #23 doesn't fix is 100k: the vectorised scan is still 1,313 ms there, because it reads 150 MB of blobs per query — I/O, not arithmetic. Only the two strategies that avoid the per-query read get under 200 ms, and between them vec0 (135 ms, index on disk in the same file) beats a cached matrix (149 ms, 146 MB resident, 3.3 s warm-up, invalidated on every write). With a tag filter at 100k the gap widens: 144 ms vs 284 ms, because the KNN prunes before scoring.

So the case for sqlite-vec isn't raw speed — it's being fast at 100k without holding the corpus in process memory, which is the better shape for a single-local-file tool.

Suggested shape if it's implemented: optional extra (localmem-mcp[vec]), one load helper returning None on any of the three failure modes, index built lazily on first search and dropped when embedding_model/dim changes, candidates = limit * oversample KNN rows unioned with FTS5 hits, exact scan kept for tag-filtered queries unless the filter is measured to be unselective, and the extension version pinned with recall re-measured on every bump.

Happy to close this as premature and re-open after #23 lands if you'd rather re-measure then — the harness is committed either way.

---

Generated by Claude Code