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.
- Storage layout
- Column in the row: Where each row's vector is kept and how the row id leads back to the source.
- Separate engine: Vectors kept outside the source table, joined or fetched back by id.
- Search algorithm
- Exact search: Compares the query with every candidate row; returns the true top k.
- Approximate index: Skips most rows to do less work, and may miss a true neighbor.
- Embedding model
- 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 thecustomer_profilesandprecedentstables, queried withORDER 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 tablessource,fileandchunk, and its vectors in a separate virtual table,vec_chunk USING vec0(chunk_id INTEGER PRIMARY KEY, embedding FLOAT[384]), joined tochunkby id. Because virtual tables have no foreign-key cascade, its delete paths clearvec_chunkexplicitly.
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'):
| Match | Cosine distance | Source row | Vector column in the row | Auxiliary vector table | Managed store |
|---|---|---|---|---|---|
PRF-0218 | 0.0018 | SUB-2218, office, masonry, built 1994 | the match is the row | join on profile_id | fetch by id |
PRF-0206 | 0.0045 | SUB-2206, retail, frame, built 1960 | same | same | same |
PRF-0091 | 0.0048 | SUB-2091, office, concrete, built 1979 | same | same | same |
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.
- Vector column in the row
- Match = row: The vector, the filter values and the text are in one row; no join.
- Auxiliary vector table
- Vector row: Holds one vector per source row, keyed by the same id.
- Source row: The join by id brings back the text and the exact metadata.
- Separate vector store
- Id and score: The store returns ids and scores; its metadata is a copy that can lag.
- 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.
| Layout | Match to source | Row and vector change together | Delete removes the vector | Filters read | Second model during migration |
|---|---|---|---|---|---|
| Vector column in the row | the match is the row | yes, one row in one transaction (Q14) | yes, part of the row | the row's own values | a second column |
| Auxiliary vector table | join on the row id | yes, if in the same database and transaction | with a cascading foreign key, enforced | the source row after the join | more rows keyed by id and model |
SQLite virtual table (vec0) | join on the row id | yes, same database | no cascade; delete explicitly | the joined source row, or the extension's own columns | a second virtual table |
| Separate vector database | fetch by id from the source | no shared transaction | only if the application deletes it there | a copy of metadata, possibly stale | a second collection or index |
| Reference to a document | a path, hash and offsets | the reference does not update itself | only if a job notices | the chunk's metadata | re-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. Ifstatus,tenantorpolicy_versionlive 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 caseUW-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#
| Question | Why 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.