The idea
Relational (SQL) databases like Postgres store data in tables with defined columns and relationships, enforce constraints, and support joins and transactions. NoSQL covers several shapes: document stores (MongoDB, Firestore) for nested JSON, key-value stores (Redis, DynamoDB) for fast lookups by key, wide-column and graph databases.
Choose by access patterns and consistency needs. Users, organisations, documents, permissions and billing are relational; Postgres handles them well and, with pgvector and JSONB columns, also covers embeddings and flexible metadata. Redis is ideal for caches, rate limits and queues.
Most AI products end up with Postgres as the system of record plus Redis, and maybe object storage for files.
Choosing storage by hype rather than data shape creates years of pain.
Words to know
- SchemaThe defined structure of tables and columns.
- JSONBPostgres’s binary JSON column type, indexable.
Try it without code
Model your data
- List the entities in your RAG app: users, documents, chunks, chats, messages.
- Draw their relationships.
- Decide which store each belongs in.
Use it in code
Relational core with JSONB metadata and vectors in one Postgres.
CREATE TABLE users (id uuid PRIMARY KEY, email text UNIQUE NOT NULL, created_at timestamptz DEFAULT now());
CREATE TABLE documents (
id uuid PRIMARY KEY,
owner_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
title text NOT NULL,
metadata jsonb NOT NULL DEFAULT '{}'::jsonb
);
CREATE TABLE chunks (
id bigserial PRIMARY KEY,
document_id uuid NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
body text NOT NULL,
embedding vector(384)
);
SELECT d.title, c.body FROM chunks c JOIN documents d ON d.id = c.document_id
WHERE d.metadata->>'type' = 'policy';Write the full schema for your capstone project and justify any data you keep outside Postgres.
Common mistakes
- Choosing a document DB, then rebuilding joins in application code.
- Storing everything as one JSON blob.