asg017

37,500 reads per search: what memory-mapping fixed in sqlite-vec, and what it couldn't --- title: 37,500 reads per search: what memory-mapping fixed in sqlite-vec, and what it couldn't published: true date: 2026-11-05 12:00:00 UTC tags: canonical_url: mindsageapp.pages.dev --- !mindsageapp.pages.dev _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. <aside><p><strong>Machine note.</strong> 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.</p></aside> 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 mindsageapp.pages.dev._

WAL, one index and a query rewrite: making a local journal app's SQLite stay flat at 50,000 entries --- title: WAL, one index and a query rewrite: making a local journal app's SQLite stay flat at 50,000 entries published: true date: 2026-11-05 09:00:00 UTC tags: canonical_url: mindsageapp.pages.dev --- !mindsageapp.pages.dev _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. <aside> <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> </aside> 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: !Chart from the original post _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 | <caption>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.</caption> 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: !Chart from the original post 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 1. Measure the noise before the change. Run the same code against itself. If you skip this, you will celebrate noise. 2. Count full scans, not just milliseconds. At 150 entries nearly everything is fast. The query plan shows what will break at 5,000. 3. 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. 4. 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. 5. 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 mindsageapp.pages.dev._

Nulab Backlogの検索をローカルAIで拡張した — Ollama・SQLiteで意味検索を実装する 🎯 はじめに 以前、Backlogの課題とドキュメントを fzf で横断検索するCLI「bkfz」を作った。 zenn.dev しばらく使っていて、キーワード検索では見つからない場面があると分かった。「移行のあとにログインできなくなった課題があった気がする」と中身は覚えているのに、件名の言葉を思い出せないときだ。キーワード検索は、打った語が本文にそのまま含まれていないとヒットしない。 そこで、意味の近さで探す「意味検索（semantic search）」をbkfzに足した。文章をモデルで数値の列（ベクトル）に変換し、ベクトル同士の近さで候補を並べる検索だ。この変換をembeddingと呼び、この記事では「埋め込む」とも書く。embeddingはローカルの Ollama に任せた。Backlogの中身は社内情報なので、手元のマシンから外に出さない構成にしている。 hidetzu/backlog-fzf 前回の記事からの変化も書いておく。意味検索はv0.2.0で入った。同じ版で、Homebrewでの配布、URLをコピーする Ctrl-Y、TUI（端末の中で動く対話画面）を閉じずにブラウザで開く Ctrl-O も入れた。TUIで説明文や本文だけにヒットした結果が表示から消える不具合も直した。 この記事では意味検索の構成と、設計で選んだこと・選ばなかったことを書く。 ! 確認した環境は2つある。 実データでの確認、embeddingの速度、評価セットの実行: Apple M2 Max。業務のBacklogのデータはこの端末にしかない ベクトル検索のベンチマーク: Apple M2 Max（Rosetta上のGo）と、Arch Linux（Ryzen 7 5700X、ネイティブのlinux/amd64） 数値は2026年10月13日〜21日に取った。 🗺️ 全体像 既存のキーワード検索（SQLiteのFTS5による全文検索）はそのまま残し、意味検索は使う人だけが有効にする追加機能にした。主な判断は次の4つだ。 項目 選んだもの 理由 位置づけ 既存の検索を残し、意味検索は設定した人だけ有効 課題キーや固有名詞の検索はキーワード検索のほうが速く確実 embedding OpenAI互換APIを叩くだけ。既定はローカルのOllama 推論をバイナリに入れつつ、Goだけで書いた単一バイナリを保てる方法がなかった ベクトル検索 SQLiteの通常テーブルにBLOB（バイト列）で保存し、Goで総当たり 手元と同じ約1万8千ベクトルで66ms。拡張を入れるほどではない TUI 入力中はキーワード検索。Ctrl-S で今のクエリを意味検索し直す 入力のたびにembeddingを作ると、キーワード検索の即応性が落ちる 運用は「まずキーワードで打つ。出なければ Ctrl-S」という順番になる。埋め込む単位と更新の方法も、表の判断と合わせて後で見ていく。 🔥 キーワード検索で見つからないケース bkfzのキーワード検索は本文を2文字ずつ区切ったbigramの索引で候補を絞る。そのあと、元の語が本文にそのまま含まれるかを LIKE で確かめている。 この方式は課題キーや固有名詞など、文字列として覚えているものには強い。逆に、次のようなケースでは1件もヒットしない。 症状を言い換えている（件名は「〜化後のログイン不可事象」のような書き方なのに、「移行のあとにログインできなくなった」で探す） ツールの名前を思い出せない（件名はツールの固有名で、しかも綴りがゆれている） どんな言葉で書いたかを覚えていない 手元の実データで試した。直近2,500件の課題と300件のドキュメントを768次元で埋め込み、件名の語を含まない言い換えクエリを6件投げた。このときはドキュメントを分割せず、本文の先頭だけを使った。結果は目で見て判断した。 キーワード検索は6件すべてで0件だった 意味検索では、3件で意図した課題が1〜2位に来た 残る3件も、同じ種類の課題が上位5件を占めた この比較は非対話の bkfz <query> で取ったので、さきほど書いたTUIの不具合の影響は受けていない。 ! 実データは業務の情報なので、実際のクエリと件名は載せない。例はどれも抽象化したものだ。後半の評価ではリポジトリに同梱した架空データの結果を示す。 🚀 使い方 bkfz本体はHomebrewか、Releasesのバイナリで入る。v0.2.0以降が必要だ。 brew install hidetzu/tap/bkfz # macOS。Linuxは GitHub Releases のバイナリ 意味検索は既定で無効だ。設定ファイルに embedding: を書くまで、bkfzの動きは前と変わらない。 1) Ollamaを入れて、多言語対応のembeddingモデルを取得する（約600MB） brew install ollama # Linuxは ollama.com ollama pull embeddinggemma 2) 設定ファイルに追記する cat >> ~/.config/bkfz/config.yaml <<'EOF' embedding: base_url: localhost model: embeddinggemma EOF 3) 意味検索用の索引を作る（初回だけ） bkfz embed 4) 意味で探す bkfz --semantic "移行のあとにログインできなくなった" bkfz # TUIで普段どおり打ち、見つからなければ Ctrl-S TUIで Ctrl-S を押すと、プロンプトが semantic> に変わり、今のクエリで意味検索した結果に入れ替わる。続けて文字を打つと、キーワード検索に戻る。 🏗️ 設計の詳細 データの流れに沿って、埋め込む文章、embeddingの作り方、保存と検索、更新、TUIの順に見ていく。 設定しない人には何も起きない 意味検索用のテーブルは bkfz embed を初めて実行したときに作る。設定しない人のDBにはテーブルも増えないし、HTTPの通信も起きない。増えるのは Ctrl-S（と予備の Alt-S）のキーバインドだけで、押すと「設定されていない」とヘッダに表示する。 追加したテーブルは2つだ（コメントは説明のために足した）。 internal/index/vector.go CREATE TABLE IF NOT EXISTS embeddings ( kind TEXT NOT NULL, -- issue / doc ref_id INTEGER NOT NULL, chunk INTEGER NOT NULL, -- ドキュメントを分けた断片の番号 content_hash TEXT NOT NULL, vec BLOB NOT NULL, -- 長さ1に正規化したfloat32の列 PRIMARY KEY (kind, ref_id, chunk) ); CREATE TABLE IF NOT EXISTS embedding_meta ( key TEXT PRIMARY KEY, -- model / dimensions value TEXT NOT NULL ); 何を埋め込むか 埋め込む文章は課題とドキュメントで分けた。 課題: 件名と、説明の先頭1,200字で1ベクトル ドキュメント: 本文を1,000字前後の断片（chunk）に分け、1件あたり最大8本。各chunkの頭にタイトルを付ける この値は手元のデータの長さの分布から決めた。課題の説明は平均636字で、1,200字を超えるのは10,559件中1,173件だった。ドキュメントは2,799件で、本文は平均2,760字ある。設計時には1,000字で区切ると平均2.3本と見積もった。実際に作った索引は課題とドキュメントを合わせて17,949ベクトルになった。 ステータス、担当者、日付は埋め込まない。これらはキーワード検索や絞り込みで扱うほうが確実だ。 embeddingはOllamaに任せる bkfzはGoだけで書いた単一バイナリで配っている。CGO（GoからCのコードを呼ぶ仕組み）は使っていない。モデルの推論をバイナリに入れる方法も調べたが、この条件を保てるものがなかった。 ONNX Runtime（機械学習モデルの実行環境）は共有ライブラリかCGOが必要になる 純Goの推論ライブラリは、調べた範囲では依存が大きく遅い Model2Vec（文脈を見ない静的な埋め込み）は純Goで書けそうだが、Goの実装がなく、品質も劣る そこで、bkfz側はOpenAI互換の /v1/embeddings を叩くクライアントを1本持つだけにした。既定の接続先はローカルのOllamaで、モデルは多言語対応の embeddinggemma を使う。1つの文章を768個の数値（768次元）のベクトルにするモデルだ。同じAPIに対応したLM Studioやllama.cppのサーバにも差し替えられる作りにしてある。動作を確かめたのはOllamaだけだ。 クラウドのAPIも設定すれば使えるが、既定にはしなかった。社内の情報を外部へ送る動作は既定にできない。base_url がlocalhost以外のときは、課題とドキュメントを埋め込む前に注意を表示する。検索クエリを送るときには表示しない。 ベクトル検索は純Goの総当たり SQLiteでベクトル検索をするなら、拡張の sqlite-vec を使う手がある。modernc.org/sqlite のv1.60.1以降には同梱されている。bkfzが使うv1.50.0から上げれば、import 1行で使える（バイナリは0.3MB増える）。それでも今回は通常のテーブルに入れたBLOBをGoで総当たりする方式にした。 先にベンチマークを取った。ランダムに作ったベクトルをDBに入れ、新しいプロセスがDBを開いて上位50件を返すまでの時間を測る。値は3回の中央値だ。 ベクトル数 × 次元 Goで総当たり（M2 Max / Linux） sqlite-vec（M2 Max / Linux） 15,000 × 768 70ms / 56ms 25ms / 24ms 17,949 × 768 - / 66ms - / 28ms 15,000 × 256 35ms / 28ms 10ms / 10ms 50,000 × 768 211ms / 180ms 78ms / 74ms 100,000 × 256 192ms / 173ms 58ms / 58ms M2 MaxはRosetta上のGoで測ったので、総当たりの値がネイティブより遅く出ている。Linuxはネイティブだ。どちらの環境でもsqlite-vecは2〜3倍速い。 それでも、手元の実データと同じ17,949×768では総当たりで66msだった。モデルがメモリに載っている状態なら、クエリのembeddingは27〜65msだった。合計は100〜140ms程度になる見込みで、Ctrl-S を押してから待つ時間としては気にならない。 一方でsqlite-vecはv1前で、DELETE の挙動が変わるなど破壊的な変更の前例がある。専用の仮想テーブルを使うので、スキーマも複雑になる。SQLite公式のベクトル拡張（Vec1）も調べたが、2026年10月の調査時点で未リリースだった。Goから使うにはSQLiteドライバの入れ替えも必要になる。 総当たりの中身は単純だ。保存したベクトルを全部読み、クエリとの内積を取る。ベクトルは保存時に長さ1へ正規化しているので、内積がそのままコサイン類似度（向きの近さ）になる。 internal/index/vector.go for rows.Next() { // ... decodeInto(buf, blob) var s float32 for i, q := range query { s += q * buf[i] } kk := key{kind, id} if cur, seen := best[kk]; !seen || s > cur.Score { best[kk] = VectorHit{Kind: kind, RefID: id, Chunk: chunk, Score: s} } } 課題は1件1ベクトルだが、ドキュメントは複数のchunkを持つ。1件ごとに最も高いスコアだけを残して、上位k件を返す。5万ベクトルを超えたら、同じメソッドの組をsqlite-vec版に差し替えることを考える。 差分だけ埋め込み直す モデルの違いは2か所で見ている。 embedding_meta にはベクトルを作ったモデル名と次元数を記録する。設定と食い違っていたら、検索の前に bkfz embed --rebuild を促すエラーを出す。別のモデルで作ったベクトル同士を比べても、意味のある結果にならない content_hash にはモデル名と埋め込む文章をつないだsha256を保存する。同期のたびにハッシュを比べ、本文が変わった課題とドキュメントだけを埋め込み直す 初回の索引作りは bkfz embed を明示的に実行する形にした。手元のデータだと30分ほどかかる見込みだ。バッチごとにコミットするので、途中で止めても続きから再開できる。 これを bkfz sync に載せると、embedding: を書いた直後のsyncが急に長くなる。Ollamaが止まっているときにsync全体が失敗するのも避けたかった。そこで2回目以降は bkfz sync の最後で差分だけを埋め込み、Ollamaに接続できなくても課題とドキュメントの同期は成功扱いにして、警告だけを出す。 TUIはCtrl-Sのワンショット 入力のたびに意味検索する案も考えた。fzfは新しい reload が来ると、実行中の古い reload を止める。fzfの src/core.go の EvtSearchNew でこの動きを確かめた。入力が止まるまで少し待ってから検索する処理（debounce）を入れれば、打つたびの検索も成り立つ。 それでも最初の版では Ctrl-S を押したときだけ意味検索する形にした。キーワード検索の即応性を一切落としたくなかったからだ。fzfには次のような引数を渡している。 fzfに渡す引数（抜粋） --disabled --bind 'change:reload(bkfz --list {q})+change-prompt(> )+change-header:<キーの説明>' --bind 'ctrl-s:reload(bkfz --list --semantic {q})+change-prompt(semantic> )+change-header:<キーの説明>' --bind 'alt-s:（ctrl-sと同じ）' --disabled はfzf自身の絞り込みを止める指定だ。bkfzが返した結果をfzfが表示行で絞り込み直すと、本文だけにヒットした結果が消える。前回の版の不具合はこれだった。意味検索の結果は件名に語を含まないことが多いので、なおさら外せない。 alt-s は予備だ。端末によっては Ctrl-S がXON/XOFF（端末の出力を止める制御）に取られる。 Ollamaが止まっているときの見せ方にも手を入れた。fzfの中で標準エラーに書くと画面が崩れるので、--list --semantic は失敗しても終了コード0で終わる。代わりに、一覧に ✗ embedding server unreachable のような1行を出す。 📊 評価セットで既定値を決める 実データでの確認は6クエリを目で見ただけだった。これではモデルや設定を変えたときに良くなったのか悪くなったのか分からない。そこで、架空データ（課題19件、ドキュメント6件）に対する評価セットをリポジトリに入れた。 scripts/eval/cases.go {"Safari でシングルサインオンに失敗する", []string{issue("WEB-142")}}, // ... {"件数が多いと一覧の表示が重い", []string{issue("WEB-120")}}, // ... {"リリース作業の手順が書いてある資料", []string{doc(docDeployGuide), issue("OPS-57")}}, // ... {"users cannot sign in with single sign-on", []string{issue("WEB-142")}}, 言い換えクエリは22件ある。件名の言い回しをそのまま使わず、キーワード検索では正解が返らないように書いた。英語のクエリで日本語の課題を探すケースも入れている。正解が2つあるクエリは、どちらか一方が入れば当たりとする。 make eval で、Recall@k（上位k件に正解が入ったクエリの割合）とMRR（正解の順位の逆数の平均）を出す。 設定 Recall@1 Recall@5 MRR キーワード検索が正解を返した数 embeddinggemma 768次元（既定） 0.95 1.00 0.98 0 / 22 embeddinggemma 256次元 0.95 0.95 0.96 0 / 22 1位を逃した1件は「フロントエンドのフレームワークを最新版に上げる」だった。正解の「Upgrade React to v19」は2位で、1位とのスコア差は0.002だった。 この評価には限界がある。候補が25件しかなく、上位5件は全体の2割にあたる。クエリも、正解を知っている自分が書いた。点数の高さより、設定を変えたときに下がらないかを見る目安として使っている。 256次元から768次元に戻した embeddinggemmaはベクトルの先頭だけを使って次元を減らせる（Matryoshka表現）。256次元にすると索引は約52MBから17MBに減る。設計の段階ではこの256次元を既定にするつもりだった。 ところが実データの6クエリを256次元で埋め込み直すと、意図した課題が上位5件に入った数が5件から4件に減った。落ちたのは、ツールの固有名を一般語で言い換えたクエリだ。架空データでもRecall@5が1.00から0.95に下がった（22件中1件の差）。そこで既定は削らない768次元に戻し、256次元は索引の大きさを優先したい人向けの設定にした。 キーワード検索との融合（hybrid） 固有名詞を1つだけ覚えているケースなら、キーワード検索と意味検索を混ぜたほうが良いかもしれない。そこで2つの結果をRRF（Reciprocal Rank Fusion）で融合する --hybrid も実装した。各一覧での順位に定数60を足し、その逆数を合計して並べ直す方法だ。 評価セットにも「固有名詞＋あいまいな説明」のクエリを12件足した（「PKCE を入れたい」「FCM の通知が重複」など）。ただ、意味検索だけで12件すべてが1位になり、hybridが上回る余地がなかった。キーワード検索のほうは、空白で区切った全部の語が本文にあることを求める。「PKCE を入れたい」の「を入れたい」が本文にないので、12件中1件しか正解を返さない。実データの約1万8千ベクトルでも、固有名詞を含む4クエリで上位3件が一致した。 このセットでは優劣を判別できなかったので、Ctrl-S の既定は単純な意味検索のままにした。設定の embedding.tui_mode: hybrid で切り替えられる。 ⚖️ トレードオフ 有効にしたときのコスト 意味検索を有効にすると、次のコストがかかる。 項目 値 初回の索引作り 30分ほどの見込み（M2 Maxで測った毎秒9.7件からの換算）。中断しても再開できる DBの増加 約52MB（768次元） Ollamaの常駐メモリ 673MB（既定では最後の利用から5分で解放） 1回の検索 100〜140ms程度の見込み（モデルがメモリに載っている状態） 5分以上空けた直後の検索 モデルの読み込みで数百ms〜数秒が加わる 設定しない人はこのどれも払わない。「設定した人だけがコストを払う」形にできたのが、今回いちばん気をつけた点だ。 クラウドのembedding APIを使えば、Ollamaの導入は要らず、初回の索引作りも数分に縮む（OpenAIのtext-embedding-3-smallで約0.13ドルの見積もり）。その代わり、Backlogの課題とドキュメントの本文がそのAPIへ送られる。業務で使うなら、組織の方針を先に確かめてほしい。 選ばなかった案と見直す条件 選ばなかった案 今回見送った理由 見直す条件 sqlite-vec v1前で、スキーマも複雑になる 5万ベクトルを超えたとき、属性で絞り込みたくなったとき Vec1（SQLite公式） 未リリースで、ドライバの入れ替えが必要 正式リリース後 推論をバイナリに内蔵 Goだけの単一バイナリを保てない Model2VecなどのGo実装が出たとき 入力のたびに意味検索 キーワード検索の即応性が落ちる debounceを入れた別モードとして 256次元を既定に 固有名詞の言い換えで取りこぼした 索引の大きさが問題になったとき（設定で選べる） hybridを既定に 評価セットで差が出なかった 実データ規模の評価で差が出たとき 🌱 次にやること 前回の記事で挙げた項目のうち、Ctrl-Y でのURLコピーはv0.2.0で入った。コメントの検索、複数space、添付ファイル本文の検索、バックグラウンド同期はまだだ。 意味検索の側では、次の項目が残っている。 コメントの同期と埋め込み（キーワード検索のコメント対応と一緒に入れる） 入力のたびに意味検索するモード 5万ベクトルを超えたときのsqlite-vecへの差し替え int8量子化（数値を8bitの整数に丸めて保存する）。索引は1/4になり、Linuxのベンチでは17,949×768の総当たりが66msから46msになった。品質への影響は評価セットで測る いちばん大きいのは評価の穴だ。今の架空ドキュメントは短く、chunkに分かれない。1,000字で区切って最大スコアを取る方式が良いのかどうかは、まだ測れていない。hybridも候補25件のセットでは比べられなかった。次の記事では長い架空ドキュメントを足した評価セットで、chunkの分け方とhybridの効果を測る。 👋 おわりに 意味検索はキーワード検索を置き換えるものではなかった。課題キーや固有名詞はキーワード検索のほうが速く確実で、意味検索が役に立つのは言葉を思い出せないときだ。だから入力中はキーワード検索、見つからないときだけ Ctrl-S という分け方に落ち着いた。 ベクトルの保存と検索も、拡張を入れる前に測った。約1万8千ベクトルなら、SQLiteのBLOBとGoの総当たりで66msだった。拡張を入れるのは、件数が増えて足りなくなってからでよい。 hidetzu/backlog-fzf

There exists asg017/sqlite-vec And for small enough vector sets, holding them in memory is reasonable. Most personal photo collections would fit that scale.

RAG in One .db File: node:sqlite Plus sqlite-vec, No Vector Database - Book: AI That Reads - The series: AI in TypeScript — 5 books, from your first LLM call to agents in production — all five here - My project: Hermes IDE | GitHub — an IDE for developers who ship with Claude Code and other AI coding tools - Me: xgabriel.com | GitHub --- You have a docs/ folder with 300 Markdown files and someone asks for "chat with our docs". The usual first sketch has a hosted vector database or a Postgres instance with an extension, plus an ingestion service to feed it. For a few hundred files, that is a lot of infrastructure to answer "where do we configure the retry budget?" Node already ships a SQLite driver. Since v22.13.0 and v23.5.0 it can load SQLite extensions, and sqlite-vec is an extension that adds a vec0 virtual table for vector search. Put them together and the whole retrieval side of a RAG pipeline lives in one rag.db file next to your script. No separate vector service, and nothing to compile. One TypeScript file covers the whole loop, from indexing a folder of Markdown to an answer that cites its source files. It holds up into the hundreds of thousands of vectors, the practical ceiling sqlite-vec's author gives for its brute-force search. What you need, by version node:sqlite appeared in v22.5.0 behind --experimental-sqlite, lost the flag in v23.4.0 and v22.13.0, and became a release candidate (stability 1.2) in v25.7.0. The loadExtension() method and the allowExtension constructor option are what make sqlite-vec possible: loadExtension() only works if the database was constructed with allowExtension: true. One naming change to know about: in v26.11.0 the class DatabaseSync was renamed to Database, and DatabaseSync stays as a documentation-only deprecated alias (DEP0210). The script below imports DatabaseSync so it runs on Node 24 and 26 alike. On 26.11+ you can switch the import to Database with no other change. The script is a .ts file run directly by node. Type stripping is on by default since v23.6.0 and v22.18.0 and is marked stable since v25.2.0 and v24.12.0. So the floor is Node 22.18. I'd use 24, which is Active LTS and scheduled to move to maintenance on October 20, 2026, or 26, which becomes LTS on October 28, 2026, per the Node release schedule. sqlite-vec's current stable release on npm is 0.1.9. It ships prebuilt binaries as optional dependencies for macOS (x64, arm64), Linux (x64, arm64) and Windows x64, so npm install downloads a .dylib, .so or .dll instead of compiling anything. For embeddings and the answer this uses the OpenAI Node SDK with text-embedding-3-small (1,536 dimensions by default, per the embeddings guide) and gpt-6-luna, which OpenAI calls its most efficient current model. Swap in any provider; the SQLite side does not care where the floats come from. mkdir docs-rag && cd docs-rag npm init -y npm pkg set type=module npm install sqlite-vec openai export OPENAI_API_KEY=... type=module matters because the script uses top-level await. !A single database file holding Markdown pages and their vectors side by side Chunks, vectors and the query all live in one file next to the script. Open the database and create the tables // rag.ts import { DatabaseSync } from "node:sqlite"; import { readdir, readFile } from "node:fs/promises"; import { join } from "node:path"; import * as sqliteVec from "sqlite-vec"; import OpenAI from "openai"; const EMBED_MODEL = "text-embedding-3-small"; const DIMS = 1536; const CHAT_MODEL = "gpt-6-luna"; const MAX_CHARS = 2000; const openai = new OpenAI(); // reads OPENAI_API_KEY const db = new DatabaseSync("rag.db", { allowExtension: true }); sqliteVec.load(db); db.enableLoadExtension(false); db.exec(` CREATE TABLE IF NOT EXISTS chunks ( id INTEGER PRIMARY KEY, path TEXT NOT NULL, heading TEXT NOT NULL, body TEXT NOT NULL ); CREATE VIRTUAL TABLE IF NOT EXISTS vec_chunks USING vec0( embedding float[${DIMS}] distance_metric=cosine ); `); allowExtension: true is off by default, and the Node docs note that if you leave it off you cannot turn extension loading on later. sqliteVec.load(db) resolves the platform binary and calls db.loadExtension() on it. Calling enableLoadExtension(false) straight after turns extension loading off again for this connection, so SQL that runs later cannot call load_extension() unless your code switches it back on. Two tables, joined by id. chunks is a normal table with the text and where it came from. vec_chunks is the vec0 virtual table holding one 1,536-float vector per chunk, with rowid equal to chunks.id. distance_metric=cosine switches KNN from the default L2 distance to cosine, as the KNN docs describe. vec0 also supports +aux columns if you would rather keep the text inside the virtual table; a plain table is easier to inspect with any SQLite tool. Chunk the Markdown by heading type Chunk = { path: string; heading: string; body: string }; function chunkMarkdown(path: string, text: string): Chunk[] { const chunks: Chunk[] = []; let heading = "(top)"; let lines: string[] = []; let inFence = false; const flush = () => { let cur = ""; for (const para of lines.join("\n").split(/\n\s*\n/)) { if (cur && cur.length + para.length > MAX_CHARS) { chunks.push({ path, heading, body: cur.trim() }); cur = ""; } cur += para + "\n\n"; } if (cur.trim()) chunks.push({ path, heading, body: cur.trim() }); lines = []; }; for (const line of text.split("\n")) { if (line.startsWith("`".repeat(3))) inFence = !inFence; if (!inFence && /^#{1,3} /.test(line)) { flush(); heading = line.replace(/^#+ /, "").trim(); } lines.push(line); } flush(); return chunks; } Each chunk is one section under an H1 to H3 heading. Long sections are split on blank lines into pieces of about 2,000 characters, so a paragraph is never cut in half. The inFence flag stops a # comment inside a bash code block from being read as a heading. A single paragraph longer than MAX_CHARS stays whole; text-embedding-3-small accepts up to 8,192 tokens per input (embeddings guide), so that is fine for normal docs. Finding the files is one recursive readdir: async function markdownFiles(dir: string): Promise<string[]> { const entries = await readdir(dir, { recursive: true }); return entries .filter((p) => p.endsWith(".md")) .map((p) => join(dir, p)); } Embed and store async function embed(texts: string[]): Promise<Float32Array[]> { const res = await openai.embeddings.create({ model: EMBED_MODEL, input: texts, }); return res.data .sort((a, b) => a.index - b.index) .map((d) => new Float32Array(d.embedding)); } const toBlob = (v: Float32Array) => new Uint8Array(v.buffer, v.byteOffset, v.byteLength); const embedText = (c: Chunk) => `${c.path} > ${c.heading}\n\n${c.body}`; Prepending the path and heading to the embedded text (embedText) costs a few tokens and gives the vector some context when many sections share a generic heading like "Configuration". async function index(dir: string) { const chunks: Chunk[] = []; for (const path of await markdownFiles(dir)) { const text = await readFile(path, "utf8"); chunks.push(...chunkMarkdown(path, text)); } const vectors: Float32Array[] = []; for (let i = 0; i < chunks.length; i += 64) { const batch = chunks.slice(i, i + 64).map(embedText); vectors.push(...(await embed(batch))); } const insChunk = db.prepare( "INSERT INTO chunks (path, heading, body) VALUES (?, ?, ?)", ); const insVec = db.prepare( "INSERT INTO vec_chunks (rowid, embedding) VALUES (?, ?)", ); db.exec("BEGIN"); try { db.exec("DELETE FROM chunks; DELETE FROM vec_chunks;"); chunks.forEach((c, i) => { const r = insChunk.run(c.path, c.heading, c.body); insVec.run(BigInt(r.lastInsertRowid), toBlob(vectors[i])); }); db.exec("COMMIT"); } catch (err) { db.exec("ROLLBACK"); throw err; } console.log(`indexed ${chunks.length} chunks`); } Three details here come straight from sqlite-vec's own node:sqlite example. Vectors go in as raw bytes: a Float32Array wrapped in a Uint8Array, because node:sqlite binds typed arrays as BLOBs and vec0 accepts a vector as a BLOB of raw float32 values. The rowid goes in as a BigInt, which is how the official example binds it. And there is no .transaction() helper in node:sqlite, so it is BEGIN, COMMIT and ROLLBACK by hand. All network calls happen before the transaction opens. If the embeddings API fails halfway through, the old index is untouched. The trade-off is that every index run re-embeds every chunk. For a few hundred files that is a small bill; past that, store a hash per chunk and only embed what changed. Query: KNN, then a cited answer type Hit = Chunk & { distance: number }; async function ask(question: string, k = 5) { const [q] = await embed([question]); const hits = db.prepare(` WITH knn AS ( SELECT rowid, distance FROM vec_chunks WHERE embedding MATCH ? AND k = ? ) SELECT c.path, c.heading, c.body, knn.distance FROM knn JOIN chunks c ON c.id = knn.rowid ORDER BY knn.distance `).all(toBlob(q), BigInt(k)) as Hit[]; const sources = hits .map((h, i) => `[${i + 1}] ${h.path} > ${h.heading}\n${h.body}`) .join("\n\n---\n\n"); const res = await openai.responses.create({ model: CHAT_MODEL, instructions: "Answer only from the numbered sources. Cite them like " + "[2]. If the sources do not answer it, say so plainly.", input: `Sources:\n\n${sources}\n\nQuestion: ${question}`, }); console.log(res.output_text); hits.forEach((h, i) => console.log(`[${i + 1}] ${h.path} (${h.distance.toFixed(3)})`), ); } The last lines of rag.ts pick the command: const [cmd, arg] = process.argv.slice(2); if (cmd === "index") await index(arg ?? "./docs"); else if (cmd === "ask" && arg) await ask(arg); else console.log('usage: node rag.ts index <dir> | ask "<q>"'); Run it: node rag.ts index ./docs node rag.ts ask "Where do we configure the retry budget?" The WHERE embedding MATCH ? AND k = ? pair is the vec0 KNN syntax. sqlite-vec also accepts LIMIT on SQLite 3.41+, but k = ? works everywhere. The KNN runs in a CTE and joins back to chunks afterwards, so the virtual table only has to answer "which rowids are closest". The model sees numbered sources with their file paths and is told to cite them, and the script prints the paths and distances after the answer. That printout is your debugging tool. When an answer is wrong, look at the five paths first: if the right section is not among them, no prompt change will fix it. One more caution: your docs are now model input. If anyone outside your team can edit them, treat retrieved text as untrusted. !A magnifier sweeping row by row across a growing stack of vectors vec0 compares the query against every stored vector, so cost grows with the corpus. When one file stops being enough sqlite-vec's stable release does exact, brute-force search. At the v0.1.0 launch its author wrote that sqlite-vec "is currently focused on really fast brute-force vector search" and that "it just didn't make sense to include a complex ANN solution in this initial version." Every query compares against every vector. In that post's benchmarks, 100,000 float vectors at 1,536 dimensions took about 105 ms per query, and at 1 million vectors "none of the float vectors at any dimension pass the 100ms smoke test." The author's own estimate of the practical ceiling for latency-sensitive apps was "probably in the 100's of thousands depending on your dimensions/quantization techniques." Those are 2024 numbers, so read them as a rough order of magnitude and measure on your own hardware. For a docs folder that is a long runway. 300 files at a few chunks each is a few thousand vectors, far below where the scan starts to hurt. If you get closer to the limit, you have three cheap levers before migrating: 1. Ask for fewer dimensions. The -3 embedding models accept a dimensions parameter, and the same benchmark had all dimensions from 1,024 down under 75 ms at 100,000 vectors. Change DIMS to match, then re-create the table and re-index. 2. Use binary quantization. The post reports a 32x storage cut and roughly 10x faster queries for "possibly only a 5-10% loss of quality." 3. Partition. vec0 partition keys restrict a KNN query to one tenant or product, with the docs' warning to keep hundreds of vectors per key. The ANN indexes are coming: the 0.1.10 alphas add rescore and DiskANN indexes, but they are pre-releases, so I wouldn't put them under production traffic yet. Graduate to pgvector when SQLite itself becomes the limit. That means several app servers writing to the index at once (SQLite allows one writer at a time), filters joined against tables that already live in Postgres, or millions of vectors where you want an HNSW or IVFFlat index and are fine with approximate results. pgvector does exact search by default too; the index is what buys you speed at that scale. Until then, a single rag.db file is easy to copy, check in as a test fixture, ship inside a container and delete when you are done. --- If this was useful This post is the retrieval loop with nothing around it. AI That Reads is the book I wrote for what comes next in TypeScript: RAG over your own docs, embeddings, vector search, and grounding answers so the model sticks to what your sources say. If you just built rag.ts and want the full picture behind each of those steps, that is the book.  It is book 2 of AI in TypeScript, five books from your first LLM call to agents in production: the whole series is here.

claudeが意図的に省いていたらしく、実験的機能としては最近ANNインデックスが入っていたらしい: github.com/asg017/sqlite-vec/releases

너는 개인 메모나 문서 검색을 만들 때 어떤 설정에서 가장 오래 막혔어? 저장소 asg017/sqlite-vec AI 활용 정보 더 보기 discord.gg