DEV Community: Arinaymay Bhaskar

A benchmark harness that's hard to fool, starting with me

Arinaymay Bhaskar — Thu, 05 Nov 2026 13:00:00 +0000

Before optimising my Electron app, I built a benchmark suite with eleven stages. The best things it did were refusing to let me type numbers into docs, showing regressions first, and catching three of my own wrong conclusions.

The other posts in this series quote a lot of numbers: list queries dropping from 246 ms to 0.28 ms, chat taking 14 seconds, search quality of 0.467. Every one of them came from the same harness. This post is about how it's built, and mostly about the ways it stopped me from fooling myself.

What it measures

npm run bench runs up to eleven stages against MindSage, a Windows journaling app built with Electron, SQLite and local AI models:

Database: every query the screens make, at 150, 5,000 and 50,000 entries, plus reads while a background thread writes.
AI: entry enrichment, chat, inline suggestions, cold model load, embedding speed.
Chat pipeline: each of the four stages separately.
Retrieval quality: a hand-labelled test set, so search can't get faster and worse at once.
Vector search, speech-to-text, media over IPC.
The packaged app: cold start, IPC round trips, dashboard render time, dropped frames while scrolling, memory across repeated navigation.
Size: installer, packaged output, and JavaScript bundle bytes attributed to each npm package by decoding source maps.

A full run takes about 40 minutes. A database-only run takes about 6.

Rule 1: percentiles, not averages

The stats helper's opening comment explains this better than I can:

A mean hides the stalls that actually get noticed. One 400 ms freeze inside fifty 8 ms calls averages out to 16 ms and looks fine, while p95 shows it.

Every result reports p50 and p95. Warm-up runs are thrown away, because the first call to a prepared statement pays for query planning once, and that's not what a user feels while scrolling. Each scenario has a 20-second budget, so a 3-second query at 50,000 entries can't stall the suite. That budget has a cost too: one early number rested on only 13 samples, and the docs say so next to it.

Rule 2: nobody types a number

Every run is saved as JSON under a label like after-wal-and-indexes. The reports, the comparisons and the status board are all generated from those files. The issue list doesn't store numbers either. It stores where each issue's metric lives in a run:

{
  "id": "DB-1",
  "title": "No indexes on `journal_entries`",
  "severity": "high",
  "metricLabel": "`list.page1` p95 @ 50k",
  "metric": {
    "stage": "db",
    "volume": 50000,
    "path": ["results", "list.page1", "p95"],
    "unit": "ms",
    "target": 20
  }
}

So the board can't go stale and can't be "rounded up" by hand. The human part of each entry is the reasoning: what changed, why, and what it means.

Rule 3: regressions come first

The comparison report doesn't open with the wins. If anything got slower, the first thing on the page is a warning box listing it, ending with one line:

A win elsewhere does not cancel these. Decide deliberately whether each is an acceptable trade, and record the decision in the optimisation log.

That rule made me write down an accepted regression when an index made the main list 800× faster and a gallery query 30% slower. Without the rule, I'd have only seen the 800×.

Rule 4: know your noise

Early on, a change looked like it made writes 2.1× slower and background contention 2× slower, which fit a neat theory about foreign keys. The same comparison showed contention 2.5× faster at another size, which fit nothing.

So I compared the same branch with itself, no code change at all. The report said writes were 13.2× slower and listed twelve regressions. On my laptop, a single scenario can move about 2× between identical runs. Every delta in the real comparison was inside that, so it proved nothing in either direction. The log records "no detectable effect" rather than a result.

Two more ways the harness protects comparisons:

Every report prints the machine. When a Windows update landed mid-project, I re-measured unchanged code across it. The large database scenarios came out flat, so the update wasn't a confound.
Every run records the AI models. Swapping a model moves AI timings more than any code change, so an AI comparison only counts with the models held fixed.

Rule 5: a quick check on every commit, that never blocks

The pre-commit hook runs a 25-second benchmark: the database stage at 150 and 5,000 entries, 20 samples each, compared with the previous quick run. It only runs when the commit touches code the app executes, and skips merges and rebases.

It never fails a commit. With 2× noise and 20 samples, a hard gate would refuse good commits. It prints regressions loudly instead.

It earned its place. When I added an index to the journal table, the quick run showed the list query going from 9 ms to 98 ms. The index alone made things worse because of how the query grouped rows. The full story is in post 01. Without the hook I would have found that much later.

Three times it proved me wrong

"The database will throw busy errors." My own performance audit predicted it. The harness recorded zero. The real problem was readers silently waiting 200 ms behind a writer.
"Sending images over IPC is a major problem." The audit rated it as major. Measured, a full image crossed IPC in about 2 ms. I closed it with no work.
"The renderer is 76% of startup." That came from one early run. Three clean runs showed the opposite: the search server I bundled at the time took about two thirds of startup. The single-run number has a correction note next to it in the findings.

And once the harness itself was wrong. After I removed an old login argument from the app, the benchmark kept passing it. Every app-level scenario was timing an {"error": "Invalid token"} response. The dashboard read "0.30 ms, 25 bytes", which should have been the giveaway. Now I check payload sizes next to timings: if a response is suspiciously small, it's probably an error.

Writing down what's fine

The optimisation log has a section called "Measured fine, do not optimise": chat time to first token, cold start, Whisper transcription speed (about 9× real time on CPU), insert speed, memory over a session. It's as useful as the problem list, because it tells future me where not to spend a weekend.

Takeaways

Measure your noise floor by comparing code with itself, before trusting any comparison.
Generate every number in your docs from stored runs.
Put regressions at the top of every comparison, and make accepting one a written decision.
Check payload sizes next to timings. A fast response may just be a fast error.

Originally published at https://mindsageapp.pages.dev/blog/benchmark-harness.

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

Arinaymay Bhaskar — Thu, 05 Nov 2026 12:00:00 +0000

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.

Testing embedding models on a hand-labelled journal

Arinaymay Bhaskar — Thu, 05 Nov 2026 11:00:00 +0000

Every speed benchmark I had would stay green while search quietly returned the wrong entries. So I built a small labelled test set, and found that my default model ranked the wrong entry first more than half the time.

MindSage finds journal entries by meaning, not keywords. Each entry is turned into a vector by an embedding model, and a question is matched against those vectors. The chat feature (post 02) feeds its top five results to a language model, so if search is wrong, the answer is confidently wrong too.

Speed benchmarks can't see any of that. You can swap a model, change a threshold or break the indexing, and every latency number looks fine while the feature stops working. I needed a test that measures whether the right entry comes back.

The test set

I wrote 18 short journal entries , each on a clearly different subject: a running injury, a call with a sister, a failed deployment at work, insomnia, a mum's new pottery class, a dal recipe, a dentist visit, and so on. Then I wrote 15 questions and labelled by hand which entries answer each one.

The subjects are deliberately distinct. With near-duplicate entries a low score would say nothing about the code. With distinct ones, a working system should get the right entry first almost every time. This is not a research benchmark. It's a regression guard that answers "did this change break search?"

The labels are committed to the repo, so the judgement is made once and every run is scored against the same answers.

Three numbers

precision@1: how often the top result is a right answer. This is what the user sees first.
recall@5: how much of the right material is in the top five. This is what the chat model gets to read.
MRR (mean reciprocal rank): rewards getting the right entry near the top, not just somewhere in the list.

The baseline was bad

With nomic-embed-text:v1.5, the model the app ships with, recall@5 was 0.767, MRR 0.644 and precision@1 0.467. The right entry was usually somewhere in the top five, but the top hit was wrong in 8 of 15 questions. These weren't judgement calls:

Question	Top result	Should have been
"What have I written about my knee?"	the dentist entry	the running injury
"What is my mum up to these days?"	a friend moving away	the pottery class
"Anything about cooking or food I made"	the pottery class	the dal recipe
"Have I been to any medical appointments?"	a river walk	the dentist

Hypothesis 1: missing task prefixes. Wrong.

The nomic model's documentation says to prefix stored text with search_document: and questions with search_query:. The app didn't do either. That looked like the obvious bug.

Adding the prefixes made every number worse : precision@1 dropped from 0.467 to 0.333 and MRR from 0.644 to 0.544. My best guess is that the way the model is packaged already handles this, but I didn't dig further. What matters is that the "obvious fix" from the docs would have shipped a regression without this test. It's written down in the repo so nobody spends an afternoon rediscovering it.

Hypothesis 2: a different model

Next I ran Google's embeddinggemma on the same entries, questions and labels. Only the model changed.

18 entries, 15 questions, top 5. Higher is better. Runs of 27 August 2026.

Wrong top hits went from 8 of 15 to 4 of 15, and the clearly wrong ones were among those fixed. "My knee" now returns the running injury, and "my mum" returns the pottery class.

It's also nearly a drop-in. Both models produce 768-number vectors, so the vector table doesn't change. Every existing entry would still need re-embedding, because vectors from two models can't be compared.

What it costs

	nomic	embeddinggemma
Model on disk	274 MB	621 MB
Embeddings per second	9.44	7.53
Re-index 5,000 entries	8.8 min	11.1 min
precision@1	0.467	0.733

So the trade is 347 MB of the user's disk and about 2 extra minutes of background indexing at 5,000 entries, for a top result that's right 73% of the time instead of 47%. I think that's a good trade. It's a product decision, though, not a benchmark result, and I haven't shipped it yet.

How much to trust this

Not as much as the percentages suggest. precision@1 going from 0.467 to 0.733 means 7 correct top hits became 11. That's four questions. The direction is consistent across all three metrics, and the misses that disappeared were unambiguous, so I trust the conclusion. I wouldn't quote "+57%" as if it came from a large evaluation.

One thing I do trust fully: the test is deterministic. Separate runs on different days reproduced the baseline exactly, 0.767 / 0.644 / 0.467 to three decimals. That's what makes it useful as a guard. If those numbers move, something changed.

Embed a title plus summary instead of the whole entry. Long entries dilute the vector, and questions are short.
Hybrid search. A plain keyword match would get "my knee" right instantly. SQLite's FTS5 can sit next to the vector table in the same file.
Grow the test set with harder, overlapping entries, so it can tell good from very good.

Takeaways

If a feature depends on search, test search quality, not just speed. Speed tests stay green while quality breaks.
Eighteen hand-written entries were enough to catch a bad default, a wrong fix from the docs, and a better model.
Say how small your sample is. It makes the conclusion more believable, not less.

Originally published at https://mindsageapp.pages.dev/blog/embedding-models-journal.

Vector search was 0.03% of my chat pipeline

Arinaymay Bhaskar — Thu, 05 Nov 2026 10:00:00 +0000

My local "ask your journal" feature took 14 seconds to answer. I assumed retrieval was the hard part. The benchmark said retrieval was basically free, and two language model calls were the whole bill.

MindSage has a feature called Ask. You type a question like "when did my knee start hurting?", and a local model answers from your own journal entries. Everything runs on your PC: a 3B Llama 3.2 model through Ollama for text, nomic-embed-text for embeddings, and a vector index inside the app's SQLite file.

It worked, but it was slow. So I timed each stage separately instead of guessing.

The pipeline

A question goes through four stages, one after another:

Query planning. The model reads the question and returns JSON: does it need journal context, does it mention a time range, and what's the core search phrase.
Embedding. The question becomes a 768-number vector.
Vector search. The five closest entries come back.
Answer generation. The model gets the question plus those five entries and writes the answer.

Stages 2 and 3 look like the clever engineering. That's where I expected the time to go.

Where the 14 seconds went

p50 per stage, baseline run of 25 August 2026, i5-9300H laptop, llama3.2 and nomic-embed-text:v1.5. Stage medians don't add up exactly to the 14.06 s median total.

Stage	p50	Share
1. Query planning (LLM)	6.16 s	44%
2. Embedding	32 ms	0.2%
3. Vector search	4.5 ms	0.03%
4. Answer generation (LLM)	8.15 s	58%
Total	14.06 s

Retrieval, embedding and search together, was a fifth of one percent. The whole cost was two language model calls in a row, against an Ollama server that handles one request at a time. Nothing I could do to the vector index would ever show up in this number.

That result later justified a decision I wrote about in post 01: replacing the Qdrant search server with sqlite-vec, which is slower at search but removes a whole process. Making a 4.5 ms step 20 ms slower changes nothing a user can feel.

The same shape showed up twice

Saving a journal entry had the same problem. The app asks the model for metadata (title, mood score, mood tags) and then, separately, for a summary:

Enrichment stage	p50
Metadata (JSON mode)	2.82 s
Summary	1.37 s
Embedding	96 ms
End to end	6.85 s

Both features make two generations, and in both the first one only exists to produce structured data for the second. That's a design smell I didn't see until the numbers sat side by side.

One more number from the same run: the first AI call after launch took 6.40 s , of which 6.21 s was loading the model from disk. The same call warm took 224 ms. Whatever feature the user touches first pays that 28× penalty.

What I changed first: stream the answer

The quickest win doesn't make the pipeline shorter. It makes the wait feel shorter. On its own, the model's time to first token was about 400 ms. So if the answer streams into the window as it's written, the user waits for planning plus that first token, then reads while the rest arrives.

There was a catch. The answer step asks the model for JSON, so the response comes back as {"response": "…"}. You can't show raw JSON to a user, and you can't parse a half-finished JSON object either. So the stream handler pulls the partial response string out of the incomplete JSON on every token and sends only the new characters:

onToken: (_fragment, full) => {
  const partial = partialJsonString(full, "response");
  if (partial === null || partial.length <= emitted) return;
  onDelta(partial.slice(emitted));
  emitted = partial.length;
},

If the JSON fails validation, the app retries the call. A failed attempt may already have streamed half an answer, so before each retry the renderer gets a reset event and throws the partial text away. Without that, the second attempt appended itself to the first.

I haven't benchmarked "time until the first word appears" on the full pipeline yet, so I'm not quoting a number for it. That's the next metric to add.

Where it stands

The latest full run, on 20 October, measured planning at 4.13 s, embedding at 20 ms, search at 1.2 ms and the answer at 6.32 s, for 10.48 s p50 in total. That's down from 14.06 s, but I can't pin the drop on one change. LLM timings on a laptop move a lot between runs, so I treat it as "somewhat faster", not as a result.

The real fixes are still ahead, in this order:

Skip planning when it can't help. Most questions either obviously need the journal or obviously mention a date. A cheap check can handle those and save the model call for unclear ones. That's up to 4 seconds off most questions.
Send smaller context. Five whole entries go into the answer prompt. Summaries would cut the prompt reading time.
Pre-warm the model after launch, so nobody pays the 6-second cold load.
Fix retrieval quality. The same benchmark suite showed the top search hit was wrong more than half the time. So those 14 seconds were often spent reasoning over the wrong entries. That's post 03.

Takeaways

Time every stage of an AI pipeline separately before optimising any of it. The part that looks hard is rarely the part that's slow.
On local hardware the model call is the unit of cost. Count model calls before you count milliseconds.
When a feature can't get faster quickly, streaming changes how long it feels. But measure the time to the first word, or you're only guessing.

Originally published at https://mindsageapp.pages.dev/blog/local-rag-pipeline.

WAL, one index and a query rewrite: making a local journal app's SQLite stay flat at 50,000 entries

Arinaymay Bhaskar — Thu, 05 Nov 2026 09:00:00 +0000

I benchmarked my Electron journal app before touching it. The fix for the slowest list query was three small changes, and one of them made things worse until the other two landed.

MindSage is a Windows journaling app I'm building on my own. Everything runs on your PC: entries go into a SQLite file through better-sqlite3 in Electron's main process, a local model writes titles, mood tags and summaries, and a semantic index lets you ask questions about your own writing. Nothing goes to a server, so there is no backend to scale. The only thing that can get slow is the user's own laptop.

That makes the database the whole performance story. A journal grows by one or two rows a day for years, and every screen in the app reads it. So before changing anything I built a benchmark harness, measured the app as it stood, and only then started fixing. This post covers the three database changes that mattered, the one that backfired, and the regressions I chose to keep.

The harness, briefly

npm run bench seeds a fresh database at three sizes and times every query the app's screens make, reporting p50 and p95. The sizes are deliberate:

150 entries is a few months of use.
5,000 entries is a daily journal kept for about 14 years. This is the realistic worst case.
50,000 entries is not a real user. It exists to show the slope: a query that is fine at 5,000 but grows 300× by 50,000 is scanning, and it will hurt someone eventually.

Alongside the timings, the harness runs EXPLAIN QUERY PLAN on every query and counts the full table scans. Every run is saved as JSON under a label, and the comparison reports and the status board are generated from those files. Nobody types a number into the docs, so no figure can go stale.

All numbers below come from one laptop: an Intel i5-9300H with 16 GB of RAM on Windows 11, Node 24. "Before" is the run labelled phase-0-2026-10-06 and "after" is phase-1-2026-10-06, both from the same day on the same OS build.

<p><strong>The noise floor came first.</strong> Early on, I measured one branch against itself with no code change. The comparison reported <code>write.create</code> as 13× slower and flagged twelve "regressions". On this laptop a single scenario can move about 2× between identical runs. Anything smaller than that is not a result, and I stopped reporting it as one.</p>

Problem 1: reads were waiting behind the background writer

The app has two database connections to the same file: the main process serves the UI, and a background worker writes AI results and index updates. The benchmark reproduces this by reading the journal list while a worker thread writes.

The list query takes under a millisecond on its own. While the worker wrote, its p95 was 311 ms at 150 entries and 436 ms at 50,000. That's the one that mattered most, because it hit tiny journals too. SQLite's default rollback journal lets a writer lock out every reader, so the UI simply waited.

My audit notes had predicted SQLITE_BUSY errors and recommended busy_timeout = 5000. The benchmark recorded zero busy errors, and busy_timeout was already 5000 because better-sqlite3 sets that by default. Half the planned fix was a no-op. The failure was waiting, not failing.

The real fix is two lines:

db.pragma("journal_mode = WAL");
db.pragma("synchronous = NORMAL");

In WAL mode, readers keep reading the last committed state while a write is in progress. synchronous = NORMAL drops the fsync on every commit. In WAL mode that is still safe against an app crash, and the worst case is losing the last few commits on a power cut. For a journal that autosaves, I took that trade.

Reads under worker writes went from 436 ms to 0.37 ms p95 at 50,000 entries. Single writes got faster as a side effect: write.create went from 6.76 ms to 0.41 ms.

To make sure a speedup really came from WAL, every report prints the pragmas the database actually ran with. If that table had still said delete, the claim would have been invalid.

Problem 2: the busiest table had no index

Goals, categories, chats and tags all had indexes. journal_entries, the table every screen reads, had none. The baseline plan reported 41 full table scans per run. Opening the journal list at 50,000 entries cost 246 ms p95.

Every list query filters by user and hides deleted entries, then orders by date, so the obvious index is a composite one in that order:

CREATE INDEX IF NOT EXISTS idx_je_user_deleted_created
  ON journal_entries(user_id, is_deleted, created_at);

The index alone made it slower

My quick benchmark run after adding the index showed list.page1 at 5,000 entries going from 9 ms to 98 ms. Ten times worse.

The list query pulled each entry's tags with a LEFT JOIN and a GROUP BY j.id. Grouping forced SQLite to visit and sort every entry before LIMIT 10 could apply. With the new index available, the planner picked a plan that sorted the whole set twice. The index could not help a query that had to touch every row anyway.

The fix was to stop grouping. Tags now come from a correlated subquery, which runs only for the ten rows that survive the LIMIT:

-- before (simplified)
SELECT j.*, GROUP_CONCAT(t.name) AS mood_tags
FROM journal_entries j
LEFT JOIN journal_entry_tags jt ON jt.journal_entry_id = j.id
LEFT JOIN tags t ON t.id = jt.tag_id
WHERE j.user_id = ? AND j.is_deleted = 0
  AND DATE(j.created_at) >= DATE(?)
GROUP BY j.id
ORDER BY DATETIME(j.created_at) DESC
LIMIT ? OFFSET ?;

-- after
SELECT j.*,
  (SELECT GROUP_CONCAT(t.name)
     FROM journal_entry_tags jt
     JOIN tags t ON t.id = jt.tag_id
    WHERE jt.journal_entry_id = j.id) AS mood_tags
FROM journal_entries j
WHERE j.user_id = ? AND j.is_deleted = 0
  AND j.created_at >= DATE(?)
ORDER BY j.created_at DESC
LIMIT ? OFFSET ?;

Functions on a column hide it from the index

The second change in that diff is easy to miss. The date filter wrapped the column in DATE() and the sort wrapped it in DATETIME(). Once a column is inside a function, SQLite has to evaluate the function for every row, and no index on created_at can be used.

Every writer in the app already stores created_at as an ISO 8601 UTC string from toISOString(), and ISO strings sort correctly as text. So the column can be compared bare. The upper bound became created_at < DATE(?, '+1 day'), which keeps the whole of the last day, exactly what DATE(created_at) <= DATE(?) used to select.

The index, the subquery and the bare comparisons only work together. Shipped alone, the index was a regression.

The results

Full table scans per run went from 41 to 12. At 50,000 entries, p95:

p95 latency at 50,000 entries, log scale. Before: phase-0-2026-10-06. After: phase-1-2026-10-06. Same laptop, same day.

The list queries are now flat: list.page1 is 0.30 ms at 150 entries, 0.28 ms at 5,000 and 0.30 ms at 50,000. That's what an index serving the ORDER BY and stopping after one page looks like.

What I didn't fix, and the regression I kept

Two rows in that chart are not wins.

dashboard.stats went from 1.63 s to 891 ms and is still far above its 100 ms target. It runs eight aggregates over every entry for streaks and totals. An index can't make "count everything" cheap. The fix is one pass plus a cache invalidated on write, and that's still open. At 5,000 entries it's about 60 ms, so real users don't feel it yet.

gallery.random got slower, 953 ms to 1.25 s. It picks random photos with eleven image_key IS NOT NULL ... OFFSET ? queries. With the new index, each one walks the index and fetches every row just to test image_key, which turned out slower than the scan it replaced. I logged it as an accepted regression rather than holding back a change that made the main list 800× faster. The query itself needs replacing.

The comparison tool lists every measurement that got worse at the top of the report, with the line "A win elsewhere does not cancel these." I think that rule did more for the quality of this work than any single optimisation.

Problem 3: a whole search server for 5,000 vectors

Semantic search used to run on Qdrant, shipped as a sidecar process next to the app. Qdrant is very fast: 2.4 ms p95 at 5,000 vectors and 5.4 ms at 50,000, thanks to its HNSW index. But it was a second process to start, supervise and keep in sync with SQLite. It took about 0.5 s of startup, 594 MB of resident memory and 995 MB of disk at 50,000 vectors.

I replaced it with sqlite-vec, an extension that loads into the same better-sqlite3 connection. The vectors now live in a vec0 virtual table in the journal file itself:

CREATE VIRTUAL TABLE IF NOT EXISTS vec_entries USING vec0(
  embedding float[768] distance_metric=cosine,
  user_id integer partition key
);

The partition key means a search only reads the signed-in user's vectors. The table is created outside the numbered migrations, so a machine that can't load the extension still opens its journal and only search reports itself unavailable.

sqlite-vec does exact brute-force search, so it is slower than Qdrant, and I want to be upfront about that:

At 50,000 vectors unless noted	Qdrant	sqlite-vec
Search p95 at 5,000	2.4 ms	23 ms
Search p95 at 50,000	5.4 ms	192 ms
Disk	995 MB	155 MB
Resident memory	594 MB	148 MB
Upsert rate	1×	about 3×
Extra processes	1	0

Laptop runs from 6 and 20 October 2026. The targets were p95 under 100 ms at 5,000 vectors and under 200 ms at 50,000; both are met.

The trade made sense because of another benchmark. The chat feature's pipeline took about 14 s per answer, and vector search was 0.03% of it (4.5 ms). Two sequential LLM calls took the rest. Making retrieval 20 ms slower changes nothing anyone can feel. Removing a whole process, most of a gigabyte of disk and the work of keeping two stores in sync does.

It also made deletion simpler. The vector is a row in the same file, so deleting an entry and its vector happens in one transaction. Before, the two stores could disagree.

On a 4-core cloud sandbox, with the vector file memory-mapped, search came in at 9 to 11 ms at 5,000 vectors. That's a different machine, so I don't compare it with the laptop numbers above.

A WAL gotcha that matters for a journal

A journal app has to delete properly. When someone empties the trash, the text should leave the file, not linger in free pages. So the connection also sets secure_delete = ON, which overwrites deleted content with zeros.

WAL complicates that. In WAL mode, those zeroed pages are written to mind-sage.db-wal first. Until a checkpoint copies them back, the main file still holds the old page images with the deleted text, and the WAL may hold older frames too. So everything that deletes for good, like the trash purge or deleting a profile, now runs a wal_checkpoint(TRUNCATE) afterwards. It uses a short busy timeout, so a reader holding the WAL can't freeze the main process for five seconds.

It's an easy one to miss: WAL and secure delete each do their job, and only together do they leave deleted text behind for a while.

The installer, while I was there

The same harness tracks disk footprint, so packaging fixes got measured too:

The target is under 180 MB. Most of what's left is the bundled Whisper speech model and Electron itself.

What I'd tell myself at the start

Measure the noise before the change. Run the same code against itself. If you skip this, you will celebrate noise.
Count full scans, not just milliseconds. At 150 entries nearly everything is fast. The query plan shows what will break at 5,000.
An index is half a fix. If the query has to group or sort every row, or wraps the column in a function, the index can't help and may even hurt.
Profile the whole pipeline before optimising the part that looks clever. Vector search looked like the hard part of chat. It was 0.03% of it.
Write down the regressions you accept. A change can be right overall and still make one query slower. Say so in the log.

Next up is dashboard.stats. The chat pipeline, where the 14 seconds actually go, is post 02.

Originally published at https://mindsageapp.pages.dev/blog/sqlite-50000-entries.