RFC: optional SQLite tiered storage backend for serve (~17–64× lower query latency, byte-identical results)

[papinto]

Summary

I'd like to propose an optional --format sqlite storage backend for serve (JSON stays the default; serve auto-detects by file extension). In a working prototype it delivers ~17× lower p50 query latency at 54k nodes, ~21× at 177k, and ~64× at 271k nodes, with byte-identical results (Jaccard 1.000 vs the current JSON/networkx path on 8 NL queries). The relative win grows with graph size because tiered latency is index-bounded while the JSON scan degrades super-linearly. I'd like a read on whether you'd accept this upstream before I invest in a mergeable PR.

Motivation — JSON is being used as a query database

_load_graph (serve.py) parses the entire graph.json into networkx, then every query brute-force scans it. _score_nodes (serve.py) loops over every node per query (for nid, data in G.nodes(data=True), O(nodes × terms)), so query latency scales with total node count regardless of where the answer lives. On a large monorepo this is the dominant cost. Concretely, on our graphs:

Backend / scale	Fidelity (Jaccard)	p50 @ conc 1	q/s @1	load	Notes
JSON / networkx — 54k nodes	— (baseline)	428 ms	2.3	0.97 s	current
SQLite tiered — 54k nodes	1.000	25 ms	36	~0 s (mmap)	~17×
JSON / networkx — 177k nodes	— (baseline)	1730 ms	0.6	3.16 s	current
SQLite tiered — 177k nodes	1.000	83.5 ms	~7–11	~0 s (mmap)	~20.7×
JSON / networkx — 271k nodes	— (baseline)	3705 ms	0.28	4.03 s	current
SQLite tiered — 271k nodes	1.000	57.6 ms	~10–15	~0 s (mmap)	~64×

The relative win grows with N (JSON's O(N) scan degrades super-linearly: 428→1730→3705 ms, while tiered stays index-bounded at ~25–85 ms) — the opposite of what large repos need today.

Design (clean seams already exist)

• Introduce a StorageBackend abstraction at the single load seam (_load_graph, serve.py) + a new exporter alongside the existing ones. AST extraction is unchanged. export.py already exposes a uniform to_<format>(G, output_path) seam (to_json, to_cypher [Neo4j], to_graphml, to_obsidian, to_canvas, to_svg) — a to_sqlite slots into the same pattern.
• The real work is reimplementing the scorer as FTS5 MATCH + a bounded recursive-CTE BFS. (NB: there's no SQLite/FTS5 in the codebase today — this is net-new; the to_cypher exporter is the closest existing precedent for a graph→external-store serialization.)
• "Tiered" is the key insight — a naive storage swap is not enough. A faithful 1:1 swap was only ~2.9× because common trigrams (e.g. "component" → 22k nodes) make the substring candidate set 30–56% of N. The tiered design does indexed GLOB 'term*' exact/prefix retrieval (where graphify's seeds actually come from — _pick_seeds prunes <20% of top score) + a small bm25 safety net, then runs the full existing graphify rescore on the small candidate set. That preserves exact fidelity.
• Cannot use FTS5 native bm25 as the ranker — fidelity collapses to ~0.25 because graphify's scoring is exact/prefix-dominated (1000×/100× bonuses), unlike bm25 TF. The rescore must stay graphify's own.

Secondary wins (measured)

• Startup ~instant (mmap open vs 2–3 s parse) → kills the hot-reload parse blip (_maybe_reload, serve.py) where the first post-change query eats the full re-parse under a lock.
• Memory per replica drops and shares: a single-threaded replica is ~46 MB private + ~111 MB shared-once (mmap'd db + DLLs), so N replicas ≈ db-shared-once + N×46 MB rather than N× the full resident set.
• Conversion is cheap: 192 MB graph.json → 266 MB .db in ~24 s.
• Row-level incremental update (177k-node graph): applying a changed file is a DELETE+INSERT of its node/edge rows + targeted external-content FTS5 maintenance + degree recompute on the affected set — ~69 ms for 1 file, ~647 ms for 20, vs a ~6.5 s whole-file graph.json rewrite (~93×), with fidelity preserved (Jaccard 1.000) and WAL readers not blocked by the writer. Cost scales with changed rows, not total N — so serve could hot-apply commits cheaply instead of swapping whole files.

Honest non-goals / what it does NOT fix

• Not a concurrency fix. Throughput stays flat across thread concurrency — most time is still the Python rescore/BFS loop holding the GIL. The scaling lever remains scale-by-process. (This is inherent to CPython, not a storage choice.)
• Not a cross-folder-traversal fix — that needs a connected whole-repo extract, orthogonal to storage.

Bonus it would unlock

A single .db with path-prefix scoping could dissolve the per-folder-vs-whole-repo fork (one graph, bounded latency via the index, scoped queries) — which also directly serves #569. And sqlite-vec would make semantic query cheap later.

Ask: Would you accept an optional --format sqlite (JSON default, fully opt-in) along these lines? If so I'll prepare a PR with the StorageBackend seam + tiered scorer + a fidelity test harness (the 8-query Jaccard==1.000 check I used). Happy to share the bench/fidelity harness if useful.

[papinto]

Cross-linking #1056 — these complement rather than compete

Flagging #1056 (NeuG parallel storage engine) for anyone reading this RFC, since they live in the same problem space — and I think it's worth being explicit that they solve different problems and it's genuinely nice to see both approaches in flight.

The shared ground: both are fundamentally about making graphify hold up on large codebases — that's the core motivation behind this RFC, and it's clearly behind #1056 too. The NetworkX + graph.json default starts to strain (memory, full-graph reloads, query latency) exactly when the graph gets big, and both proposals are answers to that. So this isn't a disagreement about the problem — it's two different bets on the solution.

The way I'd frame the split:

• feat: add NeuG as parallel graph storage engine with Cypher query support #1056 (NeuG) broadens what you can ask. It adds a real graph database alongside NetworkX with a declarative Cypher surface (graphify cypher, cypher_query) and native O(delta) incremental MERGE. If you want expressive graph queries and graph-DB semantics — and you're willing to take on an external engine — that's the ceiling-raiser.
• RFC: optional SQLite tiered storage backend for serve (~17–64× lower query latency, byte-identical results) #1297 (this RFC) accelerates what you already ask. It keeps the existing query/serve/MCP surface exactly as-is, returns byte-identical results, adds no new query language, and pulls in no external dependency (SQLite is stdlib). The win is ~17–64× lower latency on the queries graphify already supports — a drop-in speed/footprint upgrade for users who don't want to change anything about how they query.

So one expands the capability surface; the other speeds up the current one with zero behavior change. Different tradeoff points on the "how much do I want to adopt" axis — and both are strictly optional layers alongside the NetworkX default, so they're not mutually exclusive. A user could even reach for NeuG for ad-hoc Cypher exploration and the SQLite tier for the hot serve path.

Not asking to prioritize one over the other — just that they be considered as covering different needs rather than as an either/or. Happy to align naming/config conventions with #1056 if that helps keep the "optional backend" story coherent.

[longbinlai]

Cross-linking #1056 — these complement rather than compete

Flagging #1056 (NeuG parallel storage engine) for anyone reading this RFC, since they live in the same problem space — and I think it's worth being explicit that they solve different problems and it's genuinely nice to see both approaches in flight.

The shared ground: both are fundamentally about making graphify hold up on large codebases — that's the core motivation behind this RFC, and it's clearly behind #1056 too. The NetworkX + graph.json default starts to strain (memory, full-graph reloads, query latency) exactly when the graph gets big, and both proposals are answers to that. So this isn't a disagreement about the problem — it's two different bets on the solution.

The way I'd frame the split:

• feat: add NeuG as parallel graph storage engine with Cypher query support #1056 (NeuG) broadens what you can ask. It adds a real graph database alongside NetworkX with a declarative Cypher surface (graphify cypher, cypher_query) and native O(delta) incremental MERGE. If you want expressive graph queries and graph-DB semantics — and you're willing to take on an external engine — that's the ceiling-raiser.
• RFC: optional SQLite tiered storage backend for serve (~17–64× lower query latency, byte-identical results) #1297 (this RFC) accelerates what you already ask. It keeps the existing query/serve/MCP surface exactly as-is, returns byte-identical results, adds no new query language, and pulls in no external dependency (SQLite is stdlib). The win is ~17–64× lower latency on the queries graphify already supports — a drop-in speed/footprint upgrade for users who don't want to change anything about how they query.

So one expands the capability surface; the other speeds up the current one with zero behavior change. Different tradeoff points on the "how much do I want to adopt" axis — and both are strictly optional layers alongside the NetworkX default, so they're not mutually exclusive. A user could even reach for NeuG for ad-hoc Cypher exploration and the SQLite tier for the hot serve path.

Not asking to prioritize one over the other — just that they be considered as covering different needs rather than as an either/or. Happy to align naming/config conventions with #1056 if that helps keep the "optional backend" story coherent.

We will look into your proposal. Good to share these cool ideas.

[BingqingLyu]

Cross-linking #1056 — these complement rather than compete

Flagging #1056 (NeuG parallel storage engine) for anyone reading this RFC, since they live in the same problem space — and I think it's worth being explicit that they solve different problems and it's genuinely nice to see both approaches in flight.

The shared ground: both are fundamentally about making graphify hold up on large codebases — that's the core motivation behind this RFC, and it's clearly behind #1056 too. The NetworkX + graph.json default starts to strain (memory, full-graph reloads, query latency) exactly when the graph gets big, and both proposals are answers to that. So this isn't a disagreement about the problem — it's two different bets on the solution.

The way I'd frame the split:

• feat: add NeuG as parallel graph storage engine with Cypher query support #1056 (NeuG) broadens what you can ask. It adds a real graph database alongside NetworkX with a declarative Cypher surface (graphify cypher, cypher_query) and native O(delta) incremental MERGE. If you want expressive graph queries and graph-DB semantics — and you're willing to take on an external engine — that's the ceiling-raiser.
• RFC: optional SQLite tiered storage backend for serve (~17–64× lower query latency, byte-identical results) #1297 (this RFC) accelerates what you already ask. It keeps the existing query/serve/MCP surface exactly as-is, returns byte-identical results, adds no new query language, and pulls in no external dependency (SQLite is stdlib). The win is ~17–64× lower latency on the queries graphify already supports — a drop-in speed/footprint upgrade for users who don't want to change anything about how they query.

So one expands the capability surface; the other speeds up the current one with zero behavior change. Different tradeoff points on the "how much do I want to adopt" axis — and both are strictly optional layers alongside the NetworkX default, so they're not mutually exclusive. A user could even reach for NeuG for ad-hoc Cypher exploration and the SQLite tier for the hot serve path.

Not asking to prioritize one over the other — just that they be considered as covering different needs rather than as an either/or. Happy to align naming/config conventions with #1056 if that helps keep the "optional backend" story coherent.

Hi @papinto, thanks for the thoughtful framing — completely agree these are complementary, not competing. Your distinction captures it well: #1297 is a drop-in speed upgrade for the existing query surface, while #1056 expands what's queryable (Cypher, multi-hop, pattern matching). They sit at different points on the adoption curve and can coexist.

Happy to align on naming/config conventions to keep the "optional backend" story coherent. For our side, the NeuG layer is gated behind graphify[neug] extra and activated by the presence of graph.db — we can coordinate with the pattern you're using on the SQLite side.