recallA retrieval
field guide

Schema Design for Vector Storage

Build this skill

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.

documentsdoc_id (primary key)title, source_urlcollection, owneraccess, languageversion, statuseffective_from, hashchunkschunk_id (primary key)doc_id → documentsposition, section, pagecontentembedding vector(1024)embedding_model

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.

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

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

python · runs in your browser

Expected output:

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

Follow your curiosity.

↑ ↓ to explore · Enter to open · Escape to close