Featured image of post Full-Text Memory Recall Without a Vector DB: DuckDB FTS + BM25, Hands-On

Full-Text Memory Recall Without a Vector DB: DuckDB FTS + BM25, Hands-On

When an agent's memory grows to thousands of entries, how do you retrieve the right few? We run DuckDB's built-in FTS extension end to end: one PRAGMA builds a BM25 index, and we measure two real pitfalls (snapshot-only indexing, unsegmented Chinese text) plus 10k-row timings — 0.19s to build, 0.035s to query.

DuckDB FTS + BM25 memory recall: 10,000 memories indexed in 0.19s, one recall query in 0.035s

Figure 1: Measured DuckDB FTS extension results — a 10,000-entry memory table is indexed in 0.19 seconds, and a single BM25 recall query takes 0.035 seconds.

The more memories you store, the harder they are to retrieve

An agent’s memory pipeline has three stages: writing (what to keep), forgetting (what to drop), and recall (how to find it when you need it). The first two decide what remains in the store; the last decides whether what remains is actually usable.

Once memory accumulates to thousands or tens of thousands of entries, “retrieve the right few” becomes a real problem. The blunt instrument is LIKE '%keyword%': no relevance ordering (200 hits look the same as 2), synonyms and word-order variation all miss, and it degrades to a full scan as the table grows. The other extreme is a vector store for semantic recall — accurate, but it means running another service and paying another embedding bill, which is heavy equipment for a personal agent that just wants to work.

The middle path is full-text search with BM25 ranking. DuckDB ships an fts extension (official docs): one PRAGMA builds the index, one match_bm25 call ranks the hits (BM25 is the classic Okapi retrieval model; DuckDB’s implementation follows the same spirit as SQLite FTS5). This post runs that path end to end with measured numbers: English recall, two real pitfalls (snapshot-only indexing, unsegmented Chinese text), and timings at the 10,000-row scale.

Environment: DuckDB v2.0.0 (Python, INSTALL fts; LOAD fts;; the fts extension has been available since 1.5 with identical usage — numbers here are from 2.0.0). The sample memory table is synthetic (10 rows, modeled on real agent memory entries); every output below is an unedited measured result.

Step one: table and index

CREATE TABLE memories(id INTEGER, created_at DATE, kind VARCHAR, content VARCHAR);
-- insert 10 sample memories (an AdSense rejection, Payoneer payouts, blog analytics ...)
PRAGMA create_fts_index('memories', 'id', 'content', overwrite=1);

create_fts_index creates a fts_main_memories schema in the database; recall goes through its match_bm25(document_id, query) macro: a hit returns a BM25 score, a miss returns NULL. So the recall query has a fixed shape — score in a subquery, filter and order outside:

SELECT id, score, content FROM (
  SELECT id, content, fts_main_memories.match_bm25(id, 'adsense low value content') AS score
  FROM memories
) WHERE score IS NOT NULL ORDER BY score DESC;

Measured result for adsense low value content:

idscorecontent
12.7486AdSense application for duckdblab.org was rejected for low value content

Of 10 rows, exactly one hits — and it is the right one. Two more queries behave the same way: kdp royalties payment → id=2 (Payoneer payouts, 1.8324); traffic revenue → id=3 (blog analytics, 1.6554). Even on a tiny corpus, BM25’s discriminative power is enough: irrelevant entries don’t even make the shortlist.

Pitfall one: the index is a static snapshot — new memories are invisible

The FTS index is a snapshot of the moment it was built. Measured: insert a new memory after building the index —

INSERT INTO memories VALUES (11, '2026-11-10', 'fact',
  'AdSense resubmission planned for November 16 after ads.txt authorization');

Query adsense resubmission november immediately: the new row is absent; only the old id=1 comes back (0.9162). Re-run PRAGMA create_fts_index(..., overwrite=1) and query again:

idscore
112.6215
10.7180

The new entry now ranks first. So an FTS-based memory recall path needs an explicit “rebuild index” step on the write path: rebuild after every write when the corpus is small (timings below make this cheap), and batched periodic rebuilds when it is large. If you expect it to maintain itself like a regular index, it will silently drop your newest memories — with no error, which is the hardest kind of bug to find.

Pitfall two: unsegmented Chinese text gets zero hits

DuckDB FTS tokenizes on whitespace and punctuation. English is fine; an unsegmented Chinese sentence is one “token”, so:

-- notes table holding raw Chinese memory text, indexed:
-- query '流量主 收入'  → NO HITS
-- query '流量主收入'  → NO HITS

Both phrasings return nothing. The fix is to segment at ingest time: split Chinese content into a space-separated token string, store it in a dedicated column (e.g. content_seg), and index that column. Measured on the same data with the index pointed at the segmented column:

queryresult
流量主 收入id=1 (1.1107)
公众号 质检id=4 (0.8082) > id=1 (0.3197)

The second row is the telling one: id=4 contains both “公众号” and “质检” and outranks id=1, which contains only “公众号” — BM25’s term-frequency and inverse-rarity weighting works on segmented Chinese exactly as it does in English. The segmenter can be anything (Jieba, a custom dictionary, even the LLM doing it on the write path); the point is it must happen at ingest, not at query time.

Scale: what 10,000 memories look like

We generated 10,000 synthetic rows (uniform word distribution, so we read timings, not scores):

  • Build index: 0.19 s
  • Single BM25 recall query: 0.035 s

These are numbers from one sandbox machine, not a universal constant, but the order of magnitude is the point: personal-agent memory scales (hundreds to tens of thousands of entries) are trivial for FTS. Rebuilding is cheap enough to do a full rebuild after every batched write — no incremental-index machinery required.

When not to use it

  • Semantic recall: BM25 only sees literal terms. A memory that says “payout account” won’t match a query asking “how do I withdraw” — that is vector search’s job, and FTS cannot substitute. The pragmatic design is two-way recall: BM25 for exact terms, vectors for meaning, merge and dedupe. For the vector side we have hands-on local RAG with turbovec.
  • Write-heavy workloads: the index is a snapshot; high write rates mean high rebuild rates. Ten thousand rows at 0.19 s is nothing; a million rows is a different conversation. Check rebuild windows before committing.
  • Chinese without preprocessing: as shown above, no segmentation, no index.
  • Cross-field weighting: create_fts_index indexes multiple columns, but per-field weight tuning is far less fine-grained than Elasticsearch/OpenSearch. Memory recall rarely needs that level; migrate only if you actually do.

Bottom line

The memory pipeline is write (layering) → forget (decay) → recall (search). The first two answer “what stays in the store”; this one answers “can I find it when I need it”. DuckDB FTS sits in a clear place: zero new services, one PRAGMA to go live, millisecond-scale recall at the ten-thousand row mark; the costs are manual snapshot rebuilds, hand-rolled Chinese segmentation, and no semantic recall. For most personal agents, that trade is worth it.

Go deeper

📺 Watch video tutorials → OLAP Studio YouTube

Subscribe for more DuckDB & AI automation tutorials