vec0 table, or the SQLite project's own vec1 extension.The insurer's underwriting agent runs its retrieval on a database server in a data center. That works because the guidance passages, the applicant profiles and the precedents are company data, and the company runs the server. Now take the same retrieval primitive to a place with no server and no permission to send text anywhere: a laptop full of private notes.
A person keeps notes about their home in markdown files: the router setup, a trip plan, a garden log, a budget, recipes, and a note about the house roof. One evening they ask their notes app a question, "what to do before the storm season". The answer is in notes/home-roof-notes.md, in a paragraph about cleaning the gutters, and the word "storm" appears in it but "before" and "season" carry most of the meaning. A keyword search ranks it poorly. A vector search finds it. But a vector search needs vectors, a table to hold them and a query that ranks them, and the notes are private: the person does not want them copied to a hosted database, and does not want to install and run a database server for six files.
The question for this page: can the first primitive of this site run inside one file on one machine, and what do you give up when it does?
What this is not. This page is about SQLite as the place where vectors are stored and searched. Where vectors live in general (in the row, in a side table, in a separate engine) is the storage page; the dated product matrix is other ecosystems. An agent's durable memory across sessions is a different subject, covered at memory.sammuthu.com.
One file, inside your program#
Recall the primitive. An embedding column holds one vector per row; a vector query compares the query vector with the rows' vectors, ranks the rows by distance and returns the nearest k; the model reads only what retrieval returned. Retrieval is a query. The model reads only what it returns. Nothing in that sentence needs a server. It needs a table, a distance and a sort.
SQLite provides the table. Its own documentation, read on 2026-11-18, describes it as an in-process library that implements a self-contained, serverless, transactional SQL database engine. In-process means the database code runs inside your program, in the same process, as a library call; there is no separate server process to start, connect to or secure. The documentation also says that a complete database, with tables, indexes, triggers and views, is contained in a single ordinary disk file, and that the file format is cross-platform: the same file can be copied between 32-bit and 64-bit machines and between big-endian and little-endian ones.
A concrete picture: notes.db is one file next to the notes. The notes app opens it, runs SELECT statements against it through a library call, and closes it. Copying the file copies the whole database, vectors included.
Before AI. Java developers know this shape from embedded databases such as H2 or Derby running inside the application, and every mobile developer knows SQLite itself as the database inside the app. The SQLite documentation lists desktop applications that use it as their on-disk file format: version control systems, financial tools, media catalogs. A vector column in that file is one more column in a format teams already ship.
SQLite does not provide the distance. It has no vector type and no vector operator. That gap is what the three ways below fill, and they fill it very differently.
Three ways to search vectors in SQLite#
Before the details, the map. All three keep the vectors in the same SQLite file as the source rows. They differ in who computes the distance and whether anything can skip rows.
Diagram: Three ways to search vectors inside one SQLite file
Each lane is one way. All three keep vectors in the same file as the source rows. They differ in who computes the distance and whether anything can skip rows. Click a box for what it does.
- Registered function
- BLOB column: Each vector is packed into an ordinary column of the source table.
- cosine_distance(a, b): A Python function registered with create_function computes the distance; SQL sorts by it.
- sqlite-vec
- Load the extension: A compiled library adds vector functions and the vec0 table to the connection.
- vec0 virtual table: Holds a row id and a vector per row; L2 by default; joined back to the source rows by id.
- vec1
- Exhaustive modes: Documented exact modes that compare every row; no index is the default.
- Trained mode: A documented approximate mode trained from a sample, version 0.8; release status not verified.
The first way stores each vector in an ordinary BLOB column (a column of raw bytes) and teaches SQLite a new function, written in your own language, that computes the distance. The second loads a C extension, sqlite-vec, that adds a vector table type with its own query form. The third is an extension written by the SQLite project itself, vec1, which is documented today with both exact and approximate modes. The rest of the page takes them in that order, from the one with no dependencies to the one with the most open questions.
Way 1: a registered function from the standard library#
Python's sqlite3 module, which ships with Python, can register a Python function under a name that SQL statements can then call. A registered function is exactly that: a function in the host language that the database calls once per row when a statement uses its name. The Python documentation, read on 2026-11-18, gives the signature as Connection.create_function(name, narg, func, /, *, deterministic=False).
With one such function, exact vector search becomes ordinary SQL. examples/rag-uw/sqlite_exact.py packs each vector into a float32 BLOB (4 bytes per dimension), registers cosine_distance(a, b), and sorts by it. The excerpt below is from that file, unchanged.
# excerpt from examples/rag-uw/sqlite_exact.py: pack, sql_cosine_distance, connect
def pack(vec: Sequence[float]) -> bytes:
"""float32 little-endian BLOB (4 bytes per dimension)."""
return struct.pack(f"<{len(vec)}f", *vec)
def sql_cosine_distance(a: bytes, b: bytes) -> float:
"""Registered as cosine_distance(a, b) in SQLite. Both arguments are float32 BLOBs."""
return py_cosine_distance(unpack(a), unpack(b))
def connect() -> sqlite3.Connection:
con = sqlite3.connect(":memory:")
con.create_function("cosine_distance", 2, sql_cosine_distance, deterministic=True)
The exact search page already ran this file's guidance query and read its plan. Here the interesting part is the other corpus in the same file: six Mnemos-like private notes, each stored as a file row with a path and a content hash, and chunk rows with offsets, a chunk hash, the model name and the vector. The run below is from a scratch copy of the example (SQLite 3.53.4, the version bundled with that Python). A successful run proves that a local file, a registered function and bound parameters are enough for exact retrieval over private notes, with every result traceable to a path and a character span. It does not prove anything about the quality of a model: the vectors come from the toy encoder toy-a@1, a deterministic 16-dimension stand-in for an embedding model, and the notes are synthetic.
cd rag-uw && /opt/homebrew/bin/python3 sqlite_exact.py
SQLite 3.53.4, in memory. Loaded 6 files -> 6 chunks (chunk_size=1000, overlap=200), 18 guidance passages. Vectors: toy-a@1, 16 x float32 BLOB.
Mnemos-like private notes (bound parameters: query vector, model, k):
SELECT f.path, c.ordinal, c.start_offset, c.end_offset, substr(c.chunk_sha256, 1, 12) AS sha,
cosine_distance(c.embedding, ?) AS dist
FROM chunk AS c JOIN file AS f ON f.file_id = c.file_id
WHERE c.embedding_model = ?
ORDER BY dist, c.chunk_id
LIMIT ?
query: 'how do I fix devices dropping off the wifi'
0.0038 notes/router-setup.md#chunk0 [0:512] sha256=056755dec40e...
0.9398 notes/trip-plan.md#chunk0 [0:368] sha256=62526f0aeec0...
0.9613 notes/garden-log.md#chunk0 [0:295] sha256=83dc2996a49d...
query: 'what to do before the storm season'
0.6404 notes/home-roof-notes.md#chunk0 [0:363] sha256=745a0c360137...
0.8507 notes/garden-log.md#chunk0 [0:295] sha256=83dc2996a49d...
0.8635 notes/budget-notes.md#chunk0 [0:298] sha256=8dfd69fe0d0c...
query: 'make a thick sauce'
0.0040 notes/recipe-notes.md#chunk0 [0:296] sha256=82ae0debc5f8...
0.0159 notes/garden-log.md#chunk0 [0:295] sha256=83dc2996a49d...
0.8863 notes/budget-notes.md#chunk0 [0:298] sha256=8dfd69fe0d0c...
...
Every row was compared: this is exact search (no index).
Nothing left the machine: embeddings are computed in process; the database is in memory.
The output is trimmed at the "..." line, where the guidance query and the UNSAFE example sit. The storm-season question finds the roof note first, at cosine distance 0.6404: a weak match by the numbers, but the right file, and the next rows are much further away. Every result names a path, a chunk ordinal, a character span and a hash prefix, so the app can open the exact text it is about to show. The query vector, the model name and k are all ? parameters, bound by the driver; no user text is pasted into the SQL.
Two properties of this design follow directly from how it works. First, it is exact by construction: SQLite has no idea what cosine_distance means, so it cannot use an index to skip rows; it calls the function for every row that survives the WHERE and sorts the results. Second, every one of those calls crosses from SQLite into Python and unpacks two BLOBs, so the cost per row is high compared with a distance computed in C. For six notes that is irrelevant. For a large corpus it is the first thing to measure.
Production pitfall.deterministic=Truetells SQLite the function always returns the same output for the same input. That is true for a distance and lets SQLite treat the function like a built-in. Do not set it on a function that reads a clock, a random number or external state. The Python documentation says the flag raisesNotSupportedErrorif the SQLite library is too old to support it.
Way 2: the sqlite-vec extension and its vec0 table#
A loadable extension is a compiled library that a running SQLite connection loads at run time to gain new functions, types or virtual tables, without changing SQLite itself. sqlite-vec is a community project packaged as one: as documented on 2026-11-18, it is written in C with no dependencies, runs wherever SQLite runs (Linux, macOS, Windows, WASM in browsers, Raspberry Pi), and has bindings for Python (pip install sqlite-vec), Node.js and several other languages. Its README marks it pre-v1 and asks users to expect breaking changes.
Loading an extension is a deliberate act. The sqlite-vec Python page shows the sequence: db.enable_load_extension(True), sqlite_vec.load(db), then db.enable_load_extension(False). The same page warns that the SQLite bundled with macOS does not support extensions, so the system Python raises AttributeError on enable_load_extension, and suggests Homebrew Python instead. Python's own documentation confirms the reason from the other side: the sqlite3 module is not built with loadable-extension support by default because some platforms, notably macOS, ship SQLite without it.
Once loaded, sqlite-vec adds vec0, a virtual table for vectors. The storage page explains virtual tables: an object that looks like a table to SQL while the extension's code handles its reads and writes. A vec0 table holds a row id and a vector per row; the source rows stay in ordinary tables, so vec0 plays the role of an auxiliary vector table. The KNN query form, as documented on 2026-11-18, is WHERE embedding MATCH ? AND k = ?: the first parameter is the query vector and k is how many nearest rows to return. The documentation adds that a LIMIT form also works, but only on SQLite 3.41 and later.
The example's sqlite_vec_demo.py builds the notes corpus in the layout Mnemos uses: file and chunk tables, plus vec_chunk USING vec0(chunk_id INTEGER PRIMARY KEY, embedding FLOAT[16]), joined to chunk by id. It builds a second vec0 table declared with distance_metric=cosine, queries both, then deletes one file. A successful run proves three things: the KNN form returns the nearest chunks and joins back by row id, the default metric and the cosine option rank normalized vectors identically, and deleting a source file leaves its vector behind until the application removes it. The run below is from a throwaway virtual environment outside the repository (pip install sqlite-vec==0.1.9, Homebrew Python, SQLite 3.53.4); it matches the recorded run in examples/rag-uw/SQLITE_VEC_OUTPUT.md line for line.
/opt/homebrew/bin/python3 -m venv vecenv && vecenv/bin/pip install sqlite-vec==0.1.9
vecenv/bin/python rag-uw/sqlite_vec_demo.py
SQLite 3.53.4, sqlite-vec v0.1.9, in memory.
Loaded 6 files -> 6 chunks; vectors toy-a@1 as FLOAT[16] in vec0.
KNN query (bound parameters: query vector as JSON text, k):
SELECT v.chunk_id, f.path, c.ordinal, c.start_offset, c.end_offset, v.distance
FROM vec_chunk AS v
JOIN chunk AS c ON c.id = v.chunk_id
JOIN file AS f ON f.id = c.file_id
WHERE v.embedding MATCH ? AND k = ?
ORDER BY v.distance
query: 'how do I fix devices dropping off the wifi'
default distance 0.0874 | cosine distance 0.0038 notes/router-setup.md#chunk0 [0:512]
default distance 1.3710 | cosine distance 0.9398 notes/trip-plan.md#chunk0 [0:368]
default distance 1.3866 | cosine distance 0.9613 notes/garden-log.md#chunk0 [0:295]
same order with both metrics: True
query: 'what to do before the storm season'
default distance 1.1317 | cosine distance 0.6404 notes/home-roof-notes.md#chunk0 [0:363]
default distance 1.3044 | cosine distance 0.8507 notes/garden-log.md#chunk0 [0:295]
default distance 1.3142 | cosine distance 0.8635 notes/budget-notes.md#chunk0 [0:298]
same order with both metrics: True
Deleted one file: chunks cascade, vec0 rows do not. Orphaned vector rows: 1 of 6.
After an explicit DELETE on vec_chunk: 5 rows remain.
Read the two distance columns first. The sqlite-vec KNN documentation describes L2, the Euclidean distance, as the default metric, and distance_metric set on the vector column (for example float[768] distance_metric=cosine) as the way to change it. The default column printed 0.0874 for the router note; the cosine table printed 0.0038. They agree because the toy vectors are normalized to length 1, and for unit vectors the squared Euclidean distance equals twice the cosine distance: 0.0874 squared is 0.00764, and half of that is 0.0038. The same holds row by row, so the order is identical. With vectors that are not normalized, the two metrics can rank rows differently, and a table created without the option silently uses L2.
The results are the same three files, in the same order, as Way 1, at the same cosine distances. The extension changed who computes the distance and how the query is spelled, not the answer. The join back to chunk and file by chunk_id is the row-id thread from the storage page: a match is a number and a distance until the join turns it into a path and a span.
The last two lines are the trap. The chunk table references file with ON DELETE CASCADE, so deleting a file removed its chunk. The vec0 row for that chunk stayed, one orphan of six, because a foreign-key cascade does not reach a virtual table. The application must delete from vec_chunk itself, in the same transaction as the file delete.
Common confusion. "sqlite-vec does approximate search" and "sqlite-vec scans every row" are both heard, and the documentation read on 2026-11-18 does not settle the default in so many words. Its KNN page calls the scalar-function approach (ordering a regular table byvec_distance_L2()) a "brute-force KNN query" and describesvec0as faster and more compact; itsvec0page uses "brute-force search over all documents" in a partition-key example. The releases page shows v0.1.9 as the stable line and v0.1.10 alpha pre-releases that mention new IVF and DiskANN features. This page therefore says: in the version run here, treatvec0results as exact unless you have verified an index is in use, and measure them against Way 1 if it matters.
The vec0 table can also carry more than a vector. As documented on 2026-11-18, it supports metadata columns (TEXT, INTEGER, FLOAT, BOOLEAN; up to 16) that can be filtered inside the KNN query with =, !=, >, >=, <, <=; partition key columns (up to 4) that shard the table internally so an = constraint limits the search; and auxiliary columns, written with a + prefix, that are stored and returned but cannot be used in the KNN WHERE. A metadata column is how a filter such as status = 'current' runs inside the vector search instead of after it, which the pipelines page shows is the difference between a full result and a starved one.
Way 3: vec1, the SQLite project's own extension#
The third way surprised the readers of the source pack. The page https://www.sqlite.org/vec1doc.html exists, confirmed by two readers on 2026-11-18. It is titled "Vec1 User Manual", and it documents vec1, a vector extension written by the SQLite project itself. The companion page https://www.sqlite.org/vec1.html calls the current release version 0.8, describes an extension for approximate nearest-neighbor search through SQLite's virtual table interface, and names its approximate method: IVFADC (an inverted file with asymmetric distance computation) with OPQ (optimized product quantization). The pages carry no formal status label.
What the documentation describes, as read on 2026-11-18: a virtual table created with CREATE VIRTUAL TABLE t USING vec1(vector, meta1, ...), with exactly one vector column and up to 255 metadata columns; queries through a table-valued function such as SELECT rowid FROM t(:vec, '{K: 10}'); Euclidean (L2) and cosine distance; two exhaustive modes, "no index" (the default) and "flat index"; and an approximate mode that is trained with a vec1_train() aggregate and applied with a rebuild command. The ann page explains the ideas behind inverted files and product quantization: see ANN.
What is not verified matters as much. Whether vec1 ships in a SQLite release is not confirmed. A forum user wrote on 2026-10-11 that vec1 0.8 is in the main SQLite repository and shipped with 3.54.0; the official 3.54.0 release notes, dated 2026-10-09, do not mention vec1 or any vector extension. Earlier, on 2026-03-30, the SQLite developer who posted the "Vec1 update" said it had no release yet and that testing was seriously lacking; the 0.7 announcement on 2026-07-07 said it was solid enough to drop the "preview" label but was not 1.0. So: vec1 is a documented extension of the SQLite project at version 0.8; whether your SQLite build contains it is something to check, not assume.
None of the vec1 SQL on this page was run here. It is the documented shape only.
The three ways side by side#
With the mechanisms explained, the differences fit in one table. The cells come from the runs above and the documentation read on 2026-11-18.
| Registered function | sqlite-vec vec0 | vec1 | |
|---|---|---|---|
| Who provides it | your code, through the standard library | a community C extension, pre-v1 | the SQLite project, version 0.8 |
| Dependencies | none beyond Python's sqlite3 | a loadable extension and a SQLite that allows loading | an extension built from the SQLite source tree |
| Where vectors live | a BLOB column in your table | a vec0 virtual table joined by row id | a vec1 virtual table |
| Search | exact, every filtered row | KNN form MATCH ? AND k = ?; treat as exact in v0.1.9 unless verified | exhaustive modes plus a trained IVFADC/OPQ mode, as documented |
| Default metric | whatever your function computes | L2 unless distance_metric=cosine | L2, as documented |
| Delete cascade | yes, if the vector is a column of the row | no; delete from the virtual table explicitly | the documentation read does not say; virtual tables have no foreign-key cascade |
| Run on this page | yes | yes (0.1.9) | no, documented shape |
The table makes the separation this site defends visible again. The storage layout (a column or a virtual table), the search algorithm (exact or trained approximate) and the embedding model are three separate choices. Way 1 and Way 2 returned identical rows because the model and the algorithm were the same; only the layout and the implementation of the distance changed.
The lab: private notes, local vectors, nothing leaves#
The lab runs the Mnemos-like corpus in your browser. Six synthetic notes are chunked at one of three sizes (1,000 / 200, 300 / 60 or 150 / 30 characters), embedded with the toy encoder and searched exactly. Each result shows the source path, chunk ordinal, offsets, a SHA-256 prefix and the distance. A checkbox, "an external chat model is configured", switches the "what leaves the machine" panel. Toy encoders and synthetic notes show how the mechanism works, not how good any model or product is.
The same nearest chunks without JavaScript, in this example (cosine distance):
| Query | 1,000 / 200 (6 chunks) | 300 / 60 (9 chunks) | 150 / 30 (20 chunks) |
|---|---|---|---|
| how do I fix devices dropping off the wifi | notes/router-setup.md#0 0.0038 | notes/router-setup.md#0 0.0033 | notes/router-setup.md#1 0.0038 |
| what to do before the storm season | notes/home-roof-notes.md#0 0.6404 | notes/home-roof-notes.md#0 0.6441 | notes/home-roof-notes.md#0 0.3399 |
| make a thick sauce | notes/recipe-notes.md#0 0.0040 | notes/recipe-notes.md#0 0.0040 | notes/recipe-notes.md#1 0.0034 |
| plan for the train trip | notes/trip-plan.md#0 0.0088 | notes/trip-plan.md#1 0.0062 | notes/trip-plan.md#0 0.0031 |
What leaves the machine: nothing by default; with an external model configured, only the retrieved chunks.
Notice the storm-season row. At 1,000 characters the roof note is one chunk, and its gutter sentence is diluted by everything else in the note: distance 0.6404. At 150 characters the first chunk is mostly about the roof and storms, and the distance falls to 0.3399. The file did not change; the unit of retrieval did. That is the subject of the next page.
Mnemos: the same design in a real application#
Mnemos is an open-source, local-first personal RAG application (TypeScript, Next.js). Version 0.21 was read for this site, and the facts below are what that code shows, nothing more.
Diagram: Mnemos in the code read: private files to an answer, inside one SQLite file
Read left to right. Files are chunked and embedded on the machine. Chunks live in ordinary tables, vectors in a vec0 table joined by id. A query vector is made locally and compared. By default nothing leaves the machine; an external model, if configured, sees only the retrieved chunks.
- Ingest
- Private files: The user's documents, registered as rows in ordinary tables.
- Recursive splitter: Cuts each file into chunks of about 1,000 characters with about 200 overlap.
- Local embedding: Each chunk is embedded in process and normalized; no embedding service is called.
- Store
- chunk table: Ordinary rows holding each chunk, linked to its file and source.
- vec_chunk (vec0): An auxiliary virtual table with one vector per chunk id; deletes clear it explicitly.
- Query
- KNN query: Finds the nearest chunk vectors and joins back to chunk, file and source.
- What leaves: Local chat by default; an external model, if configured, receives the retrieved chunks only.
In Mnemos. In the code read, Mnemos stores everything in one SQLite file through better-sqlite3 with the sqlite-vec extension loaded. Source rows live in ordinary tablessource,fileandchunk; vectors live in an auxiliary virtual table,vec_chunk USING vec0(chunk_id INTEGER PRIMARY KEY, embedding FLOAT[384]), joined tochunkby id. The query isWHERE v.embedding MATCH ? AND k = ? ORDER BY v.distance, joined to chunk, file and source.
The details line up with the mechanisms above. Embeddings come from Xenova/bge-small-en-v1.5 (384 dimensions) through @xenova/transformers, run in process with pooling: "mean" and normalize: true; a schema comment names Ollama all-minilm and OpenAI text-embedding-3-small at 384 as alternatives. Chunking is a recursive character splitter of about 1,000 characters with about 200 overlap. Retrieval in the code read is dense only: no BM25 or full-text search and no reranker, plus a second vec0 table for "verified answers". Deletes clear vec_chunk explicitly, because virtual tables have no foreign-key cascade: the exact trap the demo's last two lines showed.
One detail is a lesson in reading code. No distance_metric option is set on the vec0 table, so it uses the documented default, L2. The code comments call the distance "cosine". Both are compatible with correct results here, because the vectors are normalized at embedding time and L2 and cosine then rank the same: the arithmetic from the demo above. If a future model in Mnemos returned unnormalized vectors, the comment would become false in a way that changes rankings. The fix is to state the metric in the table declaration, not in a comment.
Local first: what keeping one file on one machine buys#
Local-first means the data and the computation that touches it stay on the user's own machine by default, and anything sent elsewhere is an explicit, visible choice. For a retrieval system that means three things live locally: the source files, the vectors, and the model that makes the query vector.
In Mnemos, as the code read shows, embeddings and chat are local by default; if an external model is configured, it receives the retrieved chunks only, and an audit log records each query. The lab's checkbox shows the same boundary: nothing leaves, or exactly the chunks that retrieval returned. That second case restates the refrain in privacy terms. The model reads only what retrieval returns, so retrieval also decides what a remote model can see.
Portability follows from the file. Back up the notes database and you back up the corpus, the chunk offsets, the hashes and the vectors together. Move it to another laptop and it opens. Two cautions keep that honest. The file holds vectors made by one model, so it is portable only to a program that uses the same model and preprocessing for queries; the guard from the embeddings page applies here as anywhere. And a sqlite-vec file needs an SQLite that can load the extension on the other side, or its vec0 tables cannot be read.
In our insurance agent. The insurer's agent does not run on SQLite, and nothing on this page suggests it should: its corpora are shared company data with many writers and tenant boundaries, and it runs on PostgreSQL. The same guidance corpus does run in SQLite in this example (sqlite_exact.py, the guidance query on the exact search page), which shows the primitive does not depend on the engine.
The limits: one writer, and a scan that grows with the rows#
SQLite's own guidance is direct about concurrency. As documented on 2026-11-18, it supports an unlimited number of simultaneous readers but allows only one writer at any instant; writers queue. The same page says client/server databases can usually handle far more write concurrency, and that when many processes must write at the same instant and cannot take turns, a client/server engine is the better choice. For a notes app that ingests files in the background while the user searches, one writer is enough. For a shared corpus that many services update at once, it is the limit that decides.
The second limit is arithmetic, not a benchmark. Exact search computes one distance per candidate row, and one distance costs about one multiply-add per dimension. For 100,000 chunks at 384 dimensions, that is 100,000 × 384 = 38.4 million multiply-adds per query, computed here and not measured. Ten times the chunks is ten times the work. Whether that is fast enough depends on the machine, the implementation (a Python function per row in Way 1 is far slower per row than C) and how many rows a filter removes first. The scale page measures instead of guessing, and this page names no row count at which SQLite stops being enough.
How it fails, and the fix for each#
Four failures come straight from the mechanisms above. Each one is quiet: the query still returns k rows.
| Failure | Cause | Fix |
|---|---|---|
| Deleted notes still retrievable by id | vec0 rows are not removed by a foreign-key cascade | delete from the vector table in the same transaction; test for orphans |
| Rankings change after a model swap | the new model returns unnormalized vectors and the table uses the L2 default | declare the metric on the column; normalize; store the model name per row |
| The extension will not load | the platform's SQLite was built without extension loading | use a Python or runtime whose SQLite allows it; check at startup |
| Searches slow down as notes grow | exact work grows with rows; a per-row function call is costly | filter first; measure; consider a compiled distance or an index after measuring |
In a coding agent. A coding agent that indexes a repository on a developer's laptop can keep its chunk vectors in one SQLite file in the project's cache directory: no server, nothing sent off the machine, and the file deleted with the cache. Grep and symbol lookup stay lexical and exact; the vector table answers "find where we retry provider calls". The same delete rule applies: when a refactor removes a file, its rows in the vector table must go too, or the agent will cite code that no longer exists.
A checklist for Monday#
| Question | Why it matters |
|---|---|
| Is the distance metric declared on the table, not assumed? | vec0 defaults to L2; a comment is not a declaration |
| Are the stored vectors normalized, and is that written down? | Then L2 and cosine rank the same; otherwise they may not |
| Does every delete path clear the vector table explicitly? | Virtual tables do not cascade |
| Is extension loading turned off again after loading? | Only the libraries you chose should load |
| Is the model name stored with each vector and checked at query time? | A portable file is portable only to the same model |
| Is the search verified against an exact registered-function query? | It is the answer key for any faster path |
| Do writes come from one place at a time? | SQLite allows one writer at any instant |
Loading native code into the database: the security sideAn extension runs with the application's privileges
A loadable extension is compiled native code. Once loaded, it runs inside the application's process with everything that process can reach: the files, the network, the memory holding other data. That is why SQLite and Python make loading an explicit act. Python's documentation describes enable_load_extension as the switch that allows or disallows loading extensions from shared libraries, and records an audit event for it since Python 3.10.
Three habits follow. Load extensions by an explicit path or package you installed on purpose, never a path built from user input. Turn loading off again right after, as the sqlite-vec Python example does with enable_load_extension(False), so later code that receives SQL from elsewhere cannot load a library. And pin the extension version: sqlite-vec describes itself as pre-v1 with breaking changes expected, and its v0.1.10 alpha notes say DELETEs became quite expensive after a DiskANN data-leak fix. An upgrade is a change to the database engine, tested like one.
Filters inside the vector search: metadata columns and partition keysKeep the filter in the KNN query, not after it
A KNN query with k = 5 returns the five nearest rows of the virtual table. If the application then drops the rows whose status is superseded or whose tenant is another book, it may keep two of five: the starvation that the pipelines page calls a post-filter, and worse, a leak if the drop is forgotten. The registered-function design avoids this naturally, because the filter is an ordinary WHERE on the same table and runs before the LIMIT.
With vec0, the documented tools are metadata columns, filtered inside the KNN query with comparison operators, and partition key columns, which shard the table internally so that an = constraint limits the search to one shard. The documentation advises hundreds of vectors per partition key value; a key with a handful of rows per value fragments the table. vec1 documents a similar rule: comparison terms on its metadata columns are evaluated during the approximate search, other terms afterwards, with a "streaming" mode that keeps producing rows in roughly ascending distance order for filters held in other tables.
The rule for both is the same as on every engine: a condition that must hold for every returned row belongs inside the query that applies k.
One writer: designing ingestion around itQueue the writes, keep transactions short, read freely
SQLite's guidance says writers queue and no lock should last more than a few dozen milliseconds when each application does its work quickly and moves on. Ingestion is where a local RAG app breaks that rule by accident: one transaction that parses a large PDF, computes embeddings for every chunk and inserts them all holds the write lock for the whole time, and the user's search, which is a read, is fine while a second ingestion job waits.
The design that fits one writer computes the expensive parts outside the transaction. Parse and chunk the file, compute the embeddings, then open a short transaction that deletes the old chunks and vectors for that file and inserts the new ones together. The transaction then covers exactly the change that must be atomic: the chunk rows and the vector rows of one file version. A single ingestion queue in the application, rather than several processes writing at once, matches what the documentation describes as SQLite's comfortable range: device-local storage with low writer concurrency.
Using Way 1 as the answer key for Way 2 and Way 3Exact search over the same file checks any faster path
The registered-function query compares every row, so for a given model, filter and metric its top k is the true top k. That makes it the ground truth for any other search over the same vectors in the same file. If a vec0 table is upgraded to a version with an index, or vec1's trained mode is switched on, run a few hundred labeled or sampled queries both ways and compute the share of the exact top k that the faster path returned: index recall, as defined on the ANN page.
Two practical details make the comparison fair. Use the same metric on both sides: compare the registered cosine_distance with a vec0 table declared distance_metric=cosine, or compare L2 with L2. And break ties by id on both sides, as sqlite_exact.py does with ORDER BY dist, c.chunk_id, so two rows at the same distance do not look like a miss.
In one breath#
SQLite is a library inside the program that keeps a whole database in one file, so the first primitive of this site can run with no server at all. Vectors can be searched there three ways: a registered function over a BLOB column (exact, standard library only), the sqlite-vec extension's vec0 virtual table (KNN with MATCH ? AND k = ?, L2 by default, joined back by row id, no delete cascade), or the SQLite project's own vec1 extension (documented at version 0.8 with IVFADC and OPQ; release status unverified). Local first buys privacy and a portable file; it costs one writer at a time and exact work that grows with the rows. Mnemos is this design in a real application.
The notes in this page's example were short enough that each file became one chunk, and the lab already showed the storm-season answer moving when the chunk size changed. Real documents are long: a policy guide, a PDF with tables, a scanned inspection report. Before any of them can be searched, they must be parsed and cut into units, and a cut in the wrong place can split a rule in half. That is the next page, chunking.