Lesson 1 · Foundations

Postgres & the relational model

Tables, rows, relationships — the model everything else rests on.

Your win: explain what a relational database is, and why we reach for an RDBMS like Postgres instead of a plain key-value store — grounded in the emails table your services already read and write.

Data lives in tables

A relational database organises data into tables (formally, relations). A table is a set of rows, and every row has the same fixed set of typed columns.¹ That's it — but the discipline of "every row has the same shape, and the database enforces it" is what makes it powerful.

Tables relate to each other

The "relational" part is that tables reference one another. Each email has many recipients, stored in a separate email_recipients table that points back via a foreign key. The database refuses to store a recipient for an email that doesn't exist — referential integrity, enforced for you.

Your code, exactly

The emails table is a real relation defined in a migration; spike reads and writes it every time someone sends an email:

CREATE TABLE emails (
    email_id      text,              -- the primary key column
    subject       text,
    content       jsonb,
    resource_path text NOT NULL,     -- the tenant (Part 6)
    CONSTRAINT pk__emails PRIMARY KEY (email_id)
);

SQL is declarative — remember this

When you write SELECT … WHERE …, you don't tell Postgres how to find the rows (scan? use an index? which join order?). You describe the result; the query planner figures out the plan. That separation is the whole reason indexes and query tuning (Parts 3–4) are a thing — you influence the how indirectly.

Check yourself (from memory)

1. PostgreSQL — The relational data model.