Relational Databases (MySQL, Postgres)

The default storage layer for structured data — and still the right choice for a huge fraction of what an ML system needs to store, even in an AI-first stack.

The Relational Model

Data lives in tables (rows and columns), with relationships between tables expressed via foreign keys rather than nesting data inside itself. Normalization organizes tables to minimize redundancy — e.g. storing a user's address once in a users table rather than repeating it on every one of their orders — trading some query complexity (more joins) for data consistency (update it in one place).

SQL Fundamentals

Joins: combine rows from multiple tables based on a related column — the mechanism that makes normalization practical to query.
Aggregations: GROUP BY with COUNT, SUM, AVG, etc. — computing summary statistics, exactly the kind of query you'd run to build training features from raw event logs.
Window functions: compute values across a set of rows related to the current row (running totals, rankings, moving averages) without collapsing them into a single aggregated row — extremely useful for feature engineering over time-series-like data (e.g. "this user's average order value over their last 5 orders").

customers

id	name
1	Ada
2	Grace
3	Alan

orders

order_id	customer_id	item
101	2	Widget
102	3	Gadget
103	4	Gizmo

SELECT c.name, o.item
FROM customers c
INNER JOIN orders o ON o.customer_id = c.id;

Result

c.id	c.name	o.order_id	o.item
2	Grace	101	Widget
3	Alan	102	Gadget

Only rows with a match on both sides survive -- customer Ada (no orders) and order 103 (unknown customer) both disappear.

Indexing and Query Planning

An index is an auxiliary data structure (typically a B-tree — see Algorithms & Data Structures) that lets the database find rows matching a condition without scanning the entire table, turning an $O(n)$ scan into roughly $O(\log n)$ lookup. The tradeoff: indexes speed up reads but slow down writes (every insert/update must also update the index) and consume extra storage — so indexing is a deliberate design decision based on actual query patterns, not something to apply everywhere by default.

The query planner decides how to execute a given SQL query (which indexes to use, what order to join tables in) — understanding how to read a query plan (EXPLAIN in both MySQL and Postgres) is the core skill for diagnosing a slow query.

table size = 10^4.0 ≈ 10,000 rows4

⬤ full scan: O(n) = 10,000 rows checked⬤ B-tree index: O(log n) = 14 rows checked

Real op counts at n = 10,000 rows: full scan checks all 10,000 rows; a B-tree index checks ~log2(n) = 14 rows on its path down the tree -- a 714x fewer comparisons. The gap only widens as the table grows, which is exactly why indexes matter more on large tables and barely matter on tiny ones.

Transactions and ACID

Atomicity: a transaction either fully completes or fully rolls back — no partial writes.
Consistency: a transaction takes the database from one valid state to another, respecting all constraints.
Isolation: concurrent transactions don't see each other's uncommitted changes.
Durability: once committed, a transaction survives a crash.

These guarantees matter whenever correctness under concurrent access is non-negotiable — financial transactions, inventory counts, anything where a race condition would cause real damage.

When Relational Is (and Isn't) the Right Tool

Relational databases excel at structured data with well-defined relationships and a need for strong consistency — user accounts, transactional records, ML experiment/model metadata. They're a poor fit for storing and searching high-dimensional embeddings efficiently at scale (see Vector Databases) or for deeply connected, multi-hop relationship queries (see Graph Databases) — which is exactly why modern AI systems typically use relational databases alongside vector and graph stores, not instead of them.

Relational: B-tree index

Vector: HNSW / IVF

Graph: native adjacency

Structure: A multi-layer graph or clustered buckets over the embedding space

What “nearby” means here: "Near" = small distance (cosine/Euclidean) in high-dimensional space

Same goal (avoid scanning everything) -- three different structures, because “close” means something different in each engine.

Without this index: Compare the query against every stored vector.

Next: PostgreSQL — a deep dive into the system you'll actually run: schemas and types, writing queries end to end, indexing, transactions, and Postgres-specific features like JSONB and pgvector.