Database Basics

[!NOTE] This module explores the core principles of Database Basics, deriving solutions from first principles and hardware constraints to build world-class, production-ready expertise.

Data is the lifeblood of any system. In this module, we move beyond simple “CRUD” operations and dive into the internal mechanics of how databases actually work, scale, and survive crashes. We will answer the critical questions: “Why is NoSQL faster?”, “How do transactions work?”, and “What happens when the power plug is pulled?”.

Chapter Roadmap

1. SQL vs NoSQL: The Fundamental Choice

  • The Choice: Relational (B-Trees) vs Non-Relational (LSM Trees).
  • Deep Dive: How Storage Engines dictate performance.
  • Key Concept: The “Join Tax” and why we Denormalize in NoSQL.

2. ACID Transactions

  • The Safety Net: How databases guarantee correctness.
  • Deep Dive: Atomicity (Undo Logs), Consistency, Isolation, Durability.
  • Advanced: Distributed Transactions (2PC vs Sagas).

3. BASE & Eventual Consistency

  • The Speed Trade-off: Giving up correctness for massive scale.
  • Mechanics: Quorums (R + W > N), Vector Clocks, and Anti-Entropy.
  • Key Concept: Last Write Wins (LWW) and conflict resolution.

4. Isolation Levels

  • Controlling Chaos: Read Committed, Repeatable Read, Serializable.
  • Anomalies: Dirty Reads, Phantom Reads, and the dangerous Write Skew.
  • Deep Dive: Multi-Version Concurrency Control (MVCC).

5. Write-Ahead Logging (WAL)

  • Durability: How data survives a crash.
  • Mechanics: Log Sequence Numbers (LSN), Checkpoints, and ARIES recovery.

  • The OS Lie: fsync and Group Commit.

    1. Learning Goals

    By the end of this module, you should be able to:

    1. Confidentially choose between SQL and NoSQL for a given problem.
    2. Explain why a database is slow (Indexing, Locks, I/O).
    3. Design systems that handle Eventual Consistency gracefully.
    4. Understand the trade-offs of Isolation Levels in high-concurrency environments.

Module Chapters