Databases,
learned properly.

Why databases exist.

Two problems, repeated forever. Durability: when the power fails, the bytes you said you saved are still there. Concurrency: when ten thousand clients hit the same data at once, none of them sees a contradiction. Files can't do this on their own, and neither can the operating system. The database is the layer that buys both, with a surprisingly small set of primitives: pages, a write-ahead log, locks (or MVCC), and an index.

The relational model, Codd 1970, added a third gift: a logical schema independent of the physical layout. SQL queries describe what you want; the planner decides how to fetch it. Fifty years of competing models, hierarchical, network, object, document, key-value, graph, have all been absorbed back into relational engines (JSONB, GIN indexes, foreign tables, materialised views) without dethroning the core. Postgres in 2026 is the most capable database humans have ever built, and it ships under the same license it had in 1996.

When not to reach for "something webscale". Modern hardware: 1 TB RAM, 24 TB NVMe, 128 cores. A single Postgres instance handles tens of thousands of writes per second and terabytes of working set. The operational tax of distributed SQL (Spanner, CockroachDB, Vitess) is real. Use one node until it actually hurts, then add a read replica, then partition.

The twelve mental models you must build.

Twelve concepts cover ~95% of database surface. Get these in your bones in the first month. Every engine you meet (Postgres, MySQL, Cassandra, RocksDB, Spanner, ClickHouse) is a recombination of them.

Day-zero — your first hour.

One hour. Open Designing Data-Intensive Applications and read chapters 3 through 7. Then run pgbench and read the EXPLAIN ANALYZE output of one of your own queries. The bar is muscle: you have read about storage and you have measured a real database under load.

# 1. Read DDIA chapters 3–7 (≈ 90 minutes)
#    — Storage and retrieval (B-trees, LSM-trees)
#    — Encoding and evolution
#    — Replication
#    — Partitioning
#    — Transactions

# 2. Spin up a local Postgres
docker run --name pg -e POSTGRES_PASSWORD=pw -p 5432:5432 -d postgres:16
docker exec -it pg psql -U postgres

# 3. Run pgbench — synthetic TPC-B-like workload
docker exec -it pg pgbench -i -s 10 postgres
docker exec -it pg pgbench -c 8 -T 30 postgres

# 4. EXPLAIN ANALYZE one query you actually run
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE user_id = 42;
#    — Read every line. Note Seq Scan vs Index Scan vs Bitmap Heap Scan.
#    — Note "actual time" vs "rows" — large mis-estimates mean stale stats.

# 5. (Optional, satisfying) read pg_stat_statements for the top-10 queries
CREATE EXTENSION pg_stat_statements;
SELECT query, calls, mean_exec_time
  FROM pg_stat_statements
  ORDER BY mean_exec_time DESC LIMIT 10;

Done. You have read the right five chapters, run a real benchmark, and looked at a real query plan. Everything below extends from this beachhead.

Week-1 to Month-3 — pick a track.

After the first hour you can read database writing without bouncing off it. Spend the next three months one track at a time, depth-first. Don't try to learn storage internals and distributed SQL in the same fortnight. Pick the track that maps to your job and finish it.

The books that matter.

Honourable mentions: PostgreSQL Internals (Egor Rogov, free online); SQL Performance Explained (Markus Winand — the print edition of Use-the-Index-Luke); Streaming Systems (Akidau et al — the data-pipeline complement to DDIA).

Courses, lectures, and where to actually learn.

• Carnegie Mellon · 15-445
  Database Systems
  Andy Pavlo's flagship course. Free lectures, slides, and the BusTub project — implement a buffer pool, B+tree, query executor, and concurrency control in C++. The single best self-study path in databases.
• Carnegie Mellon · 15-721
  Advanced Database Systems
  Pavlo's grad sequel. Storage, query optimisation, vectorised execution, modern OLAP. Each lecture pairs with a paper; together they map the modern frontier.
• Stanford · CS245
  Database System Principles
  The Stanford counterpart. Strong on transaction theory and recovery. Good complement to CMU's project-heavy approach.
• UC Berkeley · CS186
  Introduction to Database Systems
  The undergraduate Berkeley course. Free lectures and projects — RookieDB is the implementation track. Approachable on-ramp before tackling 15-445.
• Markus Winand
  Use the Index, Luke!
  A free book on SQL indexes — how the planner uses them, how to read EXPLAIN, why your query is slow. The single highest-use afternoon for working app developers.

• Bradfield CS
  Databases
  Cohort-based, paper-driven. Pricey; the curated reading list and small-group discussion are the value. Bradfield's distributed-systems and OS courses pair well with this.

The paper canon.

Fourteen papers, roughly 1970 → 2017. Read them in order. The later ones cite the earlier ones constantly. Most are 8–20 pages.

1. 01
   1970 · Codd

   A Relational Model of Data for Large Shared Data Banks

   The founding paper. Relations, tuples, normal forms; the case for separating logical model from physical layout. Twelve pages that started the field.

2. 02
   1976 · Astrahan et al

   System R: A Relational Approach to Database Management

   IBM's prototype — the first relational DBMS. SQL, the cost-based optimiser, the storage manager. Almost every modern relational engine traces here.

3. 03
   1976 · Eswaran, Gray, Lorie, Traiger

   The Notions of Consistency and Predicate Locks in a Database System

   The original definition of degrees of consistency and predicate locking. Read it before reading anything else about isolation.

4. 04
   1976 · Gray et al

   Granularity of Locks and Degrees of Consistency in a Shared Database

   Multi-granularity locking and the four classic isolation degrees. The vocabulary every modern transaction manager still uses.

5. 05
   1992 · Mohan et al

   ARIES: A Transaction Recovery Method

   How WAL recovery actually works. Analysis, redo, undo. The thing every database does on crash recovery; the algorithm by which the WAL keeps its promise.

6. 06
   1995 · Berenson et al

   A Critique of ANSI SQL Isolation Levels

   The famous critique. ANSI's phenomena are ambiguous; snapshot isolation is missing; serialisable is the only honest level. Read this and you stop trusting "Repeatable Read".

7. 07
   1996 · O'Neil et al

   The Log-Structured Merge-Tree (LSM-Tree)

   The LSM paper. Buffer in memory, flush sorted runs, merge in the background. The architectural ancestor of LevelDB, RocksDB, Cassandra, ScyllaDB.

8. 08
   2000 · Adya

   Generalized Isolation Level Definitions (PhD thesis chapter)

   Adya's redefinition of isolation in terms of forbidden histories — implementation-independent, mathematically clean. The right vocabulary for arguing about isolation.

9. 09
   2005 · Stonebraker et al

   C-Store: A Column-oriented DBMS

   The columnar paper. Read columns, not rows; compress aggressively; vectorise execution. The architectural seed of Vertica, MonetDB, ClickHouse, every modern OLAP engine.

10. 10
    2006 · Chang et al

    Bigtable: A Distributed Storage System for Structured Data

    Wide-column store on GFS + Chubby. Tablets, SSTables, the LSM-style write path at planet scale. The grandparent of HBase, Cassandra, DynamoDB.

11. 11
    2007 · DeCandia et al

    Dynamo: Amazon's Highly Available Key-value Store

    Consistent hashing, vector clocks, sloppy quorums, hinted handoff. The paper every NoSQL system reaches back to; Cassandra, Riak, and DynamoDB are its descendants.

12. 12
    2012 · Thomson & Abadi

    Calvin: Fast Distributed Transactions for Partitioned Database Systems

    Deterministic transaction ordering as a primitive. Decide the order first, then run; the contrarian alternative to 2PC + consensus. FaunaDB descends directly from this.

13. 13
    2012 · Corbett et al

    Spanner: Google's Globally-Distributed Database

    Externally consistent transactions over a planet-scale database via TrueTime — bounded clock uncertainty backed by GPS + atomic clocks. The paper distributed-SQL is judged against.

14. 14
    2017 · Verbitski et al

    Aurora: Design Considerations for High Throughput Cloud-Native Relational Databases

    Push the redo log into the storage layer; let storage do the page reconstruction. The cloud-native rewrite of MySQL/Postgres — six-way replicated, quorum-write logs.

Going further: FoundationDB: A Distributed Unbundled Transactional Key Value Store (Zhou et al, 2021); The Design and Implementation of MonetDB; the Snowflake SIGMOD 2016 paper (cloud OLAP, separation of storage and compute); the ClickHouse design paper. Pair every paper with at least one open-source engine that implements it.

Talks worth your evening.

• Andy Pavlo · CMU
  15-445 lecture series
  Pavlo's lectures on YouTube. Funny, sharp, paper-heavy. Watch in order with the slides open. The single highest-quality video resource on database internals.
• Bruce Momjian
  How Postgres Stores Data on Disk
  Bruce Momjian — Postgres core team since 1996 — walks through the on-disk format, the visibility map, MVCC, vacuum, and crash recovery. Slide decks freely available; videos on YouTube.
• Kyle Kingsbury · QCon / Strange Loop
  Jepsen analyses
  Aphyr's QCon and Strange Loop talks. Funny, technical, devastating. Watch one to internalise why your "serializable" database probably loses data under partition.
• Frank McSherry
  Differential Dataflow / Materialize
  The principled streaming alternative — incrementally maintained relational queries with strong consistency. McSherry's talks reframe what a "view" can be.
• Pat Helland
  Standing on Distributed Shoulders of Giants
  Helland is the most underrated voice in databases. Standing on Distributed Shoulders, Life Beyond Distributed Transactions, Immutability Changes Everything — watch all three.
• Martin Kleppmann · Strange Loop 2015
  Turning the database inside-out
  Kleppmann's argument that the log is the primary, the database the derived view. The talk that re-framed how a generation thought about CDC, event sourcing, and stream processing.

Hands-on environments — where to actually run things.

Theory without something you can run is fragile. Each of these is a manageable way to make a database push back when you make a mistake.

Isolation levels — cheat sheet.

Five levels, five anomalies. Print this and tape it next to the monitor. The famous footnote is that Snapshot Isolation prevents phantoms but allows write skew. That's why "real" Serializable matters for some workloads, and why Postgres' Serializable Snapshot Isolation (SSI) is the pragmatic compromise: snapshot semantics, with dependency tracking to detect (and abort) the dangerous histories.

Defaults: Postgres → Read Committed; MySQL/InnoDB → Repeatable Read (with phantom-prevention via gap locks); Oracle → Read Committed; SQL Server → Read Committed; Spanner / CockroachDB → Serializable. Most apps run at Read Committed and assume Serializable.

Common mistakes that ship to production.

Patterns every team writes at least once. Read these now so you recognise the shape later, when something on-call is misbehaving and the dashboard is unhelpful.

No indexes on join columns / FKs

Every join over a non-indexed column is a sequential scan. The DB will not warn you. EXPLAIN ANALYZE will show "Hash Join" with a child Seq Scan; that is your sign.

N+1 queries

Load 100 users, then issue 100 separate queries for their profiles. ORMs make this invisible by default. Eager-load joins or batch the second query — log per-request query counts and alert on the tail.

VARCHAR(MAX) / TEXT when you wanted a constraint

Storing email as TEXT means the DB will accept a paragraph. A length cap, a CHECK constraint, or a domain type enforces the contract at the layer that survives bugs in every other layer.

Wrong isolation level by accident

Postgres, MySQL, Oracle all default to Read Committed. Devs often write code that assumes serialisable. Half the "weird race condition in production" tickets are anomalies the dev didn't know could happen at RC.

Long transactions blocking VACUUM

Postgres can't reclaim row versions older than the oldest open transaction. A connection leaked open for a day → table bloats indefinitely → query plans degrade. Monitor pg_stat_activity for long idle-in-transaction sessions.

Connection pool exhaustion

App pool size × replicas easily exceeds DB max_connections. The DB doesn't scale with connections — each one is RAM and a backend process. Use PgBouncer; size the pool to (cores × 2) plus a little headroom.

Leading-wildcard LIKE queries

"WHERE name LIKE %smith%" cannot use a B-tree index; it is a sequential scan. Reach for a trigram index (pg_trgm) or full-text search; redesign the query before scaling the hardware.

Storing JSON when you should store columns

JSONB is for shape that varies row to row. If every row has the same keys, columns are faster, smaller, and check-constrainable. The inverse mistake — modelling a tag-list as 50 nullable columns — is also catastrophic.

ORM lazy-loading triggering unexpected queries

Iterating over a relation collection issues a query per element. The fix is per-ORM (eager-loading, prefetch, includes), but the symptom is universal: a request that should be one query becomes a thousand.

No WAL archive / PITR on production

Streaming replication is not a backup — it replicates DROP TABLE just as fast. Configure WAL archiving + base backups; rehearse point-in-time recovery on a real schedule. The first time you restore should not be the day you need to.

Practice deck.

Ten cards: the questions interviewers ask, the things that bite operators in production, and the trivia that separates "I use a database" from "I understand one".

Reading progressions

Three ordered paths through this material — pick the one that matches where you are.

Keep going.

Databases reward reading and re-reading. The same ARIES paper, read on day 30 and again on day 300, gives you different things. So will DDIA. So will every Pavlo lecture. The field is not large. It is dense, and it has been compounding for fifty years.

Pick one real engine and read its source for an afternoon. Postgres, SQLite, RocksDB, DuckDB, CockroachDB are all open. Pair what you read with the paper that inspired it. Then come back to your own queries, your own schemas, your own indexes. You will rewrite some of them.