Keys: identity and referential integrity
A primary key uniquely identifies a row — NOT NULL, unique, one per table. A foreign key declares that values in child columns must exist in the parent's key — the engine rejects orphans at write time (cheaper than detecting them in a nightly batch job).
| Key type | When to use |
|---|---|
| Natural key | Stable business identifier (country_code) |
| Surrogate key | Opaque BIGSERIAL / UUID — survives business rule changes |
| Composite key | Junction tables, partition-aligned keys |
Surrogate UUIDs simplify distributed inserts (no central ID generator) but widen indexes and hurt locality vs sequential IDs.
Constraints beyond PK/FK
- UNIQUE — alternate keys (
email), allows one NULL on most engines - CHECK — row-level predicates (
price >= 0) - NOT NULL — presence guarantee
Constraints are storage contracts: they turn application invariants into engine-enforced rules. Trade-off: extra index maintenance on UNIQUE, FK lock behavior on parent deletes.
Index design from a storage lens
Indexes are separate on-disk structures (usually B-trees) mapping key → row locator (heap TID or primary key).
| Index type | Best for | Storage note |
|---|---|---|
| B-tree (default) | Equality, range, sort | Balanced tree, page splits on insert |
| Hash | Equality only | No range scans |
| GIN/GiST (Postgres) | Full-text, JSON paths, geo | Larger, slower writes |
Composite indexes and the leftmost prefix
Index on (user_id, created_at) supports WHERE user_id = ? and WHERE user_id = ? ORDER BY created_at — not WHERE created_at = ? alone.
Covering indexes
If all selected columns live in the index (INCLUDE columns in Postgres), the engine index-only scans — avoids heap fetches. Huge win for read-heavy dashboards.
Clustered vs secondary
Clustered (InnoDB PK, SQL Server clustered index) — table rows stored in PK order. Secondary indexes point to PK. Secondary-only (Postgres) — heap is unordered; indexes point to physical TID.
Selectivity and write cost
Every index speeds specific reads and taxes every write (index leaf updates, WAL). Low-selectivity indexes (gender) rarely help. High-cardinality lookup columns (user_id, email) almost always do.
Senior-level signal
"We'll index everything" → doubled write latency and autovacuum pressure. Profile with EXPLAIN (ANALYZE, BUFFERS) (SQL roadmap) but design indexes from access patterns first.
Where this goes next
B-Tree vs LSM Storage Engines explains the on-disk structure your B-tree indexes actually are.
Further Reading
Hands-On Tasks (Optional)
Low-setup exercises — schema drills, paper walkthroughs, or optional local installs. No autograding; the goal is interview fluency on how data is stored.
- Sketch indexes for a lookup pattern15m
Table `events(user_id, created_at, type)` with queries filtering `WHERE user_id = ? ORDER BY created_at DESC LIMIT 20`. Propose a composite index column order and explain whether it can be covering. No database required.