# Chapter 1: Foundations & Data Modeling
## 1.0 - Relations & Normalization (The Cafe Layout)
<img src="assets/elephant_cafe_architecture_layout.png" width="250" style="float: left; margin: 0 20px 20px 0;" />
Postgres manages a structured collection of **Facts** about its world.
In the eyes of the engine, every record is a fact:
- "Babu is a Capybara" is a fact.
- "Saffron costs $400 per kg" is a fact.
- "Supplier #5 delivered 50kg of Apples" is a fact.
PostgreSQL gathers facts of the same shape into a **table**. Classical relational theory calls this a relation and models it as a mathematical set of tuples. A practical SQL table can still contain duplicate rows unless a constraint stops them—a small but important gap we revisit in the academic detour.
### The Discipline of Normalization
If we were setting up the Cafe in a simple spreadsheet, we might be tempted to record every detail about an animal in a single, massive row. This is the **Naive Model**.
| id | name | species | diet |
| :-- | :--- | :------- | :-------- |
| 101 | Babu | Capybara | Herbivore |
| 102 | Pip | Capybara | Herbivore |
| 103 | ... | ... | ... |
Storing descriptive strings like "Herbivore" redundantly in every record creates a **data integrity risk**. If you rename the category, you must update every copy; missing even one creates an **Update Anomaly** where the database disagrees with itself.
We avoid this through **normalization**: giving one authoritative fact one home instead of copying it into every dependent row. `animals.species_id` stores a small key, and a **foreign-key constraint** checks that key against a referenced unique key in `species`. It is an ID with a rule, not a physical pointer to another row.
#### The Anatomy of the Link
Observe how a simple integer placeholder spares the engine from redundancy:
**The `species` Ledger (Fact Blueprint)**
| id | name | diet_type |
| :---- | :------- | :---------- |
| **1** | Capybara | Herbivore |
| **2** | Aardvark | Insectivore |
**The `animals` Ledger (Set of Facts)**
| id | name | species_id |
| :-- | :----- | :--------- |
| 101 | Babu | 1 |
| 102 | Pip | 1 |
| 103 | Arthur | 2 |
When Babu walks in, PostgreSQL joins **`species_id`** (1) to `species.id` and reconstructs his profile. The species description has one authoritative home; each animal carries only the matching key. A foreign key keeps non-null child values honest, although it cannot invent business rules we never declared. The Cafe pays for an extra join and escapes a warehouse full of contradictory copies.
> [!TIP]
> The mathematical machinery behind this model—why a relation is a **set of typed tuples**, and why the planner can rearrange SQL so freely—is the subject of **[[Manuscript/01 - Foundations & Data Modeling/1.2 - Relational Model (An academic detour)|1.2 An academic detour]]**.
---
#### The Blueprint of the Cafe
Before a single order can be processed, Postgres must define the schema of its world using **DDL (Data Definition Language)**. These blueprints — `CREATE TABLE` statements — are the strict contracts that every piece of data must conform to.
> [!NOTE]
> You can find the full, literal blueprints for the Elephant Cafe in the **[[scripts/init.sql|Architectural Inventory]]**.
```mermaid
erDiagram
species ||--o{ animals : "classifies"
suppliers ||--o{ supply_deliveries : "delivers"
ingredients ||--o{ supply_deliveries : "received_in"
ingredients ||--|| flavors : "profiles"
animals ||--o{ animal_favorites : "prefers"
ingredients ||--o{ animal_favorites : "preferred_by"
dishes ||--o{ dish_ingredients : "contains"
ingredients ||--o{ dish_ingredients : "used_in"
animals ||--o{ orders : "places"
orders ||--o{ order_items : "includes"
dishes ||--o{ order_items : "ordered_as"
```
---
### The Three Tiers of Truth (Normal Forms)
A schema does not become trustworthy all at once. It gets there by asking three increasingly awkward questions:
1. Is this one value—or several facts hiding in a trench coat?
2. Does this fact depend on the whole key?
3. Does this fact belong in this table at all?
Those are the first three **Normal Forms**. Each one removes a different way for the database to disagree with itself.
| Normal Form | Rule | Example Violation |
| :--- | :--- | :--- |
| **1NF: One value per attribute** | Each attribute holds one value from its declared domain | Hiding several diets inside one text string |
| **2NF: The whole key owns the fact** | A non-key attribute depends on the whole candidate key, not merely part of it | Storing `order_date` in rows keyed by `(order_id, dish_id)` |
The first two keep facts legible and anchored. The third keeps them from wandering into tables where they do not belong.
#### 3NF: The Source of Truth
Babu and Pip are both Capybaras. If each animal row carries `diet_type = 'Herbivore'`, the Cafe has written one species fact twice. Nothing looks wrong—until that fact changes. Then every copy must change with it, and missing one leaves the database telling two stories at once.
**Third Normal Form (3NF)** removes that **transitive chain**. Put `diet_type` in `species`; let each animal store only its `species_id`. Babu's diet now **follows from** his species membership. The schema itself knows where that truth belongs.
Normalization is not academic purity. It is refusing to make the database remember the same truth twice.
> [!NOTE] One value can still be structured
> PostgreSQL arrays are valid values of array types, and they become useful later in the book. The 1NF question is not whether brackets appear; it is whether the attribute represents one value from its declared domain and whether the application can enforce the dependencies it cares about.
---
### 🧪 Manipulation Lab: The Broken Update (Update Anomalies)
To understand why Postgres enforces the discipline of normalization, you must experience the consequences of ignoring it.
#### The Setup
Connect to your database and create a denormalized, temporary table representing a naive animal registry where species details are stored directly inside each row:
```sql
CREATE TEMP TABLE naive_animals (
id INT,
name TEXT,
species_name TEXT,
diet_type TEXT
);
INSERT INTO naive_animals (id, name, species_name, diet_type) VALUES
(1, 'Babu', 'Capybara', 'Herbivore'),
(2, 'Pip', 'Capybara', 'Herbivore'),
(3, 'Waffles', 'Capybara', 'Herbivore');
```
#### The Task
The Cafe's director decides to update the official taxonomy: the category `Capybara` is to be updated to its scientific name, `Hydrochoerus`.
Write an update statement, but simulate a common developer mistake: target the update using specific IDs rather than the logical category, intentionally missing one row:
```sql
-- Updating the taxonomy, but missing Waffles (ID 3)
UPDATE naive_animals
SET species_name = 'Hydrochoerus'
WHERE id IN (1, 2);
```
#### Duplicated Facts Drift into Contradiction
Query the table to inspect the results:
```sql
SELECT * FROM naive_animals;
```
Notice the state of the registry:
```
id | name | species_name | diet_type
----+---------+--------------+-----------
1 | Babu | Hydrochoerus | Herbivore
2 | Pip | Hydrochoerus | Herbivore
3 | Waffles | Capybara | Herbivore
```
#### One Species Row Restores One Truth
The database now disagrees with itself. You have created an **Update Anomaly**. An external application querying for `'Capybara'` will find Waffles, but miss Babu and Pip. An application querying for `'Hydrochoerus'` will miss Waffles entirely.
Third Normal Form (3NF) removes this particular update anomaly from the model. By splitting the table into a `species` blueprint and relating animals to it with a foreign key, the species name has one authoritative stored value. New snapshots can observe an update to that value as one committed change; older transaction snapshots may continue to see the prior version until their visibility window advances.
> [!NOTE] Where the link metaphor stops
> `species_id` is a value, not an address. PostgreSQL may move or replace the physical tuple version for the parent row without changing the child's stored key. The constraint relates values; it does not point at a disk location.
---
## 1.1 - SQL (The Waiter's Pad)
<img src="assets/arch_sql_waiter_pad.png" width="250" style="float: left; margin: 0 20px 20px 0;" />
When you interact with Postgres, you send it a command like this:
```sql
SELECT * FROM animals WHERE species_id = 1;
```
Notice what is missing from that command. You do not specify which file to open on the disk. You do not tell the engine what byte offset to seek, or how to iterate through memory buffers.
This is because **SQL (Structured Query Language)** is a **declarative** language.
In a declarative language, you describe the *result* you want, rather than the *steps* required to get it. You provide the requirement, and the database engine takes total responsibility for the physical labor.
> [!IMPORTANT]
> **The Declarative Checkpoint**: If you remember one thing about SQL, let it be this: **You describe the 'What,' not the 'How.'**
### The Waiter's Pad
Think of SQL like a **Waiter's Pad**.
You describe what you want to eat. You do not tell the chef which pan to use or how hot the stove should be.
This separation of concerns is the foundation of database performance. Because you only provide a logical request, the engine's **Query Planner** is granted the freedom to find the cheapest physical path. It can choose to scan every page sequentially, or it can use an index to jump directly to the answer.
### The Three Vocabularies (DDL, DQL, DML)
This logical interface is divided into three functional areas:
| Area | Purpose | Key Commands | Architectural Payoff |
| :--- | :--- | :--- | :--- |
| **DDL** (Definition) | Define structure. | `CREATE`, `ALTER`, `DROP` | Allocates physical files and catalog entries. |
| **DQL** (Query) | Read state. | `SELECT` | Optimizer decides between Index or Seq Scans. |
| **DML** (Manipulation) | Modify state. | `INSERT`, `UPDATE`, `DELETE` | Manages tuple versioning and MVCC headers. |
SQL is the **Logical Interface**—a rigorous layer that separates your request from the physical reality of how the engine delivers it.
---
### 🧪 Observation Lab: Planner Freedom (SQL Equivalency)
Because SQL is declarative, the database engine takes complete ownership of how a query is executed. This grants the query optimizer the freedom to rewrite and compile different syntactic requests into the exact same physical operations.
#### The Task
Compare the execution plans of two query variations that seek the same logical outcome: returning the names of all capybaras.
1. **Query A (Join first, then Filter)**:
```sql
EXPLAIN SELECT a.name, s.name
FROM animals a
JOIN species s ON a.species_id = s.id
WHERE s.name = 'Capybara';
```
2. **Query B (Subquery Filter first, then Join)**:
```sql
EXPLAIN SELECT a.name, sub.name
FROM animals a
JOIN (SELECT * FROM species WHERE name = 'Capybara') sub ON a.species_id = sub.id;
```
#### Two SQL Shapes Produce the Same Plan
Examine the outputs. Despite Query B using a subquery that looks like a manual optimization to pre-filter species records, both statements generate the **exact same execution plan**:
In both cases, the planner recognized that filtering `species` first is the cheapest path, pushed the filter down automatically, and joined the records using a nested loop.
#### The Planner Optimizes Intent, Not Nesting
Trying to hand-optimize declarative SQL using complex subqueries or CTEs is often a waste of time. The query planner strips away your structural nesting, parses the logical requirements, and reconstructs the query into a tree of primitive mathematical operations.
---
## 1.2 - Relational Model (An academic detour)
<img src="assets/arch_relational_model_set.png" width="250" style="float: left; margin: 0 20px 20px 0;" />
SQL is not the foundation of Postgres. It is a surface dialect — a human-readable layer resting on top of a much older, more rigorous framework called **Relational Algebra**. Understanding the difference between the two changes how you read query plans.
This algebraic foundation is what allows Postgres to transform from a simple storage engine into a high-performance query optimizer.
The core insight is simple: if you define a database table not as a spreadsheet, but as a **mathematical set of typed tuples**, you gain a clean set of operations that compose predictably. The engine can then rearrange those operations with significant freedom, provided the result is algebraically equivalent. Most of the Query Planner's "magic" is just this rule applied recursively.
---
### What a Relation Actually Is
A **Relation** is a set of tuples sharing a common attribute header. This sounds like a table, but two important constraints fall out of the mathematical definition that distinguish it from a spreadsheet:
**1. No guaranteed ordering.**
A set has no inherent sequence. Postgres does not promise that rows come back in the order they were inserted, or in any order at all, unless you explicitly ask for one with `ORDER BY`. This surprises a surprising number of engineers in production.
```sql
-- This returns rows in no guaranteed order.
SELECT * FROM animals;
-- This returns rows in a guaranteed order.
SELECT * FROM animals ORDER BY name;
```
**2. No duplicate rows (by default, unenforced).**
A true mathematical set prohibits duplicate elements. Postgres does not enforce this constraint automatically — you can insert two identical rows — but a `UNIQUE` or `PRIMARY KEY` constraint brings a table into formal compliance. The decision to not enforce uniqueness by default was deliberate: enforcing it has a cost, and not every table needs it.
---
### The Five Primitive Operations
Most common `SELECT` statements can be decomposed into five fundamental algebraic operations. These five are sufficient to express nearly any query over a relational schema:
| Symbol | Operation | What It Does | SQL Equivalent |
| :----- | :------------- | :--------------------------------------------------- | :--------------------------------------- |
| `σ` | **Selection** | Filter the set — keep only tuples matching predicate | `WHERE diet_type = 'Herbivore'` |
| `π` | **Projection** | Trim the header — keep only specified attributes | `SELECT name, diet_type` |
| `⋈` | **Join** | Combine two relations on a shared attribute | `JOIN species ON animals.species_id = species.id` |
| `∪` | **Union** | Merge two compatible sets, remove duplicates | `UNION` |
| `−` | **Difference** | Return tuples in the first set absent from the second | `EXCEPT` |
Applied to the Elephant Cafe:
- **Selection (`σ`)**: "Only the Herbivores" (`WHERE diet_type = 'Herbivore'`)
- **Projection (`π`)**: "Only the names" (`SELECT name`)
- **Join (`⋈`)**: "Match animals to their species" (`JOIN species ON ...`)
Under the hood, Postgres sees your query as a composition of these operators. Because the algebra is **closed**, the output of one operator is typically a valid input for the next.
---
### Why Composability Matters: The Planner's Freedom
Consider this query, which looks for every order placed by a specific elephant:
```sql
SELECT * FROM orders
JOIN animals ON orders.animal_id = animals.id
WHERE animals.name = 'Babu';
```
If Postgres executed this query precisely as written, the performance could be catastrophic for large datasets: it would join millions of `orders` to thousands of `animals`, and only *then* filter for Babu.
Instead, the planner applies an algebraic identity called **predicate pushdown**. It recognizes that the filter can be applied to the `animals` table *before* the join, reducing the work to a single row lookup.
This is made possible by **Closure**—the defining property of Relational Algebra where every operation takes a relation as input and returns a relation as output. Because every operator speaks the same language, they are freely composable.
> [!NOTE] Declare the Result; Let PostgreSQL Choose the Path
> **Concept**: SQL is declarative. You write *what* you want; Postgres determines *how* to get it.
> **Payoff**: Because relational operations obey the law of closure, the query planner can treat your query as a flexible execution tree rather than a rigid list of step-by-step instructions. It is free to push filters down, change join orders, and swap scan strategies, guaranteeing the exact same result set while executing it thousands of times faster.
---
## 1.3 - Summary: The Shape of Truth
You started this chapter with tables, rows, and SQL.
You leave it with a different model: a database is a disciplined collection of facts, and SQL is the declarative boundary between what you want and how the engine gets it.
The important change is not that you learned normalization terminology. It is that you can now see why duplicated truth becomes operational risk. A bad schema is not merely untidy; it forces the engine and the application to negotiate contradictions.
You can also see why Postgres has room to be clever. Because your query describes a logical result, the planner can rearrange joins, push filters down, and choose a cheaper physical route without changing the answer.
> [!NOTE] State the Truth Once
> **Concept**: State the truth once. Let the engine find the path.
### Sources & Further Reading
- [PostgreSQL 18: Data Definition](https://www.postgresql.org/docs/18/ddl.html)
- [PostgreSQL 18: Constraints](https://www.postgresql.org/docs/18/ddl-constraints.html)
- [PostgreSQL 18: The Path of a Query](https://www.postgresql.org/docs/18/query-path.html)
- Source trail: `src/backend/parser/`, `src/backend/rewrite/`, and `src/backend/optimizer/` in the PostgreSQL source tree.
<div style="page-break-after: always;"></div>