Database Internals: Deep Dive into Distributed Systems

Executive Summary: Under the Hood of Data Storage Engines

Databases are the most complex software systems in existence. Modern applications depend on databases for strict durability, microsecond query speeds, and seamless linearizable consistency across global server clusters. Yet to most developers, database management systems (DBMS) remain black-box magic.

Database Internals is the 360-page benchmark reference manual for database engineers, infrastructure developers, backend systems architects, and storage engine developers. Spanning 8 deep modules, this guide demystifies the entire storage engine and distributed systems stack: comparing in-place B+Trees with write-optimized LSM-Trees, dissecting Write-Ahead Logs (WAL), implementing ARIES crash recovery, configuring MVCC snapshot isolation, modeling Raft consensus log replication, and analyzing vectorized SIMD query execution engines.

Deep Dive: Core Storage Engine & Consensus Mechanics

The handbook provides functional Python, C++, and pseudo-code implementations across five primary database engine pillars:

1. In-Place B+Trees vs Append-Only LSM-Trees

Analyzing storage data structures for read-heavy vs write-heavy workloads:

• B+Tree Page Splits & Slotted-Pages: Organizing 8KB disk pages, managing internal node keys, leaf node pointers, and page splits during insertions.
• LSM-Tree Flushes & SSTable Compaction: Writing incoming writes sequentially to a skip-list MemTable, flushing to immutable Sorted String Tables (SSTables), and running Size-Tiered or Leveled Compaction with Bloom filter acceleration.

2. Transaction Processing, WAL & ARIES Recovery

Ensuring total ACID compliance and crash survival:

• ARIES Crash Recovery Algorithm: Executing the 3-phase recovery process—Analysis (reconstructing dirty page table), Redo (repeating history to restore state), and Undo (rolling back uncommitted transactions using Compensation Log Records / CLRs).

3. Distributed Consensus: Raft Protocol State Machines

Achieving fault-tolerant state machine replication across untrusted networks:

• Raft Invariants: Leader election terms, `AppendEntries` RPCs, log matching property, leader completeness, and commit index advancement.

Field Engineering: Python LSM-Tree MemTable & SSTable Engine

Chapter 3 of the handbook provides practical Python source code for an append-only LSM-Tree storage engine with SSTable flushing:

import json
import os

class LSMTreeEngine:
    def __init__(self, memtable_threshold=3):
        self.memtable = {}
        self.threshold = memtable_threshold
        self.sstable_count = 0

    def put(self, key, value):
        self.memtable[key] = value
        print(f"[+] MemTable PUT: {key} => {value}")
        
        if len(self.memtable) >= self.threshold:
            self.flush_memtable()

    def flush_memtable(self):
        self.sstable_count += 1
        filename = f"sstable_{self.sstable_count}.json"
        
        # Sort keys before writing to SSTable
        sorted_data = dict(sorted(self.memtable.items()))
        with open(filename, 'w') as f:
            json.dump(sorted_data, f)
            
        print(f"[FLUSH] MemTable Flushed to Immutable SSTable: {filename}")
        self.memtable.clear()

    def get(self, key):
        # 1. Search Active MemTable
        if key in self.memtable:
            return f"[FOUND in MemTable] {self.memtable[key]}"

        # 2. Search SSTables in reverse order (newest first)
        for i in range(self.sstable_count, 0, -1):
            filename = f"sstable_{i}.json"
            if os.path.exists(filename):
                with open(filename, 'r') as f:
                    data = json.load(f)
                    if key in data:
                        return f"[FOUND in {filename}] {data[key]}"
        return "[-] Key Not Found"

db = LSMTreeEngine()
db.put("usr_1", "Alice")
db.put("usr_2", "Bob")
db.put("usr_3", "Charlie") # Triggers SSTable Flush 1
print(db.get("usr_1"))

#include 
#include 
#include 

enum NodeState { FOLLOWER, CANDIDATE, LEADER };

struct LogEntry {
    int term;
    std::string command;
};

class RaftNode {
public:
    int nodeId;
    int currentTerm;
    int votedFor;
    NodeState state;
    std::vector log;
    int commitIndex;

    RaftNode(int id) : nodeId(id), currentTerm(0), votedFor(-1), state(FOLLOWER), commitIndex(0) {}

    void startElection() {
        state = CANDIDATE;
        currentTerm++;
        votedFor = nodeId;
        std::cout << "[ELECTION] Node " << nodeId << " started election for Term " << currentTerm << std::endl;
    }

    void receiveAppendEntries(int leaderId, int term, int prevLogIndex, int prevLogTerm) {
        if (term < currentTerm) {
            std::cout << "[-] Rejected RPC from outdated Leader " << leaderId << std::endl;
            return;
        }
        state = FOLLOWER;
        currentTerm = term;
        std::cout << "[+] Accepted Log Append RPC from Leader " << leaderId << " (Term " << term << ")" << std::endl;
    }
};

Complete Table of Contents & Module Syllabus

• Module 01 Storage Engine Fundamentals: Disk Architecture & Page Formats

  Pages 1–45
  Block I/O storage devices, page layouts, slotted-page architectures, binary serialization, and memory-mapped files (mmap).
• Module 02 In-Place Storage Engines: B-Trees, B+Trees & Index Maintenance

  Pages 46–90
  B+Tree node structures, fanout, depth calculations, page split algorithms, leaf node linking, and PostgreSQL/InnoDB index internals.
• Module 03 Append-Only Storage Engines: LSM-Trees, SSTables & Compaction

  Pages 91–135
  Log-Structured Merge Trees, skip-list MemTables, SSTable flushing, Leveled vs Size-Tiered Compaction, and Bloom filter optimizations.
• Module 04 Transaction Management, WAL & ARIES Crash Recovery

  Pages 136–180
  ACID guarantees, Write-Ahead Logging (WAL), Log Sequence Numbers (LSN), and the 3-phase ARIES recovery algorithm (Analysis, Redo, Undo).
• Module 05 Concurrency Control: Locks, MVCC & Isolation Anomalies

  Pages 181–225
  Two-Phase Locking (2PL), Multi-Version Concurrency Control (MVCC), tuple visibility rules (`xmin`/`xmax`), and preventing Write Skew anomalies.
• Module 06 Distributed Data Systems: CAP Theorem, PACELC & Replication

  Pages 226–270
  CAP theorem trade-offs, PACELC latency bounds, synchronous vs asynchronous replication, and multi-leader topology conflicts.
• Module 07 Distributed Consensus: Raft Protocol Mechanics & 2PC

  Pages 271–315
  Leader elections, log entry replication, term safety invariants in Raft, Paxos protocol, and Two-Phase Commit (2PC) atomicity.
• Module 08 Query Engines: Parsing, Optimization & Vectorized Execution

  Pages 316–360
  Volcano iterator execution (`open()`, `next()`, `close()`), SIMD vectorized processing, Cost-Based Optimizers (CBO), and Join algorithms.

Who Should Read This Handbook?

This handbook is designed for database engineers and backend systems architects:

Verified Database Architect Reviews

Frequently Asked Questions