
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:
| id | score | content |
|---|---|---|
| 1 | 2.7486 | AdSense 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:
| id | score |
|---|---|
| 11 | 2.6215 |
| 1 | 0.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:
| query | result |
|---|---|
流量主 收入 | 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_indexindexes 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
- Building an AI Agent Memory Layer with DuckDB — the first post in this series: how memory is written, decayed, and merged (the vector-recall side); this one adds the literal-term recall (BM25) half
- Build an AI Agent with DuckDB as Its Brain — the memory table is one layer of agent state; this one covers letting an LLM query the store in natural language
- turbovec Offline RAG: local embeddings + full-text two-way recall — the measured vector side of the “when not to use it” list above