Summary

Hybrid search moves far too much data and runs far too long on large projects. In production we measured 40 to 350 MB of database egress per search and searches that take up to 100 seconds. The cause is not one bad query. It is how the algorithm is shaped: it ranks whole rows in Python, carries full note text through every step, and widens its candidate window with a loop that cannot reach its own stop condition.

This issue proposes rebuilding search around one rule: rank by id inside the database, and fetch text only for the page we return.

What we measured (Basic Memory Cloud, Postgres + pgvector, 2026-10-10)

• Hourly egress for two heavy projects follows search calls, not reads or writes. A regression over 48 hours gives about 40 to 60 MB per search on one project and about 350 MB per search on the other. A script making 640 note reads an hour adds almost nothing.
• 55% of production searches (2,644 of 4,790) ran the vector window loop two or more times. 969 ran it four or more times. One search ran six rounds: 1.7, 2.7, 7.5, 8.6, 19 and 22 seconds, 101 seconds in total.
• In one large project: 7,136 notes, an average search_index.content_snippet of 41,522 characters (the "snippet" is the whole note), and about 52 vector chunks per note (373K chunks).

Why it happens

1. Every MCP search counts as filtered. search_notes sets entity_types=["entity"] when the caller gives no types (mcp/tools/search.py). That makes has_filters true on every default search.
2. The widening loop compares chunks to notes. _candidate_window stops when window.admitted >= candidate_limit. admitted counts unique search rows, but candidate_limit is a chunk budget (260). With about 52 chunks per note, 260 chunks resolve to a few dozen notes, so the loop doubles: 260, 520, 1,040, up to 50,000.
3. Each round starts over. Every round re-runs the vector query, re-reads chunk_text for every chunk, fetches the full search_index row (including the whole note in content_snippet) for every candidate, then runs an FTS filter pass that returns content_snippet again for the same rows.
4. The FTS leg also returns whole notes. It returns content_snippet for up to candidate_limit rows (about 260 × 41 KB, roughly 10 MB) just to compute a fused ranking, and it computes ts_rank over each note's full document vector plus a per-row subquery over its chunks.
5. Filters run in Python, after retrieval. The vector query ranks by distance alone, and type, note type, tag, date and metadata filters are applied afterwards through a second FTS pass. That is what forces the widening loop in the first place.
6. Ranking units do not match. FTS ranks search_index rows (entities, observations, relations), FTS chunks rank parts of rows, and vector chunks rank parts of entities, observations and relations. Fusion has to reconcile three granularities in Python.

Fixing the loop's stop condition alone would cut the worst cases, but the full-text-in-every-step design would still ship megabytes per search.

Proposed design

1. One retrieval unit: the chunk

• Keep a single chunk table that both FTS and vector search read: chunk(id, project_id, entity_id, source_type, chunk_index, text, tsvector, embedding), or two tables that share one chunk id.
• Copy the filterable note fields onto the chunk row (or keep them in a narrow note_facets table joined by entity_id): note type, tags, status, created and updated time, file path, and the metadata fields we filter on.
• search_index stops holding the whole note. If we need a stored preview, cap it (for example 2,000 characters).

2. Filters are SQL predicates in both legs

• Vector leg: WHERE project_id = ANY(:projects) AND source_type = 'entity' AND <filters> ORDER BY embedding <=> :q LIMIT :k. pgvector's iterative HNSW scan (already enabled with relaxed_order) is built for filtered k-NN, so the database keeps scanning until it has k matching rows. No widening loop in Python.
• FTS leg: the same predicates, ts_rank on chunk tsvectors only, LIMIT :k.
• SQLite: sqlite-vec vec0 metadata columns support the same filtered KNN. Where a filter cannot be pushed down, fall back to one bounded overfetch (for example 4× k) with no retry loop.

3. Fusion happens in one SQL statement and returns ids only

WITH fts AS (
  SELECT entity_id, max(rank) AS score, (array_agg(id ORDER BY rank DESC))[1] AS best_chunk
  FROM (<fts top-k chunks>) GROUP BY entity_id
),
vec AS (
  SELECT entity_id, max(similarity) AS score, (array_agg(id ORDER BY similarity DESC))[1] AS best_chunk
  FROM (<vector top-k chunks>) GROUP BY entity_id
)
SELECT entity_id, <fusion score>, best_chunk ...
FROM fts FULL OUTER JOIN vec USING (entity_id)
ORDER BY score DESC, entity_id
LIMIT :page_end + 1;

• The result is a ranked list of (entity_id, score, best_chunk_id). No note text leaves the database at this stage.
• Keep the current fusion formula at first so ranking quality is unchanged. RRF can be evaluated separately against the feat: upsert_note tool — semantic dedup before write using existing embeddings #1259 benchmark.

4. Late materialization: text for the final page only

• After ranking, fetch title, permalink, file path, note type and the excerpt for the page_size rows being returned.
• Excerpt = the best chunk (or top 2 to 3 chunks) for that note, capped. Never the full note. Callers that want the full note already call read_note.

5. Reranking reads bounded text

• Rerank the top reranker_candidates notes using their best chunk text, capped at max_document_chars. That is the only extra text fetch, and it is bounded by candidates × max_document_chars.

6. Observations and relations

• Default search returns notes. Observation and relation chunks only enter when the caller asks for those types, and that becomes a source_type predicate in SQL, not a post-filter.
• Today observation text is embedded twice: inside the note's chunks and as its own chunks. Decide whether to keep separate observation chunks (useful for categories) or drop them from the vector index.

7. Pagination and counts

• Order by (score DESC, entity_id) so pages are stable. Page N re-runs the query with a larger k, capped (for example 1,000), instead of keeping state.
• Use LIMIT page_end + 1 to report whether there are more results. Do not run an exact count for hybrid search.

8. Hard budgets and instrumentation

• Every search has fixed caps: k_fts, k_vec, page size and excerpt size. Bytes returned per search are bounded at roughly page_size × excerpt cap plus ids.
• One span per search with fts_k, vec_k, rows admitted, rows returned, and total bytes of text materialized, so a regression shows up in a dashboard instead of an egress bill.

Expected effect

• Data per search drops from tens or hundreds of MB to under about 100 KB (ids plus about 10 excerpts).
• Search time stops growing with note size and chunk count. No multi-round searches.
• The default entity_types=["entity"] filter stops costing anything, because it is a WHERE clause.
• The same design works on SQLite and Postgres.

Suggested order of work

1. Stopgap (small): make _candidate_window count admitted rows against a row budget, not a chunk budget, and push source_type into the vector query. Stop selecting content_snippet in _fetch_search_index_rows_by_ids and in the filter pass. This removes most of the egress quickly.
2. Schema: add source_type and the filterable note fields to the chunk table. Cap content_snippet. Backfill with a migration.
3. New search path: filtered top-k in each leg, SQL fusion, late materialization, bounded rerank. Ship it behind a setting and compare against the current path on the feat: upsert_note tool — semantic dedup before write using existing embeddings #1259 benchmark set.
4. Remove the old path: the widening loop, the FTS filter pass, and the whole-row hydration.

Open questions

• RRF or the current max + 0.3 × min fusion? Measure on feat: upsert_note tool — semantic dedup before write using existing embeddings #1259 before changing it.
• Keep separate observation chunks in the vector index or not?
• One unified chunk table, or keep the FTS chunks and vector chunks separate and share ids?
• The weak-match floor in Hybrid search and the similar-notes advisory return weak vector matches #1718 fits naturally into the fused SQL (require lexical support or a higher vector floor when FTS finds nothing). Do it here or separately?

Related

• Hybrid search and the similar-notes advisory return weak vector matches #1718 hybrid search returns weak vector matches
• Vector/hybrid filter intersection drops candidates beyond VECTOR_FILTER_SCAN_LIMIT #1431 filter intersection by candidate keys (the current filter pass)
• Avoid duplicate hybrid/vector retrieval for stable reranker pagination #1557 fixed rerank prefix
• Vector search silently drops results when entity and relation search_index ids collide #982 (type, id) keys across row types
• Add a rerank stage to search: ~half of LoCoMo benchmark misses are ranking failures, and there is ~20x latency headroom #950 reranking