2.2.2. Schema Modeling and Data Types

💡 First Principle: A RAG schema is designed around retrieval units, not entities. You store chunks — retrievable passages sized for a prompt — each carrying its text, its vector, and enough metadata to filter by. Model the chunk well and every downstream query gets simpler.

The canonical RAG table shape, after enabling the extension (CREATE EXTENSION vector;):

CREATE TABLE chunks (
    id          uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    doc_id      uuid NOT NULL REFERENCES documents(id),
    content     text NOT NULL,
    metadata    jsonb NOT NULL DEFAULT '{}',
    embedding   vector(1536) NOT NULL,
    created_at  timestamptz NOT NULL DEFAULT now()
);

Each type earns its place. vector(1536) comes from pgvector and its dimension is fixed per column — it must equal your embedding model's output size exactly. jsonb holds flexible metadata (source, language, department) without schema churn, and supports GIN indexing for fast containment filters (2.2.3). text avoids arbitrary length caps; timestamptz over naive timestamps; uuid keys generate safely across distributed writers.

Normalization is a judgment call the exam respects: documents and chunks split into two tables (change a document's title once, not per chunk), while heavily filtered attributes might be denormalized onto chunks to keep retrieval single-table. The reasoning tool: queries at retrieval time should touch as few tables as possible, because retrieval sits on the user's critical path — the same latency logic that motivated caching in the 1.2.2 job map.

⚠️ Exam Trap: Inserting a 768-dimension embedding into a vector(1536) column fails outright — dimension is enforced per column. Scenarios about "switching embedding models" imply a schema migration and full re-embedding, not just new inserts.

Reflection Question: Why store chunk metadata in jsonb rather than as columns — and when does that reasoning flip?