MindSage engineering notes · 04

37,500 reads per search: what memory-mapping fixed in sqlite-vec, and what it couldn't

After moving my app's vector search into SQLite, a search over 50,000 vectors missed its target. Profiling showed a quarter of the time was file reads the page cache never saw. The rest was a loop no setting could touch.

By Arinay · November 2026 · About 8 minutes

In post 01 I replaced the Qdrant search server in MindSage with sqlite-vec, so the vectors live in a vec0 table in the journal's SQLite file. sqlite-vec does exact, brute-force search: every query compares against every vector. On my Windows laptop that measured 182 ms p50 and 192 ms p95 at 50,000 vectors. The ticket's target was p95 under 100 ms.

This post is the investigation. The short version: one setting cut about 25 ms, three ideas did nothing, one more would make it much faster at a recall cost, and in the end I changed the target. That last part needs justifying, so I'll get to it.

The experiments

Why a bigger cache did nothing

The usual first move for a slow SQLite read is a bigger page cache. It changed nothing, which was the clue.

vec0 stores vectors in chunk blobs of about 3 MB each. A blob that big spills across overflow pages, and SQLite reads those straight into the caller's buffer, one read() call per 4 KB page. That works out to about 37,500 system calls per search, and those reads go around SQLite's page cache entirely. Making the cache bigger can't help with reads that never use it.

Memory-mapping the file changes how those reads happen. With PRAGMA mmap_size set, each page read becomes a memory copy from a mapped region instead of a system call:

// One line, applied when the connection opens.
db.pragma(`mmap_size = ${MMAP_BYTES}`); // 1 GiB

Interleaved on the same file, 200 samples each, the mapped version measured 81 / 115 ms against 106 / 140 ms. About 25 ms of every search was I/O overhead. At 5,000 vectors, search went from 11–12 ms to 9–11 ms p50.

The 80 ms floor

To find the floor, I loaded all vectors into memory with no file at all. Search still took 81 ms p50. That's the distance loop itself: comparing one vector against 50,000.

Nothing about the query moved it. Asking for 10, 50 or 200 results cost the same. The app's full search function (joining results back to entries, building payloads) added about 3 ms on top of the bare query, within noise. Float32 cosine, float32 L2 and int8 cosine all measured 81–83 ms in memory. The prebuilt extension reports no SIMD build flags, so the loop costs the same whatever the number format.

Two ideas I didn't ship

int8 vectors

Storing vectors as 8-bit integers is the standard way to speed up vector search. Here it saved nothing once the file was mapped (74 / 116 ms against 75–77 / 89–90 ms for float32 in the same run). It would also have needed a new table, a full re-index, and rescaling the app's vectors, because vec_quantize_int8(…, 'unit') clips values outside −1 to 1. Recall@10 against exact search was 0.998, so quality wasn't the problem. It just didn't pay.

Binary quantisation with an exact rerank

This one is clearly faster. Reduce each vector to one bit per dimension, scan those bits (2.6 ms at 50,000), then rerank the best candidates with exact float32 distance. Reranking costs about 0.25 ms per candidate:

The catch: those recall numbers are on random and synthetic clustered vectors, not real embeddings. Losing even 6% of the right results in a journal search is a real cost. I won't ship it until recall is measured on real nomic vectors, with a migration that keeps the float vectors until the new index proves itself.

Side effects of the mapping

The mapping applies to the whole journal file, so other queries got faster too. At 50,000 entries, one run each, p50: dashboard.stats 1,002 to 663 ms, the random gallery 531 to 150 ms, dashboard.data 107 to 37 ms. Nothing got slower.

Two costs, written down so they don't surprise anyone:

• Memory looks higher. Resident memory after a 50,000-vector search rose from 185 to 330 MB on Linux, because mapped pages count there. They're file pages the OS can drop, and Windows Task Manager's default column leaves them out. But my own app-level memory benchmark will move.
• A disk error becomes a crash. If the mapped file hits an I/O error, like a failing disk or a profile folder on a network share, the process dies instead of getting an error back. The mapping is only used for reads, so writes and the journal itself aren't put at risk by it.

Changing the target

After all this, p95 at 50,000 vectors was 96–117 ms on the test machine, and the floor is the 80 ms loop. My laptop was about 1.7× slower than this machine before the change, so it would land around 165–200 ms p95. I haven't measured that on Windows yet.

So I revised the target to p95 under 100 ms at 5,000 vectors (about 14 years of daily entries) and under 200 ms at 50,000 on my Windows machine. Here's why that isn't moving the goalposts to declare victory:

• A search comes right after an embedding call of about 100 ms.
• In chat, it comes right before several seconds of text generation (post 02).
• 50,000 entries isn't a real user. It exists to show how the cost grows.

If 100 ms at 50,000 ever has to hold, that's its own ticket: binary prefilter plus exact rerank, measured on real embeddings, or a SIMD build of the extension for all three platforms.

Takeaways

• When a cache change does nothing, find out why. Here it meant the reads were bypassing the cache.
• Measure the floor first. Running fully in memory told me which costs could move and which couldn't.
• A quantisation trick is only free if you measure recall on your real data.
• Changing a target is fine if you write down why, and what it would take to hit the old one.