Schema Design for Vector Storage
Design a documents-and-chunks schema with columns, JSONB metadata and cascading deletes.
Deciding what to store, and where
A schema is the design of your tables: which tables exist, which columns each has, and how they link. For a vector store, a good schema makes it easy to cite sources, filter by metadata, update one document, keep old versions apart, and track which model produced each embedding.
The most common mistake is one wide table with a row per chunk, where the document's title, URL, owner and access level are repeated in every row. It works until a document's access level changes and 300 rows need updating, or two chunks of the same document disagree about its title.
A college office avoids this without thinking. A student's name, branch and phone number are written once, in the student register, and the marks sheets carry only the roll number. A change of phone number changes one line.
Documents and chunks: two linked tables
Each document is stored once, with the facts that are true for the whole document. Each chunk stores only what is specific to that piece (its text, position, section and embedding) and points to its document by ID.
Document-level facts are stored once. Chunks link to their document and hold what is specific to each piece.
In SQL with pgvector, the design looks like this.
CREATE TABLE documents (
doc_id text PRIMARY KEY,
title text NOT NULL,
source_url text,
collection text NOT NULL, -- e.g. 'academics', 'hostel'
access text NOT NULL, -- e.g. 'students', 'staff'
language text DEFAULT 'en',
version int NOT NULL,
status text NOT NULL, -- 'current' or 'superseded'
effective_from date,
content_hash text NOT NULL
);
CREATE TABLE chunks (
chunk_id bigserial PRIMARY KEY,
doc_id text NOT NULL REFERENCES documents(doc_id) ON DELETE CASCADE,
position int NOT NULL, -- order within the document
section text,
page int,
content text NOT NULL,
metadata jsonb DEFAULT '{}', -- flexible extra fields
embedding vector(1024) NOT NULL,
embedding_model text NOT NULL -- e.g. 'bge-m3'
);
CREATE INDEX ON chunks USING hnsw (embedding vector_cosine_ops);
CREATE INDEX ON chunks (doc_id);
| Piece | Why it is there |
|---|---|
REFERENCES documents(doc_id) |
A foreign key: the database refuses a chunk whose document does not exist. |
ON DELETE CASCADE |
Deleting a document deletes its chunks automatically, so no orphaned chunk turns up in search results. |
status, version |
Let a search skip superseded versions without deleting them. |
content_hash |
A fingerprint of the text. If it has not changed, the document need not be re-processed. |
embedding_model |
Records which model produced each vector, which you need on the day you switch models. |
Fixed columns or a flexible JSON field?
Some metadata is used in almost every query, such as access level and status. Other metadata is occasional or exists only for some kinds of document, such as a circular number or a course code. PostgreSQL's JSONB type holds a flexible set of named fields in one column, so new kinds of metadata can be added without changing the table.
A workable rule is to give a field its own column when you filter on it often, because columns are faster to filter, easier to index and checked by the database. Occasional or varied fields go into JSONB.
Searching with the schema
Because document-level facts live in documents, a search joins the two tables. A join matches each chunk with its document row using doc_id, so one query can find the closest chunks, keep only current documents the user may see, and return what a citation needs.
SELECT d.title, c.section, c.page, c.content,
1 - (c.embedding <=> :q) AS similarity
FROM chunks c
JOIN documents d ON d.doc_id = c.doc_id
WHERE d.status = 'current'
AND d.access = ANY(:user_roles)
ORDER BY c.embedding <=> :q
LIMIT 5;
FROM chunks c JOIN documents d pairs every chunk with its document (c and d are short names for the two tables). The WHERE lines drop superseded documents and those this user's roles do not allow, and ORDER BY ... LIMIT 5 keeps the five closest of what remains.
Trying the design on a small example
The program below builds the two tables in SQLite, a small database that ships with Python. SQLite has no vector type, so the embedding column is left out; the structure is what is being tested.
Expected output:
students can see: ['Circular 14: Diwali break from 30 October.']
rows updated to open the regulations to students: 1
students can now see: 3 chunks
chunks left after deleting circ-14: 2
PRAGMA foreign_keys = ON is needed because SQLite only enforces foreign keys, and so the cascade, when asked; PostgreSQL always does. visible_to_students() joins chunks to documents and keeps the chunks whose document is open to students. .rowcount reports how many rows the UPDATE touched.
Opening the regulations to students changed one row, and both of its chunks became visible at once. Deleting the circular removed its chunk without a second statement.
Collections: one table or many?
A knowledge base usually has several collections, such as academics, hostel and placements. They can be a column (collection = 'hostel') in shared tables, or separate tables. A column is simpler and lets one query search across collections. Separate tables make sense when collections use different embedding models (with different dimensions), differ hugely in size, or must be kept strictly apart, for example separate customers of one product.