All chapters
  1. Start hereRetrieval is a query. The model reads only what it returns
  2. Foundations · 1What retrieval means: lookup, keyword search, semantic search
  3. Foundations · 2Traditional SQL first: WHERE, indexes, full-text search and BM25
  4. Foundations · 3Vectors without AI: coordinates, distance and nearest neighbors
  5. Foundations · 4Embeddings: how a model turns text into a vector
  6. Foundations · 5Choosing an embedding model: quality, cost, privacy and fit
  7. Search · 6Schema and storage: where the vectors live
  8. Search · 7Exact vector search: compare the query with every row
  9. Search · 8Approximate nearest neighbors: HNSW, IVF and the recall trade
  10. Search · 9PostgreSQL and pgvector: vectors inside a relational database
  11. Search · 10SQLite vector search: one file on one machine
  12. Pipeline · 11Chunking and ingestion: from a document to retrievable units
  13. Pipeline · 12Retrieval pipelines: from a question to the context the model reads
  14. Pipeline · 13Hybrid search: words and vectors together
  15. Pipeline · 14Reranking: a second, more careful look at the candidates
  16. Pipeline · 15Top-k and scale: ten thousand, a hundred thousand, a million rows
  17. Pipeline · 16Two corpora: similar applicants and underwriter precedents
  18. Pipeline · 17Grounded generation: citations, abstention and conflicts
  19. Pipeline · 18Evaluating retrieval: recall@k, MRR, nDCG and faithfulness
  20. Practice · 19Google Cloud choices: Cloud SQL, AlloyDB, Vector Search, Agent Retrieval, RAG Engine, Agent Search
  21. Practice · 20Other ecosystems: libraries, engines and managed services
  22. Practice · 21Operations: migrations, deletes, permissions and freshness
  23. Practice · 22Advanced retrieval: rewriting, hierarchies, late interaction, graphs, agents
  24. Practice · 23Reference architectures: local-first, dual-corpus, managed, Postgres-native
  25. Practice · 24Framework recipes and the decision guide
  26. ReferenceGlossary
  27. AboutAbout this site and its sources
Search · 6

Schema and storage: where the vectors live

A vector column in the same row, an auxiliary vector table, a SQLite virtual table, a separate vector database, or a reference to an object. Each layout keeps the row id that joins a match back to its source.

Five places a vector can live
A column in the source row, an auxiliary table, a SQLite virtual table, a separate vector database, or beside a reference to the original document.
The row id is the thread
Every match must lead back to its source row through a stable row id. Lose that join and retrieval returns vectors nobody can cite.
Layout is not search
Where vectors are stored is separate from how they are searched (exact or approximate) and from which model made them. Choose each on its own.

Policy version 2026.2 of the insurer's guidance takes effect on 2026-07-01. The ingestion job marks the 2025.4 passages as superseded in the guidance table, inserts the new passages, and embeds them. On the database side, everything is correct.

Now picture an illustrative prototype in which the vectors lived in a separate store, together with a copy of each passage's status so that searches could filter on it. The job that updated the guidance table finished; the job that updated the copies in the vector store failed halfway and retried the next night. For a day, a search filtered on status = 'current' still returned the superseded 2025.4 roof rule, with its 30-year limit, next to the current 25-year rule. That is this site's case UW-R03 again, but this time the filter was there. It was filtering a stale copy, and that copy was the mistake: a filter must read the system of record, or a copy kept in step with it.

A second symptom appeared in the same week. A broker's private attachment was deleted from the source tables, and its chunk still came back in a search, because deleting the source row had not deleted its vector. Neither failure came from the model or the search algorithm. Both came from where the vectors were stored and how the store stayed in step with the rows. That is the subject of this page.

What this is not. This page compares storage layouts, not products; the dated product matrix is on other ecosystems. How a stored vector is searched, exactly or through an index, is on exact search and ANN. pgvector and sqlite-vec details live on pgvector and SQLite.

The question a layout answers#

Recall the primitive this site keeps true. 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. A layout decides two concrete things about that primitive: where each row's vector is physically kept, and how a match, which is a vector and a distance, becomes a source row the agent can read and cite.

The second question has one answer in every layout: a row id. A row id is the stable identifier that the vector and its source row share, such as profile_id = 'PRF-0218' or passage_id = 'GD-2026.2-R-ROOF-03'. A match returns the id, the id finds the source row, and the source row carries what the agent needs: the passage text, the policy_version, the tenant, the evidence id to cite. A vector without a reliable id is a number nobody can quote.

Before AI. Teams have stored searchable copies of their data next to a database for decades. A Lucene or Elasticsearch index kept beside PostgreSQL holds document ids and indexed fields; a search returns ids, and the application loads the rows. The known costs came with it: two writes per change, a copy that can lag, and deletes that must reach both. A separate vector store has the same shape and the same costs.

Layout, search algorithm and model are three separate choices#

Before the layouts, one separation this site defends everywhere. Teams often say "we need a vector database" when they mean one of three different things.

The storage layout is where vectors live: in the row, in a side table, in a virtual table, in a dedicated engine. The search algorithm is how the nearest rows are found: exact search compares the query with every candidate row, and an approximate index skips most rows and may miss some true neighbors. The embedding model is what made the vectors, and it alone defines their space.

Diagram: Storage layout, search algorithm and embedding model are three independent choices

Read each lane on its own. The layout decides where vectors live and how a match finds its source row. The algorithm decides how the nearest rows are found. The model decides what the coordinates mean. Any layout can run either algorithm over vectors from any model. Click a box for an example of each.

  1. Storage layout
    1. Column in the row: Where each row's vector is kept and how the row id leads back to the source.
    2. Separate engine: Vectors kept outside the source table, joined or fetched back by id.
  2. Search algorithm
    1. Exact search: Compares the query with every candidate row; returns the true top k.
    2. Approximate index: Skips most rows to do less work, and may miss a true neighbor.
  3. Embedding model
    1. Model identity: Decides what the coordinates mean; a change means re-embedding wherever vectors live.

The diagram shows three independent dials. PostgreSQL with pgvector can store vectors in the row and search them exactly, or build an HNSW index over the same column; the layout does not change. A dedicated engine can run exact or approximate search too. And any layout can hold vectors from any model; a model change means re-embedding wherever the vectors live. Picking a layout does not pick an algorithm or a model, and the reasons for each choice are different: layout is about consistency and operations, algorithm about work per query and recall, model about where meaning lands.

Layout 1: a vector column in the source row#

The simplest layout adds a column of vector type to the table that already holds the data. A vector column is a column whose value in each row is that row's embedding. The example's schema does this for all three corpora. Here is the guidance table from examples/rag-uw/sql/schema.sql (it ran unchanged in the recorded throwaway run of sql/OUTPUT.md); the exact metadata, the text, the model identity and the vector sit in one row.

CREATE TABLE guidance_passages (
  passage_id      text PRIMARY KEY,
  rule_id         text,                                   -- NULL for notes and attachments
  policy_version  text,                                   -- '2026.2' current, '2025.4' superseded
  effective       date,
  status          text NOT NULL CHECK (status IN ('current', 'superseded')),
  section         text NOT NULL,
  source          text NOT NULL,                          -- 'UW-GUIDE' or 'broker attachment (untrusted)'
  trusted         boolean NOT NULL,
  submission_id   text,                                   -- set only for attachments
  body            text NOT NULL,
  body_tsv        tsvector GENERATED ALWAYS AS (to_tsvector('english', body)) STORED,
  embedding_model text NOT NULL,
  embedding       vector(16) NOT NULL
);

In this layout the row id path is the shortest possible: the match is the row. A query that filters on policy_version and sorts by embedding <=> $1 reads the filter value and the vector from the same row, in the same snapshot.

The bigger gain is consistency. In PostgreSQL, a change to a row's status and the vector that goes with it happen in one transaction: either both are visible or neither is. Q14 of sql/queries.sql shows it on the throwaway database (PostgreSQL 16.14, pgvector 0.8.3; one run on one laptop, toy data; not a benchmark). Inside a transaction it marks the current R-ROOF-01 passage as superseded, reads it back, and rolls back. A successful run proves the status change is visible inside the transaction and gone after the rollback, together with everything else in that row, including its vector; it does not test concurrent readers.

BEGIN;
UPDATE guidance_passages SET status = 'superseded' WHERE policy_version = '2026.2' AND rule_id = 'R-ROOF-01';
SELECT passage_id, status FROM guidance_passages WHERE rule_id = 'R-ROOF-01' ORDER BY passage_id;
ROLLBACK;
SELECT passage_id, status FROM guidance_passages WHERE rule_id = 'R-ROOF-01' ORDER BY passage_id;
== Q14 a policy revision is transactional: rows and their vectors change together (rolled back)
BEGIN
UPDATE 1
     passage_id      |   status   
---------------------+------------
 GD-2025.4-R-ROOF-01 | superseded
 GD-2026.2-R-ROOF-01 | superseded

ROLLBACK
     passage_id      |   status   
---------------------+------------
 GD-2025.4-R-ROOF-01 | superseded
 GD-2026.2-R-ROOF-01 | current

The opening incident cannot happen in this layout. The status that a search filters on is the row's own status, not a copy, so no reader ever sees a new status with an old vector or the reverse. Deleting the row deletes its vector, because the vector is part of the row.

The costs are real too. Every row grows by the vector: pgvector documents its vector type as 4 bytes per dimension plus 8, so 1,544 bytes per row at 384 dimensions. A second model during a migration means a second vector column on the same table. And the vector search runs on the same database as everything else, sharing its memory and CPU.

In the insurance build. In that build, both corpora use this layout: PostgreSQL 16 with pgvector, vectors in a column of the customer_profiles and precedents tables, queried with ORDER BY embedding <=> :q. A model change is planned as a new column, a backfill, then a switch of reads.

Layout 2: an auxiliary vector table#

The second layout keeps the source rows free of vectors and puts the vectors in a separate table keyed by the same id. An auxiliary vector table is a table whose rows hold one vector each, plus the id of the source row it belongs to. A search ranks the auxiliary rows and joins them back to the source table by id.

The script below builds both layouts for Corpus A in standard-library SQLite, with the same 240 synthetic applicant profiles, the same toy vectors and a registered cosine_distance function, then runs SUB-1004's profile query (case UW-R04) against each. It is meant to show that the layout changes the SQL and the update rules, never the result. A successful run proves both layouts return the same three rows in the same order, and that a foreign key with ON DELETE CASCADE removes the auxiliary vector when its source row is deleted. It is exact search over a few hundred rows, not a performance comparison.

# layouts.py, run from a scratch copy of examples/rag-uw (standard-library SQLite, toy encoder, synthetic rows)
import sqlite3

import data
from encoders import TOY_A1
from sqlite_exact import pack, sql_cosine_distance

con = sqlite3.connect(":memory:")
con.execute("PRAGMA foreign_keys = ON")
con.create_function("cosine_distance", 2, sql_cosine_distance, deterministic=True)
con.executescript("""
    -- Layout 1: the vector is a column of the source row.
    CREATE TABLE profile_inrow (profile_id TEXT PRIMARY KEY, submission_ref TEXT, occupancy TEXT,
        wind_zone TEXT, tenant TEXT, embedding_model TEXT NOT NULL, embedding BLOB NOT NULL);
    -- Layout 2: source rows without vectors, plus an auxiliary vector table keyed by the same id.
    CREATE TABLE profile (profile_id TEXT PRIMARY KEY, submission_ref TEXT, occupancy TEXT,
        wind_zone TEXT, tenant TEXT);
    CREATE TABLE profile_vec (profile_id TEXT PRIMARY KEY REFERENCES profile(profile_id) ON DELETE CASCADE,
        embedding_model TEXT NOT NULL, embedding BLOB NOT NULL);
""")
for p in data.applicant_profiles():
    blob = pack(TOY_A1.encode_document(p["summary"]))
    src = (p["profile_id"], p["submission_ref"], p["occupancy"], p["wind_zone"], p["tenant"])
    con.execute("INSERT INTO profile_inrow VALUES (?, ?, ?, ?, ?, ?, ?)", src + (TOY_A1.name, blob))
    con.execute("INSERT INTO profile VALUES (?, ?, ?, ?, ?)", src)
    con.execute("INSERT INTO profile_vec VALUES (?, ?, ?)", (p["profile_id"], TOY_A1.name, blob))

q = pack(TOY_A1.encode_query(data.QUERY_PROFILES["SUB-1004"]["summary"]))
params = (q, "C", "book-east", TOY_A1.name, 3)
inrow = con.execute("""
    SELECT profile_id, submission_ref, occupancy, round(cosine_distance(embedding, ?), 4) AS dist
    FROM profile_inrow WHERE wind_zone = ? AND tenant = ? AND embedding_model = ?
    ORDER BY dist, profile_id LIMIT ?""", params).fetchall()
aux = con.execute("""
    SELECT p.profile_id, p.submission_ref, p.occupancy, round(cosine_distance(v.embedding, ?), 4) AS dist
    FROM profile_vec AS v JOIN profile AS p ON p.profile_id = v.profile_id
    WHERE p.wind_zone = ? AND p.tenant = ? AND v.embedding_model = ?
    ORDER BY dist, p.profile_id LIMIT ?""", params).fetchall()
print("in-row column:   ", inrow)
print("auxiliary table: ", aux)
print("same rows, same order:", inrow == aux)

con.execute("DELETE FROM profile WHERE profile_id = ?", ("PRF-0218",))
left = con.execute("SELECT count(*) FROM profile_vec WHERE profile_id = 'PRF-0218'").fetchone()[0]
print("deleted PRF-0218 from profile; its vector rows left in profile_vec:", left, "(foreign key ON DELETE CASCADE)")
in-row column:    [('PRF-0218', 'SUB-2218', 'office', 0.0018), ('PRF-0206', 'SUB-2206', 'retail', 0.0045), ('PRF-0091', 'SUB-2091', 'office', 0.0048)]
auxiliary table:  [('PRF-0218', 'SUB-2218', 'office', 0.0018), ('PRF-0206', 'SUB-2206', 'retail', 0.0045), ('PRF-0091', 'SUB-2091', 'office', 0.0048)]
same rows, same order: True
deleted PRF-0218 from profile; its vector rows left in profile_vec: 0 (foreign key ON DELETE CASCADE)

Both layouts return PRF-0218, PRF-0206 and PRF-0091 at distances 0.0018, 0.0045 and 0.0048, the same rows the PostgreSQL run returns for UW-R04. Every query is bound: the vector, the two filter values, the model name and k are ? parameters, never text pasted into SQL.

The last line depends on two things the script sets on purpose. The foreign key declares ON DELETE CASCADE, which in both PostgreSQL and SQLite deletes the referencing rows when the referenced row is deleted. And SQLite enforces foreign keys only after PRAGMA foreign_keys = ON on each connection; its documentation says they are disabled by default. Without either, the delete would leave an orphaned vector, the second symptom of the opening.

An auxiliary table pays for its join with flexibility. Source rows stay narrow for queries that never touch vectors. Vectors from two models can coexist, as rows keyed by id and model, which makes a migration a matter of inserting rows rather than altering a hot table. And because the auxiliary table is in the same database, a transaction can still cover the source row and its vector together.

Layout 3: a SQLite virtual table#

SQLite has a mechanism for tables whose storage and search are implemented by an extension. A virtual table, in SQLite's documentation, is an object registered with a database connection that looks like any other table to an SQL statement, while queries and updates call the extension's code instead of reading the database file directly. sqlite-vec provides one called vec0: a vector table with its own KNN query form, WHERE embedding MATCH ? AND k = ?.

A vec0 table is used as an auxiliary vector table: it holds a chunk id and a vector, and the source rows live in ordinary tables. The difference is in what a virtual table cannot do. As documented on 2026-11-18, SQLite does not allow a trigger on a virtual table or additional indexes on it with CREATE INDEX. In practice the vec0 rows are not removed when a source row is deleted, and the application must delete them itself. The example's sqlite_vec_demo.py shows it; below is its recorded run from SQLITE_VEC_OUTPUT.md (sqlite-vec 0.1.9 in a throwaway virtual environment, SQLite 3.53.4, toy encoder, synthetic notes; trimmed with "..." where marked).

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
...
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.

The chunk table references file with a cascading foreign key, so deleting a file removed its chunk. The vec0 row for that chunk stayed: one orphaned vector of six. The KNN query above joins back through chunk, so the orphan no longer reaches the agent in this query; but it still occupies the vector table, a query without the join would see it, and a reused chunk id would attach an old vector to a new chunk. The fix is the explicit DELETE on vec_chunk in the same transaction as the file delete.

In Mnemos. In the code read, Mnemos keeps its source rows in ordinary tables source, file and chunk, and its vectors in a separate virtual table, vec_chunk USING vec0(chunk_id INTEGER PRIMARY KEY, embedding FLOAT[384]), joined to chunk by id. Because virtual tables have no foreign-key cascade, its delete paths clear vec_chunk explicitly.

Layout 4: a separate vector database#

The fourth layout moves vectors into a dedicated engine whose main job is vector search: a vector database, on this site's terms. pgvector and sqlite-vec are extensions to databases you already run, not vector databases. A separate engine stores, per item, an id, a vector and usually a copy of the metadata you want to filter on. A search returns ids and distances or scores; the application then fetches the source rows by id from the system of record.

That shape is the opening incident waiting to happen. The source database and the vector engine do not share a transaction, so every change is two writes that can succeed or fail separately, and the filterable metadata is a copy that can lag behind the source. A status change, a tenant move or a delete must reach the engine before a search can be trusted, and a search in between returns what the copy says.

Teams accept those costs for reasons that can be real: a corpus or query load that the existing database should not carry, features an engine documents and the database does not, or an organizational boundary. The scale page measures the first reason instead of assuming it, and other ecosystems reads the products' documentation; this page names no threshold. Whatever the reason, the source of truth for status, tenant and policy_version stays in one place, and the engine's copy is treated as a cache with a reconciliation job.

Layout 5: a reference to the original document#

Some sources are not rows at all: a PDF in object storage, a markdown file on a laptop, a broker's attachment. The layout for them stores a reference instead of the document. An object reference is a pointer to where the original lives, such as a path or URI, kept with a content hash and the offsets of each chunk. The chunk's text and vector live in one of the layouts above; the reference ties every match to an exact span of an exact version of the original.

The example's standard-library SQLite file, sqlite_exact.py, stores Mnemos-like notes this way: a file row with its path and content hash, and chunk rows with the file id, an ordinal, start and end offsets, a chunk hash and the vector. A result such as notes/router-setup.md#chunk0 [0:512] sha256=056755dec40e... can be cited down to the character, and a changed file is detected because its hash no longer matches. The reference does not keep itself fresh: if the original changes or disappears, the chunks must be rebuilt or removed, which the chunking page covers.

The lab: one query, three layouts#

The lab runs the same Corpus A query for SUB-1004 under three layouts: the vector column in the row, the auxiliary vector table joined by profile_id, and a managed vector store that returns ids and scores before the rows are fetched by id. Pick a match and follow its row id from the vector to the source row; each layout shows its SQL, or its documented shape, and what an update or a delete means there. The vectors come from the toy encoder and the rows are synthetic.

The same result without JavaScript, in this example (query = SUB-1004's banded profile, toy-a@1, exact search, filter wind_zone = 'C'):

MatchCosine distanceSource rowVector column in the rowAuxiliary vector tableManaged store
PRF-02180.0018SUB-2218, office, masonry, built 1994the match is the rowjoin on profile_idfetch by id
PRF-02060.0045SUB-2206, retail, frame, built 1960samesamesame
PRF-00910.0048SUB-2091, office, concrete, built 1979samesamesame

Diagram: One match, PRF-0218, followed from its vector to its source row in three layouts

Read each lane left to right. The distance is the same in every layout. In the row, the match is the source row. In an auxiliary table, the id joins back. In a separate store, the application fetches the row by id from the source of record. Each hop is a place where the two sides can disagree.

  1. Vector column in the row
    1. Match = row: The vector, the filter values and the text are in one row; no join.
  2. Auxiliary vector table
    1. Vector row: Holds one vector per source row, keyed by the same id.
    2. Source row: The join by id brings back the text and the exact metadata.
  3. Separate vector store
    1. Id and score: The store returns ids and scores; its metadata is a copy that can lag.
    2. Fetch by id: The application loads the source row; there is no shared transaction.

The diagram follows one match, PRF-0218, through each layout. The distance is identical everywhere; only the number of hops to the source row and the number of systems that must agree differ. Every extra hop is a place where an id can point to a row that changed, or a row can be gone while its vector remains.

Updates and consistency, layout by layout#

With the layouts side by side, the decision is mostly about changes, not about search. The table summarizes what the sections above showed.

LayoutMatch to sourceRow and vector change togetherDelete removes the vectorFilters readSecond model during migration
Vector column in the rowthe match is the rowyes, one row in one transaction (Q14)yes, part of the rowthe row's own valuesa second column
Auxiliary vector tablejoin on the row idyes, if in the same database and transactionwith a cascading foreign key, enforcedthe source row after the joinmore rows keyed by id and model
SQLite virtual table (vec0)join on the row idyes, same databaseno cascade; delete explicitlythe joined source row, or the extension's own columnsa second virtual table
Separate vector databasefetch by id from the sourceno shared transactiononly if the application deletes it therea copy of metadata, possibly stalea second collection or index
Reference to a documenta path, hash and offsetsthe reference does not update itselfonly if a job noticesthe chunk's metadatare-chunk and re-embed from the original

Read the table from left to right as a gradient of moving parts. The in-row column has none: one row, one transaction, one truth. Each step to the right adds a hop or a system, and with it a job that must run for the data to stay correct. That is not a reason to avoid the right-hand layouts; it is the work they require.

Production pitfall. A filter on a copied field is only as current as the copy. If status, tenant or policy_version live in a separate store for filtering, every search must be able to tolerate them being stale, or the source must be re-checked after the join. For tenant isolation (this site's case UW-R07) a stale copy is a leak, so the permission check reads the system of record.
In a coding agent. A coding agent that indexes a repository faces the same choice. Chunk vectors can live in a file beside the code, in a local SQLite table, or in a hosted store. Whatever the layout, each vector needs a stable id that leads back to a file path, a commit or content hash, and line offsets, so the agent can open the exact lines it cites. After a refactor, a chunk whose file was deleted must lose its vector too, or the agent will confidently point to code that no longer exists.

A checklist for Monday#

QuestionWhy it matters
Does every vector carry a stable row id that leads to its source row?A match must be citable
Is the filter metadata read from the system of record, or from a copy?A stale copy returns superseded or foreign rows
Do a row and its vector change in one transaction?Otherwise readers can see one without the other
Does deleting a source row delete its vector in every layout?Virtual tables and separate stores do not cascade
Are foreign keys actually enforced (SQLite needs PRAGMA foreign_keys = ON)?A declared cascade that is off does nothing
Is the model identity stored next to each vector, wherever it lives?The guard from UW-R06 needs it
Was the layout chosen separately from the algorithm and the model?They answer different questions
Two systems, one change: ordering and reconciliationWhat to do when vectors live outside the system of record

When the source rows and the vectors live in different systems, a single change becomes two writes, and any of them can fail. Writing the source first and the vector second leaves a window in which the source says one thing and the vector store another. Writing the vector first can publish a vector for a row that is then rolled back.

A common design records the intent in the source database, in the same transaction as the change: a row in an outbox table saying "passage GD-2026.2-R-ROOF-01: status changed" or "chunk 42: delete". A worker reads the outbox and applies each change to the vector store, retrying until it succeeds, and marks it done. The source transaction is then the only place a change happens; the vector store follows, late but in order.

The outbox narrows the gap but does not close it, so add two more defenses. Re-check what matters after the fetch by id: the status, tenant and policy_version of the source row, not of the copy. And run a reconciliation job that compares the ids on both sides and repairs differences. The operations page covers deletes everywhere and the migration that runs on top of this.

Row ids that survive changeNever reuse an id, and know which ids change when you re-chunk

The row id is the only thing every layout shares, so it must stay meaningful for as long as any copy of the vector exists. Two rules follow. Never reuse an id for a different row: an orphaned vector in a virtual table or a lagging store would then attach an old vector to a new, unrelated row. And keep the id stable across updates of the same row, so a changed passage replaces its vector instead of adding a second one.

Chunks complicate this. If chunk ids are positions such as #chunk0, #chunk1, re-chunking a document with a new size shifts every id. Treat a re-chunk as delete-and-insert for the whole document, keyed by the document id and its content hash, rather than as updates by position. Mnemos-like rows in this example carry the file path, a content hash and the offsets for exactly this reason.

Natural ids such as passage_id = 'GD-2026.2-R-ROOF-03' are readable and stable when the source guarantees them. A surrogate integer is compact and works well inside one database; when the vector leaves that database, the surrogate travels with it and must never be recycled.

Two models at once: what the migration needs from the layoutSide-by-side vectors are a layout decision made early

Every model change requires a period in which old and new vectors coexist: the new ones are backfilled while queries still use the old ones, then reads switch, and the old ones remain until the switch is proven. The layout decides how awkward that period is.

With a vector column in the row, the new model gets a new column; the table carries both until the old column is dropped, and each row grows by the second vector. This is the insurance build's plan: a new column, a backfill, then a switch of reads. With an auxiliary table keyed by id and model, the new vectors are new rows, and the guard in the query (embedding_model = $2) selects which ones a query may compare with; nothing about the source table changes. With a virtual table, the new model usually gets a second virtual table, because the vector dimension is part of its declaration. With a separate engine, it gets a second collection or index, and both must be kept in step with the source during the overlap.

In every case the old vectors are not reindexed into the new space; they are replaced by re-embedding. The operations page plans the full sequence with a rollback.

In one breath#

A storage layout decides where each row's vector lives and how a match leads back to its source row, and every layout depends on a stable row id. A vector column in the row needs no join and changes together with the row in one transaction; an auxiliary table adds a join and keeps deletes correct with an enforced cascading foreign key; a SQLite virtual table has no cascade, so deletes are explicit; a separate vector database adds a second system whose metadata copy can lag; a reference ties chunks to an exact span of an original document. The layout is chosen for consistency and operations, separately from the search algorithm and the embedding model.

The agent's vectors now have a home, and every match can be followed to a citable row. What it has not yet done is the search itself in SQL: compare the query vector with every candidate row, apply the exact filters, sort and keep k, with parameters bound and the plan visible. That is the next page, exact search, and its result is the ground truth every faster method is measured against.