Vol 5: Databases and Data Systems

This volume starts with the relational model and continues through indexes, execution, transactions, replication, messaging, and search. Consistency and recovery contracts come before throughput; otherwise the benchmark may look healthy while the data quietly resigns.

Prerequisites

• Bring basic programming, file I/O, and data-structure knowledge. Storage and execution lessons also use caching, complexity, and concurrency concepts.
• Start with a separate SQLite 3.37.0+ practice database. For PostgreSQL, MySQL, RocksDB, and other products, follow the environment notes in the relevant lesson.

Coverage

Chapters 1–3 establish querying and modeling; 4–10 cover storage and execution; 11–16 address transactions and distributed constraints; 17–20 examine messaging, architecture, caching, and search.

Chapter 1

• 1.1 Relational Model, Constraints, and Secure Writes
• 1.2 SQL Query Semantics, Joins, Aggregations, and Execution Plans

Chapter 2

• 2.1 Relational Algebra: Combining Queries with Relations
• 2.2 Query Equivalence, Logical Rewriting, and SQL Semantic Boundaries

Chapter 3

• 3.1 From Business Rules to ER Models and Relational Schema
• 3.2 Functional Dependencies, Normal Forms, and Verifiable Decomposition

Chapter 4

• 4.1 Paged Storage, Record Layout, and Buffer Pool
• 4.2 B+ Tree Indexing: Pages, Key Ordering, and Execution Cost
• 4.3 WAL, Checkpoint, and Crash Recovery

Chapter 5

• 5.1 LSM-Tree Write, SSTables, and Read Merge
• 5.2 Compaction, Amplification Effects, and LSM Operations Maintenance

Chapter 6

• 6.1 Columnar Layout, Parquet, and Encoding
• 6.2 Columnar Scanning: Pruning, Vectorization, and Deferred Materialization

Chapter 7

• 7.1 Hash Indexes: Equality Lookups, Collisions, and Dynamic Resizing
• 7.2 External Sorting, Multi-Way Merge, and Merge Join

Chapter 8

• 8.1 Query Executor: Scan, Join, Aggregate, and Resource Boundaries
• 8.2 Cardinality Estimation, Cost Modeling, and Execution Plan Diagnosis

Chapter 9

• 9.1 From Row-at-a-Time Interpretation to Expression JIT
• 9.2 Pipeline, Push Execution, and Query Code Generation

Chapter 10

• 10.1 Vectorized Execution, Selection Vectors, and Data Representation
• 10.2 SIMD Kernel, Runtime Dispatch, and Numerical Semantics

Chapter 11

• 11.1 Transaction Boundaries, ACID, and Failure Semantics
• 11.2 Concurrency Anomalies, Schedules, and Serializability

Chapter 12

• 12.1 PostgreSQL MVCC, Snapshots, and Isolation Levels
• 12.2 InnoDB Read View, Undo, and Locking Reads

Chapter 13

• 13.1 Two-Phase Locking, Lock Granularity, and Deadlock Diagnosis
• 13.2 OCC, Timestamp Ordering, and Application Concurrency Strategies

Chapter 14

• 14.1 Memory-Optimized Databases: Execution, Layout, and Recovery
• 14.2 Redis Command Model, Transactions, and Persistence

Chapter 15

• 15.1 Choosing a NoSQL Data Model Based on Access Patterns
• 15.2 CAP, Read-Write Consistency, and Degradation Boundaries

Chapter 16

• 16.1 Sharding, Replica Groups, and Consensus
• 16.2 Cross-Shard Transactions, Clocks, and Geographical Distribution

Chapter 17

• 17.1 Understanding Kafka, Pulsar, and RabbitMQ Through Message Lifecycle
• 17.2 Acknowledgment Boundaries, Idempotent Consumption, and Backlog Recovery

Chapter 18

• 18.1 Using Workloads and Invariants to Select Data Systems
• 18.2 Data Architecture, Derived Views, and Safe Evolution

Chapter 19

• 19.1 Caching Protocols, Consistency Windows, and Invalidation
• 19.2 Cache Stampedes, Missing-Key Traffic, and Outage Recovery

Chapter 20

• 20.1 Inverted Indexes, Analyzer, and Relevance
• 20.2 Near-real-time Indexing, Synchronization, Sharding, and Search Operations

Outcomes

• Write queries that preserve join cardinality and NULL semantics, and check keys, foreign keys, and transaction boundaries.
• Use execution plans and amplification measurements to locate storage or query bottlenecks and explain optimization costs.
• Define acknowledgment, expiry, retry, and recovery conditions for replication, messaging, caching, and search.

How to use this map

For a first pass, complete the querying, modeling, and storage sequence in Chapters 1–5, then study transactions. For a slow query, start at 8.2 and follow the relevant index or execution lesson. For duplicate consumption or stale cache values, use 17.2 and 19.1 to inspect acknowledgment and invalidation. Each route should leave a query result, plan, or failure record you can check.