asg017

What Hybrid Search Looks Like When It Has to Run on Your Laptop Suppose you are working in a codebase you did not write, and you type the following question: > Where is ERR_CONN_RESET handled? The answer exists. One file contains a catch block that checks for exactly that constant and retries the request. A purely semantic search may not put that file first. It may return a connection-pool module, a general error-handling utility and a retry helper. All three are about the right topic. None of them necessarily contains the string you asked for. Now consider a second question: > How do we stop a user from logging in too many times? The relevant function is called throttleAuthAttempts. Its body talks about a counter and a lockout window. It never uses the words "logging in" or "too many times". A keyword search may miss it. An embedding model has a much better chance of placing it near the question. This is the trade-off that hybrid retrieval exists to address, and it has been described well elsewhere: lexical search preserves exact identifiers, dense search preserves meaning, and neither preserves both. What is described less often is what happens after you accept that argument and actually build the system. Which BM25 parameters do you use? How does the tokenizer treat getUserById? What does a fused score of 0.03 mean to a user? Where do the chunks come from in the first place? ctx is a useful place to look for answers, because it is small enough to read and has to make all of those decisions in code. It is a TypeScript command-line tool (published on npm as yodaman-ctx, a maintained fork of an earlier project called Context Expert) that indexes local projects into SQLite, searches them with BM25 and dense vectors fused by RRF, and answers questions through a local model served by Ollama. There is no cloud provider in the code path. Everything below describes ctx 1.3.3; file paths are relative to the repository root. This article walks through that pipeline in the order data moves through it: chunking, embedding, the two retrievers, fusion, optional reranking, context assembly and evaluation. At each stage, the same two questions apply: what does this step preserve, and what does it give up? --- 1. Before Retrieval: Deciding What a Document Is A retriever can only return the units you gave it. In code, choosing those units is most of the work. Retrieval papers usually start with a corpus of passages. A code-search tool has to create those passages first, and the choice determines what can be found at all. ctx routes each file by type in src/indexer/chunker/chunker.ts. Config files become a single segment. Markdown goes to a section-based extractor. TypeScript, JavaScript, Python, Go and Rust go to a tree-sitter extractor. Every other language is treated as one segment and split by size. The tree-sitter extractor (code-extractor.ts) walks the top-level statements of the file in order. Each definition becomes its own segment: a function, class, interface, type alias, enum, or a const bound to an arrow function; in Go a func, method or type; in Rust a fn, struct, enum, trait or impl block. Each segment includes the definition's export keyword, decorators or Rust #[...] attributes, and any comments that sit directly above it with no blank line in between. Everything that is not a definition (imports, constants, module setup, a Python if __name__ == "__main__": block) is grouped with its neighbours into a module segment. The comment at the top of the file states the invariant directly: every line of the file lands in exactly one segment. Languages whose code lives almost entirely inside one class or namespace per file, such as Java and C#, are not on the list. A top-level split would give each such file one segment, which the size-based splitter already produces. That invariant matters more than it looks. A chunker that keeps only functions and classes would leave constants, configuration objects and module-level setup in no chunk at all, and no ranking function can retrieve text that was never indexed. Tests in src/indexer/__tests__/chunker.test.ts check the invariant for TypeScript, Python, Go and Rust files: every line appears in exactly one segment. Markdown follows the same principle at a different granularity. markdown-extractor.ts uses the marked lexer to find headings (so a # inside a code fence is not mistaken for one) and makes each heading plus everything under it, up to the next heading, one segment. A table stays attached to its title. A heading with nothing under it is merged into the next section. Segments that fit within a size limit are kept whole. The limits are set per content type in chunker/config.ts: | Content type | Target size | Overlap | |---|---|---| | code | 512 tokens | 50 tokens | | docs | 1,024 tokens | 100 tokens | | config | 256 tokens | 25 tokens | "Tokens" here is an estimate, not a tokenizer count: estimateTokens returns Math.ceil(text.length / 4). Oversized code segments are split by a recursive character splitter. Oversized documentation segments are split by a semantic chunker that embeds sentences and starts a new chunk where similarity between neighbours drops below 0.5. If that embedding call fails, the code catches the error, records a warning, and falls back to the size-based splitter instead of aborting the index. What this step preserves: the boundaries a reader would recognise: a whole function with its doc comment, a whole section with its heading. What it gives up: precision on very large units. A 2,000-line class is still one definition, so it will be split by size, and the four-characters-per-token estimate means the real token count of a chunk can differ from the configured limit. --- 2. The Dense Side: One Vector per Chunk, Scanned Exactly ctx uses a single local embedding model and compares every vector on every query. The default embedding model is BAAI/bge-large-en-v1.5, which the provider rewrites to Xenova/bge-large-en-v1.5 so that it can run through @huggingface/transformers in the Node process. It produces 1,024-dimensional vectors, L2-normalised. For BGE models the vector is taken from the first ([CLS]) token, which is how BAAI trained them; other models use the mean of all tokens (poolingFor in src/sdk/rag/embeddings/huggingface-provider.ts). The README states that the model is about 340 MB and is cached in ~/.ctx/models. An Ollama embedding model can be configured as a fallback, but the provider code refuses a fallback whose dimension differs from the primary model's, because the stored vectors would not be comparable. Queries and chunks are not embedded quite the same way. BAAI's model card for the English BGE models gives an instruction to put before a search query, and only before the query: "Represent this sentence for searching relevant passages: ". The Hugging Face provider adds it in embedQuery(), which every search path uses, while chunks are embedded as they are. On the 56-question evaluation described in section 8, scored against one fixed index, the instruction raised MRR from 0.51 to 0.55 and put the expected file in the top five for 80% of questions instead of 73%; eight questions improved and four got worse. Stored vectors and chunks depend on how they were made, and an index built one way keeps working when the code changes: it just returns worse results. ctx guards against that. Each project records the index format it was built with (index_version, defined in src/database/index-version.ts), written only when an index run finishes. Loading a project whose format is older than the current one prints a warning with the exact re-index command, and ctx check lists it. The comment above the constant states when it must change: whenever stored chunks or vectors change meaning, such as a different pooling method or a different way of splitting a language. At query time, the vector store is not an approximate nearest-neighbour index. It is an exact scan, and it runs inside SQLite. The embeddings already sit in the chunks table as float32 blobs, and the sqlite-vec extension, loaded on every database connection, scores them in C. src/search/sqlite-vector-store.ts asks for the top k in one query: SELECT id, content, file_path, file_type, language, start_line, end_line, metadata, 1 - vec_distance_cosine(embedding, ?) AS score FROM chunks WHERE project_id = ? ORDER BY score DESC LIMIT ? Why an exact scan, and why in SQL, both come back to the shape of the program. ctx is a CLI: each command is a new process, so anything built in memory is built again for every search. A comment in src/search/store.ts records what an approximate index would cost on those terms: 8.3 seconds to build HNSW over 3,714 vectors, compared with 43 ms to scan 58,728. For a long-running server with millions of vectors, an approximate index is the normal choice. For a CLI that starts, answers one question and exits, the build cost dominates. The same reasoning applies to loading vectors at all. Measured on synthetic projects with 1,024-dimensional vectors, a project is ready to search in about 5 ms and the process holds almost no extra memory, because nothing is loaded; a query takes 0.13 seconds at 25,000 chunks and 0.97 seconds at 200,000. Loading the same vectors into memory first took 0.33 seconds and 0.2 GB at 25,000 chunks, and between 2.6 and 7.8 seconds and 1.1 GB at 200,000, before a faster scan. A long ctx chat session, which asks many questions in one process, pays a little more per question this way; a single search pays much less. Tests in src/search/__tests__/sqlite-vector-store.test.ts check that the SQL scan returns exactly what the in-memory scan returns. sqlite-vec ships prebuilt binaries for Linux and macOS on x64 and arm64 and for Windows on x64. Where it cannot load, store.ts falls back to loading a project's vectors into memory, in batches paged by rowid > ? rather than OFFSET so each page is an index seek, and scanning them there. In both cases, a stored vector whose dimension differs from the project's stops the search with an error that tells the user to re-index with --force, rather than comparing incompatible vectors. What this step preserves: paraphrase and intent. A question about "logging in too many times" can land near throttleAuthAttempts. What it gives up: any explicit guarantee that a rare exact string will dominate. The identifier has to compete with everything else in the chunk for influence over a single 1,024-dimensional vector. --- 3. The Lexical Side: BM25 Inside SQLite The formula is the textbook one. The interesting decisions are in the tokenizer, and in where the index lives. Where the keyword index lives matters as much as the formula. ctx is a CLI: each command is a new process. An inverted index built in memory would have to be rebuilt from every chunk of a project at the start of every search. So ctx keeps its keyword index on disk too, in SQLite's FTS5 full-text extension, which ships with the better-sqlite3 package it already uses. Migration 008 in src/database/migrate.ts creates the index and three triggers on the chunks table: CREATE VIRTUAL TABLE IF NOT EXISTS chunks_fts USING fts5( tokens, content = '', contentless_delete = 1, tokenize = 'unicode61 remove_diacritics 0' ); CREATE TRIGGER IF NOT EXISTS chunks_fts_insert AFTER INSERT ON chunks BEGIN INSERT INTO chunks_fts (rowid, tokens) VALUES (new.rowid, ctx_tokens(new.content)); END; The index is "contentless": it stores the tokens' positions but not the text, which stays in chunks. The triggers fire on every insert, update and delete, including the delete-then-insert swap ctx uses when re-indexing a project, so no code path has to remember to update the keyword index. The migration also fills the index from any chunks already stored. Searching is one query in src/search/bm25-store.ts: match any query token, join back to chunks for the project and any file-type or language filter, and order by FTS5's bm25() function. FTS5 uses the common defaults, k1 = 1.2 and b = 0.75, with the standard saturated, length-normalised term frequency. The result is divided by the top score so the best keyword hit is 1.0. Here, a "document" is a chunk. Two properties of FTS5's scoring are worth knowing. First, it counts how rare a term is (its IDF) over every chunk in the index, not only the project being searched; with several projects indexed, a word common in one project but rare overall weighs more than it would within that project alone. Second, its IDF is log((N − n + 0.5) / (n + 0.5)), with a zero or negative value replaced by 0.000001. Some BM25 implementations add 1 inside the logarithm so every term keeps a small positive weight; FTS5 does not, so a term found in half or more of all chunks adds almost nothing. Measured on the same synthetic projects, nothing is built when a search starts. A keyword query took between 3 and 9 ms at 25,000 chunks and between 60 and 198 ms at 200,000 across two runs, and indexing pays about 0.3 ms per chunk for the trigger, small next to computing the chunk's embedding. There is one caveat, which a comment in the migration states: the index is keyed on SQLite's internal row IDs, and a manual VACUUM can renumber those. ctx never runs one. The part that most affects code search is not the formula but the tokenizer, in src/sdk/rag/retrieval/bm25.ts: const CAMEL_BOUNDARY = /(?<=\p{Ll})(?=\p{Lu})|(?<=\p{Lu})(?=\p{Lu}\p{Ll})/u; for (const word of text.split(/[^\p{L}\p{N}]+/u)) { const parts = word.split(CAMEL_BOUNDARY); if (parts.length > 1) tokens.push(word.toLowerCase()); for (const part of parts) tokens.push(part.toLowerCase()); } return tokens.filter((token) => token.length >= 2); Every run of characters that is not a Unicode letter or digit becomes a separator. A camelCase word is kept whole and also split at its case boundaries. Tokens shorter than two characters are dropped. FTS5 has its own tokenizer, but it does not split camelCase, so ctx registers this function with SQLite as ctx_tokens() (in src/database/connection.ts). The triggers store its output, and the same function tokenizes each query, so the index and the query always split text the same way. Running it on a few identifiers shows what it does: | Input | Tokens | |---|---| | ERR_CONN_RESET | err, conn, reset | | ZX-4821 | zx, 4821 | | getUserById | getuserbyid, get, user, by, id | | HTTPServer | httpserver, http, server | | get user by id | get, user, by, id | Keeping both forms serves two kinds of query. A query that contains getUserById verbatim still matches the whole-word token, which is rare and so carries a high IDF. That is exactly what BM25 is for. A query that says "get user by id" in plain words matches the parts. A tokenizer that kept camelCase as one token would give that second query no lexical match on the identifier at all. Other limits remain. ZX-4821 becomes two tokens, so a chunk containing ZX-4812 still matches on zx, and only the numeric token distinguishes the two. The parts of a camelCase word are also common words (get, user, id), so their IDF is low; with FTS5's formula, a part that appears in half or more of the chunks adds almost nothing, and the parts help a chunk's score rather than deciding it. It is still worth running a retriever's tokenizer on your own identifiers before assuming what "exact match" means. What this step preserves: exact rare strings (constants, error codes, function names typed verbatim) and, through the split parts, a lexical link between an identifier and the words it is made of. What it gives up: any connection between words that do not share a token, such as "authenticate" and checkLogin. --- 4. Fusion: RRF, Then a Rescale So the Number Means Something Reciprocal Rank Fusion solves the incompatible-scores problem. It creates a readability problem of its own. FusionService.search in src/search/fusion.ts runs the dense and BM25 searches in parallel with Promise.all, then fuses the two ranked lists. The underlying function is the standard one, with k = 60: $$ \text{RRF}(d) = \sum_{r \in R} \frac{1}{60 + \text{rank}_r(d)} $$ A chunk that is missing from one list receives zero from that list. The implementation also records each retriever's contribution, so a fused result carries a record of which list it came from and at what rank. Raw RRF scores are small. A chunk ranked first by both retrievers scores 2/61 ≈ 0.033. Shown to a user, a value near 0.03 looks like a poor match, and any minScore threshold on a 0 to 1 scale would remove every result. fusion.ts rescales in three lines: // Raw RRF tops out near 0.03 (2/61), which read as a poor match and made // any 0-1 minScore drop every result; divide by the best possible score. const bestPossible = 2 / (k + 1); After this division, 1.0 means "ranked first by both", and about 0.5 means "ranked first by one and absent from the other". The order of results does not change; only the scale does. Applying it to a small example: | Chunk | BM25 rank | Dense rank | Scaled score | |---|---|---|---| | A: the catch block for ERR_CONN_RESET | 2 | 3 | 0.976 | | B: a log message mentioning ERR_CONN_RESET | 1 | 8 | 0.949 | | C: a general retry helper | not ranked | 1 | 0.500 | Chunk A is first in neither list but first after fusion, because both retrievers placed it near the top. This is the behaviour hybrid search is designed for. There is one practical constraint on this example. Each retriever is called with the same topK as the final result count. ctx search defaults to 10, so a dense rank of 8 is inside the list. ctx ask uses final_k, which defaults to 5, so each retriever contributes at most five candidates and chunk B's dense rank of 8 would not be seen at all. In that case, B would score 1/61 ÷ 2/61 = 0.5. The candidate depth of each retriever directly bounds how much agreement fusion can detect. It is tempting to conclude that deeper lists are always better. A comment in fusion.ts records a test that points the other way. Fetching 50 candidates from each retriever lost an expected file on the project's eight-question fixture, with recall falling from 0.94 to 0.88. With k = 60, a chunk ranked seventh by both retrievers scores 2/67 ≈ 0.030, while a chunk ranked first by one and absent from the other scores 1/61 ≈ 0.016. When the lists are deep enough to contain most of a small corpus, middling agreement can outrank a strong single signal. The fixture is too small to settle the question for large projects, so the code keeps candidate depth equal to topK. minScore is applied only to the fused score. The retrievers do not receive it, because their own scores are on other scales: raw cosine similarity for the dense search, and a score scaled to the best hit for BM25. Both retrievers count equally in the fusion. One adjustment is made after it. Documentation describes features in plain words, so a Markdown section about a feature often matches a question about that feature about as well as the code does, and in a mixed repository it can push the code out of the top results. fusion.ts therefore multiplies the fused score of documentation chunks by DOCS_WEIGHT, 0.9. Because fused scores sit close together (ranked first by one retriever: 0.5; ranked fifth: 0.47), the weight mostly decides near-ties in favour of code, and its exact value matters little. On the 56-question evaluation, every weight from 0.6 to 0.95 produced the same ranking, improving 12 questions and worsening none, the documentation questions included. A chunk of documentation that both retrievers rank highly still beats code that only one of them found, and a project that contains only documentation is unaffected, because every chunk is scaled alike. What this step preserves: agreement. A chunk supported by both kinds of evidence rises above a chunk supported by only one. What it gives up: the magnitude of the original scores. A BM25 match that is far stronger than everything else and a BM25 match that barely beats second place both count as rank 1. And in a repository of code and documentation, a documentation chunk needs slightly stronger agreement than a code chunk to rank above it. --- 5. The Optional Reranker A cross-encoder is often presented as a free improvement. In ctx it is opt-in. ctx includes a cross-encoder reranker (src/search/reranker.ts), using Xenova/bge-reranker-base. When enabled, it takes up to 50 fused candidates, scores each query–document pair, and returns the top k. In practice the limit is lower: each retriever contributes topK results, so the reranker sees at most 20 candidates at the default of 10. If the model cannot load, the code warns once and returns the results in their fused order rather than failing the search. It is not enabled by default. It runs only with ctx search --rerank; ask and chat do not rerank. The README gives the reason in the option's description: in testing, the reranker ranked tables and code lower. A cross-encoder trained mostly on prose judges prose well, and a code-search tool's content is largely not prose. That does not make cross-encoders a poor choice in general. It shows that a stage that improves results on one kind of content can make them worse on another, and that the way to know which applies is to measure on your own content, which section 8's evaluation makes possible. --- 6. From Results to Prompt: Deduplicate, Order, Budget Retrieval ends with a ranked list. The model sees something else. For ctx ask, retrieved chunks pass through an assembler (src/sdk/rag/assembly/base-assembler.ts) before reaching the model. The steps run in a fixed order: 1. Keep the top final_k results (default 5). 2. Remove near-duplicates: a chunk whose word set has Jaccard similarity of 0.8 or more with an already-kept chunk is dropped. 3. Include chunks in relevance order until the token budget (default 4,000) is used. 4. Reorder the chunks that fit with the configured strategy (default: sandwich). 5. Format as XML, with each <source> carrying its file path and line range. The sandwich ordering is a response to the observation that models tend to use information at the start and end of a long context more reliably than information in the middle. With n results and the default start count of ⌈n/2⌉, the top half goes first and the remainder goes at the end in reverse order. For five results, the order becomes 1, 2, 3, 5, 4. The order of steps 3 and 4 matters. If the budget ran after ordering, the chunks it considered last would be those at the end of the sandwich, the positions the ordering reserves for the fourth- and fifth-ranked results, so a tight budget would cut those first while the least relevant chunks in the middle survived. Running the budget first, in relevance order, drops the least relevant chunks, and the sandwich is built from what remains. A test in src/agent/__tests__/assembler.test.ts covers this: with room for four of five chunks, the result is 1, 2, 4, 3; budgeting after ordering would keep 1, 2, 3, 5. The budget loop still includes any chunk that still fits rather than stopping at the first that does not, so a short, lower-ranked chunk can be included after a long one is skipped. With five documentation chunks near the 1,024-token limit, a 4,000-token budget holds three of them. It is worth inspecting the final context rather than only the ranked list, because stages like these interact. ctx makes that straightforward: ctx ask --context-only --json returns the assembled context without calling the model. --- 7. Which Project? Routing Without a Model, Mostly Searching every indexed project is the safe default until there are a dozen of them. ctx can index many projects and search one or all of them. In ctx chat, a question first goes through QueryIntentClassifier in src/agent/query-router.ts, which uses string matching and no model call. Its rules, in order: | Condition | Intent | Confidence | |---|---|---| | Two or more project names mentioned | multi-project | 0.95 | | A multi-project keyword (e.g. "compare", "across") and at least one project name | multi-project | 0.85 | | Exactly one project name mentioned | single project | 0.9 | | No name, but a phrase like "this project" or "this repo" | single project | 0.85 | | A multi-project keyword with no project name | general | 0.6 | | Anything else | general | 0.5 | A comment in the code explains the second-to-last rule: "compare error handling" with no project named is deliberately not classified as multi-project, to avoid false positives. What happens to a "general" question depends on state. If a project is focused (with /focus), the question stays in that project. Only when nothing is focused and at least two projects exist does the router ask the local model, with a 5-second timeout and a confidence threshold of 0.7; if the call fails or the confidence is too low, it searches every project. The order is deliberate. A local model can easily exceed a 5-second limit, and the fallback, searching every project, grows with the number of projects; with many of them it can run past the agent's 30-second tool limit and leave an answer with no sources. Staying in the focused project avoids both. When multiple projects are searched, the multi-project service applies RRF at two levels: the dense and BM25 managers each merge results across projects, and the service then fuses the dense and BM25 lists. --- 8. Measuring Retrieval Separately from Answers If the right chunk never enters the context, no prompt can recover it. ctx ships an evaluation command, ctx eval, with metrics implemented directly in src/eval/metrics.ts: reciprocal rank, precision@k, recall@k, hit rate, NDCG@k with binary relevance, and average precision. Each golden entry pairs a question with the file paths expected in the results: { "id": "fixture-002", "query": "Where is the JWT token verified?", "expectedFilePaths": ["src/auth/middleware.ts"], "tags": ["auth"], "source": "manual" } The repository holds two golden sets in fixtures/eval/, and they do different jobs. The first is a small fixture project (eight source files) with eight queries. A GitHub Actions workflow runs it on pull requests that touch search, indexing, agent or eval code, and weekly on a schedule, using a mock search function; scripts/check-eval-thresholds.js fails the build if MRR falls below 0.6, precision@k below 0.5, or hit rate below 0.8. It checks that the evaluation machinery works, but it says little about search quality: with the real model it scores an MRR of 0.94, close to its ceiling, so most changes leave its scores exactly where they were. The second is ctx-source-golden.json: 56 questions about ctx's own repository, mixing exact identifiers ("Where is computeRRF defined?"), paraphrases ("How are near-duplicate chunks removed before the context is assembled?") and documentation questions. Each expected file is stored with a pattern it must contain, the code or text that answers the question, and a test fails if a file is renamed, deleted or no longer contains its answer, so the set cannot quietly drift out of date as the code changes. scripts/eval-source.ts indexes the repository (180 files, 1,529 chunks) with the real embedding model, in a temporary home folder, and scores it. It scores an MRR of 0.60, with the expected file in the top five for 86% of questions (recall 0.80, NDCG 0.63). That leaves room to show changes in either direction, and the breakdown by area of the code shows where search is still weak: | Questions about | Questions | MRR | Hit rate (top 5) | |---|---|---|---| | Database | 4 | 1.00 | 1.00 | | Embeddings | 3 | 1.00 | 1.00 | | Documentation | 8 | 0.72 | 0.88 | | Evaluation | 5 | 0.67 | 1.00 | | Search | 11 | 0.64 | 1.00 | | Indexing | 9 | 0.56 | 0.67 | | CLI | 9 | 0.49 | 0.89 | | Agent | 4 | 0.19 | 0.50 | | All | 56 | 0.60 | 0.86 | A question can belong to more than one area, and some areas have only three or four questions, so the rows are indications rather than measurements. The eight questions that miss the top five share a pattern: the results usually come from the right module but the wrong file. Asked for the largest file that gets indexed, search returns the chunker's types.ts rather than its config.ts; asked what happens when Ollama fails mid-answer, it returns the provider's errors.ts and types.ts rather than response-mapper.ts. Type and error definitions share their vocabulary with the code that uses them, and file-level judging counts those near misses as complete misses. None of this was visible in the eight-question fixture. Both sets judge relevance at the file level, so a result counts as relevant if it comes from the right file even if it is the wrong chunk of that file. And 56 questions are still few: a change of one or two questions, about 0.02 MRR, is within what a single reordering can cause, which is why the guide in docs/eval-observability.md suggests looking at which questions changed rather than only at the totals. --- Conclusion: The Algorithm Is the Short Part The core of ctx's retrieval is short. RRF is a loop over two lists. BM25 is a function SQLite already provides, over an index it keeps on disk. Cosine similarity is a function a SQLite extension provides, over vectors that are already stored. The original argument for hybrid search, that lexical and semantic retrieval preserve different evidence, is fully visible in a few hundred lines. Most of the remaining code is about everything around those lines: which units get indexed, how identifiers are tokenised, where an index lives and when it is built, what a score of 0.03 means to a person reading it, how many candidates each retriever contributes, whether a reranker helps on this kind of content, what survives a token budget, and whether an old index still matches the code that reads it. Little of it is about ranking. Much of it exists so that the right text is indexed, a score means what it appears to mean, and no stage silently does nothing. That is the practical lesson from reading this codebase. Choosing hybrid search is a decision that takes a paragraph to justify. Making it work takes attention to the steps before and after it. The Practical Takeaway If you are building retrieval over source code, a few checks follow directly from the code above: - Check coverage before ranking. Confirm that every line of a file ends up in some chunk. Constants and module-level code are easy to lose with definition-only chunking. - Run your tokenizer on your identifiers. Look at what happens to camelCase, snake_case and hyphenated codes. "Exact match" means whatever the tokenizer says it means. - Look at candidate depth, and measure before changing it. RRF can only reward agreement that appears inside both lists. If each retriever returns five results, a document ranked sixth by one of them gets no credit for it. Deeper is not automatically better, though: deep lists let middling agreement outrank a strong single signal. - Build indexes once, at index time, and search where the data is. Anything loaded or rebuilt when each process starts costs little in a long-running server and dominates a CLI. Both halves of a hybrid search can run in the database you already have: SQLite's FTS5 provides BM25, and sqlite-vec scores stored vectors. - Read the embedding model's card. Some retrieval models expect an instruction before queries and none before passages. Using the model the way it was trained is the cheapest improvement there is, but it still needs measuring. - Record what an index was built with. When chunking or embedding changes, old indexes keep working and quietly get worse. A stored version number turns that into a warning. - Make scores readable. If users or downstream code filter by score, rescale RRF output so its range has a meaning. - Measure optional stages on your own content. A reranker, a router, an approximate index or a weight on one kind of document is a trade-off, not a guaranteed improvement. - Inspect the final context, not just the ranking. Ordering and token budgets change which chunks the model actually sees. ctx is MIT-licensed and available at Yoda-Man/ctx. The files referenced in this article are under src/search/, src/indexer/chunker/, src/database/, src/sdk/rag/, src/eval/ and fixtures/eval/. --- References Robertson, S., & Zaragoza, H. (2009). The Probabilistic Relevance Framework: BM25 and Beyond. Foundations and Trends in Information Retrieval, 3(4), 333–389. doi.org Cormack, G. V., Clarke, C. L. A., & Büttcher, S. (2009). Reciprocal Rank Fusion outperforms Condorcet and individual rank learning methods. Proceedings of SIGIR 2009, 758–759. doi.org Liu, N. F., Lin, K., Hewitt, J., Paranjape, A., Bevilacqua, M., Petroni, F., & Liang, P. (2024). Lost in the Middle: How Language Models Use Long Contexts. Transactions of the Association for Computational Linguistics, 12, 157–173. doi.org Xiao, S., Liu, Z., Zhang, P., Muennighoff, N., Lian, D., & Nie, J.-Y. (2024). C-Pack: Packed Resources For General Chinese Embeddings. Proceedings of SIGIR 2024. arXiv:2309.07597. (The paper introducing the BGE embedding models.) SQLite. SQLite FTS5 Extension, section 5.1.1, "The bm25() function". sqlite.org. The IDF formula and its 1e-6 floor are in the source, ext/fts5/fts5_aux.c (function fts5Bm25GetData). github.com/sqlite/sqlite/blob/master/ext/fts5/fts… Yoda-Man. ctx source code, version 1.3.3. Yoda-Man/ctx

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