Foundations of Data Systems
- Reliability, Scalability, and Maintainability: The three pillars every data system is judged on. Reliability is working correctly despite faults, scalability is coping with growth, and maintainability is keeping the system easy to operate and change. This foundational lesson defines all three, with the fault-versus-failure distinction, percentiles and tail latency, fan-out, and the human-error reality that catches people in interviews.
- Data Models: Relational vs Document vs Graph: The data model you choose (relational, document, or graph) shapes how you think about a problem and decides what is easy and what is painful. This lesson covers all three, the object-relational mismatch that motivated documents, schema-on-read versus schema-on-write, the normalization-versus-denormalization trade-off, and how modern databases are blurring the lines.
- Query Languages: Declarative vs Imperative: Good query languages are declarative: you describe what you want and let the database decide how to get it. This lesson contrasts declarative with imperative querying, explains why declarative wins (the optimizer, parallelism, evolvability), covers the N+1 query trap, and looks at how document and graph models are queried, with SQL examples throughout.
Storage and Retrieval
- The World's Simplest Database: Logs, Hash Indexes, and the Read/Write Trade-off: Build a working key-value store in two shell functions and watch it break. This lesson uses that toy to reveal the deepest ideas in storage engines: the append-only log behind fast writes, the fundamental trade-off that every index speeds reads but taxes writes, the hash index and its limits, compaction, and why sequential I/O wins. It is the foundation for LSM-trees, B-trees, and columnar storage.
- LSM-Trees: The Engine of High-Throughput Writes: Log-Structured Merge-Trees take the append-only log from the previous lesson and make its segments sorted, which removes the in-memory key limit and unlocks range queries. This lesson walks the memtable-and-SSTable write path in detail, explains fsync and what 'durable' really means, covers the bloom-filter read path with real numbers, digs into size-tiered versus leveled compaction, tombstone deletes, and the write-amplification math behind Cassandra, RocksDB, and LevelDB.
- B-Trees and the Write-Ahead Log: B-trees are the default index in almost every relational database, and unlike LSM-trees they update data in place. This lesson covers how a B-tree organizes data into pages, why high fanout makes lookups so shallow, how page splits keep it balanced, and the crash-safety problem that in-place updates create, solved by the write-ahead log.
- B-Trees vs LSM-Trees: Choosing a Storage Engine: One of the most common storage interview questions, answered properly. We compare B-trees and LSM-trees through the three amplifications (write, read, and space), explain why LSM wins write throughput while B-trees win read latency and predictability, cover the transaction and compaction trade-offs, and give a clear framework for choosing.
- Indexes in Depth: Clustered, Covering, Composite, and Full-Text: How to actually use indexes well. This lesson covers clustered indexes versus heaps (InnoDB versus PostgreSQL), covering indexes and index-only scans, composite indexes and the leftmost-prefix rule, the traps that make a carefully built index sit unused, and full-text and specialized index types, with PostgreSQL, MySQL, and Oracle examples.
- Column-Oriented Storage: Analytical queries scan millions of rows but touch only a few columns, which row storage handles badly. Column-oriented storage flips the layout, keeping each column's values together, so a query reads only the columns it needs. This lesson covers the layout, the heavy compression it unlocks, vectorized execution, why writes are bulk-load only, and the column-family naming trap.
- OLTP vs OLAP: Transactions vs Analytics: A database serves two opposite jobs: running the application (OLTP) and analyzing the data (OLAP). This lesson contrasts the two workloads, explains why you keep them on separate systems, covers the ETL/ELT pipeline that connects them, the warehouse-versus-lake landscape and the star schema, and ties the whole storage module together: row stores for OLTP, column stores for OLAP.
Transactions
- What Is a Transaction? ACID Demystified: A transaction is the database's promise that a group of reads and writes either all happen or none do. This lesson unpacks ACID one letter at a time using PostgreSQL, separates the real guarantees from the marketing, and shows how the topic comes up in real systems and interviews.
- Race Conditions: The Bugs Isolation Prevents: A field guide to the concurrency bugs that isolation levels exist to stop: dirty reads and writes, read skew, lost updates, write skew, and phantoms. Each one is shown with a concrete example through a PostgreSQL lens, so you can recognize them on sight before choosing an isolation level.
- Isolation Levels: From Read Uncommitted to Serializable: The four SQL isolation levels explained from the ground up, Read Uncommitted through Serializable, with exactly what each one prevents and allows. Engine-neutral, with side-by-side PostgreSQL, MySQL, and Oracle code, because the same level name can mean different things in different databases.
- How Isolation Is Implemented: 2PL, MVCC, and SSI: The machinery beneath the isolation levels, walked level by level. For each level we show how the database delivers it with locking (pessimistic) or multi-versioning (MVCC and SSI, optimistic), how to take row locks yourself to fix lost updates and write skew, and how to set every level in PostgreSQL, MySQL, and Oracle.
Replication
- Single-Leader Replication: The foundation of the replication module: one leader accepts every write and streams it to its followers. This lesson covers the leader/follower model, synchronous vs asynchronous replication, the ways changes are shipped (statement, physical WAL, logical, trigger), how to add followers, and the hard problem of failover when the leader dies.
- Replication Lag and Its Consistency Problems: Asynchronous replication lets followers fall behind the leader, and that lag causes three classic bugs when you read from a replica: not seeing your own writes, watching data travel backward in time, and seeing effects before their causes. This lesson explains each, the consistency guarantees that fix them, and how to measure lag across PostgreSQL, MySQL, and Oracle.
- Multi-Leader Replication: What happens when more than one node can accept writes. Multi-leader replication unlocks multi-region writes, offline clients, and collaboration, but it introduces the hardest problem in replication: write conflicts. This lesson covers when to use it, how conflicts arise, and the strategies (avoidance, last-write-wins, merge, CRDTs) for handling them.
- Leaderless Replication (Dynamo-Style): The third replication model: no leader at all. In Dynamo-style leaderless replication the client writes to and reads from many replicas at once, and correctness comes from quorums (W + R > N), read repair, and anti-entropy rather than a leader. This lesson covers quorums, keeping replicas in sync, sloppy quorums and hinted handoff, and how concurrent writes are resolved.
Partitioning
- Partitioning: Key Range vs Hash: When one machine can no longer hold or serve all your data, you partition (shard) it across many nodes. This lesson starts from the problem (a single server running out of disk, memory, and throughput), defines what a node is, then explains the central choice: assign keys by range, which keeps data sorted but invites hot spots, or by hash, which spreads load evenly but loses range scans. Covers the compound-key compromise and the celebrity hot-key problem, with PostgreSQL, MySQL, and Cassandra examples.
- Secondary Indexes When Partitioned: Partitioning by primary key is easy; secondary indexes are where it gets hard. Once data is sharded, an index on a non-key attribute can be organized two ways: local (document-partitioned), which makes writes cheap but reads scatter across every partition, or global (term-partitioned), which makes reads cheap but writes fan out and go stale. This lesson covers both, the trade-off, and the query-table workaround.
- Rebalancing and Request Routing: Clusters are not static: data grows, nodes are added, nodes fail. Rebalancing moves partitions to keep load even, and request routing finds the node that now holds a key. This lesson covers why hash mod N is wrong, the fixed-count, dynamic, and consistent-hashing rebalancing strategies, the danger of fully automatic rebalancing, and how clients route requests via a coordination service or gossip.
Distributed Data
- The Trouble with Distributed Systems: Why distributed systems break the assumptions you bring from single-machine programming. Networks lose and delay messages so you cannot tell a slow node from a dead one, clocks drift so timestamps lie, and processes pause so a lock holder can act after its lease expires. This lesson is the catalog of partial failures, plus fencing tokens, split brain, and the crash-vs-Byzantine fault models.
- Consistency Models and the CAP Theorem: The spectrum of consistency guarantees, from linearizability (the single-copy illusion) to eventual consistency, and the famous trade-off between them. This lesson defines linearizability, separates it from serializability, states the CAP theorem correctly (you cannot give up partition tolerance), and introduces PACELC, the everyday latency-versus-consistency trade-off that CAP ignores.
- Consensus and Distributed Transactions: The capstone: getting unreliable nodes to agree on one value. This lesson covers two-phase commit for atomic commit across nodes and its fatal coordinator-blocking flaw, then consensus (Raft, Paxos) which survives failures as long as a majority is alive, the term-number fencing token, total order broadcast and its link to linearizability, and why you should never roll your own consensus.
Course · Concept track·6 chapters · 24 lessons · no labs
Databases,
from first principles.
A concept-first tour of how databases actually work: storage, indexing, transactions, replication and the distributed-systems ideas behind them. Built around annotated diagrams, so the next time someone says MVCC or two-phase commit, you can picture exactly what is happening.
Level
Beginner
Study time
10h
Chapters
6
Lessons
24
Package includes: System Design Interview Course + Database Fundamentals Course
replication.diagramSample · Replication
Healthy · lag stableasync · 3 nodes
How it's taughtNo labs. No clusters.
No labs. No clusters.
Just the mental models that stick.
01
Concept-first
Each chapter opens with the problem the technique exists to solve, before any jargon lands on the page.
02
Annotated diagrams
Hand-built diagrams where every arrow and state transition is labelled, so you read the picture, not a wall of prose.
03
Interview-focused
Real-world case studies (Quora, TikTok, Airbnb, Snapchat) show how these ideas play out under production load.
What you'll learnThe ideas you'll
The ideas you'll
stop being fuzzy about.
The storage, transaction and distribution topics that come up in nearly every system-design interview and most production architecture conversations.
Master database design fundamentals, from understanding data storage to implementing efficient indexing strategies.
Learn transaction management, ACID properties, and how to ensure data consistency in database systems.
Understand concurrency control mechanisms including locking, timestamp-based, and MVCC approaches.
Explore distributed database systems, data partitioning, and sharding techniques for horizontal scaling.
Master data replication strategies and consistency models including the CAP theorem and its implications.
Learn consensus algorithms and leader election strategies for distributed database systems.
Understand fault tolerance, recovery mechanisms, and high availability in distributed databases.
Explore NoSQL databases including key-value stores, document databases, column-family stores, and graph databases.
Learn about advanced topics like in-memory databases, NewSQL, time-series databases, and big data frameworks.
Apply your knowledge through real-world case studies including Quora, TikTok, Airbnb, and Snapchat database designs.
CurriculumThe full chapter list.
The full chapter list.
Click any chapter to expand.
6 chapters·24 lessons
- 1Reliability, Scalability, and Maintainability
- 2Data Models: Relational vs Document vs Graph
- 3Query Languages: Declarative vs Imperative
UnlockEvery chapter and diagram,
Every chapter and diagram,
ready for your next interview.
Database Fundamentals comes with the System Design package, so you get both tracks together. Bundle the full catalogue if you want everything.