Architecture: Migrate BM25 & Graph Search from In-Memory to SQLite

[Tanmay-008]

The agentmemory project currently relies on 3 powerful search mechanisms to build its Hybrid Search and contextual reasoning engine:

1.Vector Search
2.BM25 Search
3.Graph Search

but the problem is that the system performs search indexing and traversal completely in-memory. This creates many critical bottlenecks like
cpu bound operations ,RAM exhaustion ,etc.
i saw this exact same bottleneck in Vector Search, and for that, I created the recent pull request to use sqlite-vec.

The Proposed Solution:

So, for the remaining BM25 Search and Graph Search, we should use SQLite storage instead of in-memory storage.

1. BM25
   Current: Builds a massive Inverted Index Map in Node.js RAM.

Proposed: Enable SQLite's native FTS5 extension. When memory text is saved, we insert it into an FTS5 virtual table. We simply query: SELECT obs_id FROM bm25_index WHERE text MATCH 'query' ORDER BY bm25(bm25_index) LIMIT 20.

2. Graph Search via SQLite CTEs
   current: this.kv.list() loads all nodes and edges into RAM to run BFS loops in JavaScript.

Proposed: Mirror nodes and edges into dedicated graph_nodes and graph_edges SQLite tables. Instead of JS loops, we send a WITH RECURSIVE (CTE) query directly to SQLite.

this will dramatically improve performance and solve the current bottlenecks. The main data remains safely in the KV store, but offloading the search logic to SQLite

[rohitg00]

We might integrate this one: https://github.com/iii-hq/workers/tree/main/iii-database

[Tanmay-008]

thanks @rohitg00 for the suggestion, i will integrate iii-database .

[rohitg00]

For integrating database worker, we need to migrate entire iii version to 0.11.7 or more. So, we should wait.

[Tanmay-008]

ok ,thanks

[jediwarpraptor]

Confirming this at scale: imported 97 Claude Code JSONL sessions (~10k observations), and mem%3Aindex%3Abm25.bin in ~/data/state_store.db/ stays at ~96 bytes despite continuous index updates. Journal shows state::set timed out after 180000ms on every persistence attempt (post-#204 fix prevents the crash but not the data loss). Every restart pays a ~5 minute rebuildIndex cost during which the viewer port 3113 is unavailable.

iii v0.11.7 migration to sqlite-fts5 would resolve this.

[MarvinFS]

Confirming the in-memory-blob persistence bottleneck from a production deployment, with a concrete reproduction and an interim workaround that may help others until the SQLite migration lands.

Setup: agentmemory 0.9.24, iii 0.11.2 (the pinned engine), embeddings via an OpenAI-compatible provider at 1024 dimensions, ~1.7k memories + ~2.1k observations (≈3.8k indexed entries).

The failure: the vector half of the index is persisted by IndexPersistence.save() as a single state::set(KV.bm25Index, "vectors", vectorIndex.serialize()). At this corpus size that value serializes to ~27 MB, which never completes within the hardcoded invocationTimeoutMs: 18e4 and fails with Invocation timeout after 180000ms: state::set (same class as #204, which was made non-fatal but didn't address the size limit). The BM25 data value (~11 MB) writes fine, so the ceiling sits between the two. Net effect: BM25 persists, vectors silently don't, and since the boot rebuild only runs when bm25Index.size === 0, the vector half is never re-populated on restart — semantic search quietly degrades to keyword-only across the historical corpus.

Interim workaround (no engine change, holds until this lands): chunk the vector value in IndexPersistence.save()/load(). Split vectorIndex.serialize() into <6 MB pieces written as vectors:0..N plus a vectors:meta count, delete the legacy single vectors key, and on load read vectors:meta, fetch and concatenate the chunks (falling back to the single key, and to a rebuild if any chunk is missing). Each state::set is then well under the working ceiling. In our case the 27 MB index became 4 chunks (6+6+6+3.3 MB), persisted with zero failures, and a clean restart logs Loaded persisted vector index (3843 vectors) with no rebuild.

Happy to open a PR for the chunked-persistence stopgap if useful — though I realize the FTS5/sqlite-vec direction in this issue is the proper long-term fix.