Area 08 · Backend · Concept 08 of 20

SQL vs NoSQL

Tables with relations and joins vs flexible documents or key-value. Choose by the shape of your data.

01–10 · The basePhase 0 + 5 · Weeks 1–2, 18–20≈ 1 hour

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.

Why it matters

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

  1. List the entities in your RAG app: users, documents, chunks, chats, messages.
  2. Draw their relationships.
  3. Decide which store each belongs in.

Use it in code

Relational core with JSONB metadata and vectors in one Postgres.

sql
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';
PRACTICE · TICK IT AFTER THIS

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.

Go deeper