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.
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.
Machine note. These experiments ran on a 4-core Linux cloud machine (Xeon 2.8 GHz, 15 GB), with random 768-dimension vectors, top 15, one user. It isn't my laptop, but runs are easy to repeat there. Ranges below are three runs of 100 samples each. I don't compare these figures directly with the laptop's.
The experiments
| Change, at 50,000 vectors | p50 | p95 | Kept? |
|---|---|---|---|
| Starting point | 104–108 ms | 118–133 ms | |
cache_size 256 MB (default 16 MB) |
104 ms | 123 ms | No, no effect |
mmap_size 1 GiB |
78–81 ms | 96–117 ms | Yes |
| Top 10 / 50 / 200, file mapped | 83 / 79 / 87 ms | 108 / 101 / 107 ms | No, no effect |
| int8 vectors, file mapped | 74 ms | 116 ms | No (below) |
| float32, fully in memory, no file I/O | 81 ms | 94 ms | The floor |
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:
| Candidates reranked | p50 | p95 | Recall@10, random | Recall@10, clustered |
|---|---|---|---|---|
| 100 | 28–29 ms | 46–49 ms | 0.940 | 0.998 |
| 400 | 64 ms | 0.970 | 1.000 |
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.
Originally published at https://mindsageapp.pages.dev/blog/sqlite-vec-mmap.
Top comments (0)