Database Durability & WAL

📋 Overview

Durability is the "D" in ACID, guaranteeing that once a transaction is committed, it will survive system crashes, power failures, or OS reboots. The primary mechanism for achieving durability in modern databases is Write-Ahead Logging (WAL). By ensuring that transaction logs are safely persisted to disk before the data files are updated, the database can "recover" its state by replaying the log after a failure.

---

🏗️ Core Principles & Characteristics

• WAL (Write-Ahead Log): An append-only file on disk that records every change before it is applied to the database.
• Fsyc: The system call used to force data from the OS buffer cache to the physical disk platter. A commit isn't durable until the WAL is fsynced.
• Redo Logs: Part of the WAL used to "redo" committed changes that weren't yet flushed to the data files during a crash.
• Undo Logs: Used to "undo" changes from transactions that were in-progress but not yet committed when the crash occurred.
• Checkpoints: The process of flushing all dirty data from memory to the main data files, allowing the DB to safely discard old WAL logs.

---

⚖️ Trade-offs: Pros & Cons

• Pros:
  • Performance: Append-only logging is much faster than random writes to data files.
  • Safety: Provides a mathematical guarantee that committed data is safe.
  • Recovery: Enables "Point-in-Time Recovery" by replaying logs from a specific backup point.
• Cons:
  • I/O Overhead: Every write requires at least two disk operations (one for WAL, one for the data file later).
  • Storage Space: Long-running transactions can prevent WAL truncation, leading to disk space exhaustion.
  • Complexity: Recovery logic (Redo/Undo) is one of the most complex parts of a database engine.

---

🌍 Real-World Implementation

• PostgreSQL: Uses the pg_wal directory to store its transaction logs.
• MySQL (InnoDB): Uses ib_logfile0 and ib_logfile1 for redo logging.
• MongoDB: Uses "Journaling" (based on WiredTiger) to ensure durability.
• Redis: Offers AOF (Append-Only File) mode to provide durability beyond simple RDB snapshots.

---

💡 Interview "Gotchas" & Tips

• Disk vs. OS Cache: If a DB says it's durable, it means the data is on the Physical Disk, not just in the "OS Write Buffer." A power loss clears the OS buffer but not the disk.
• Group Commit: To improve performance, databases often "batch" multiple transaction commits into a single fsync call to the WAL.
• The "Crash Recovery" Process: 1. Read WAL. 2. Replay all Redo entries. 3. Rollback all Undo entries.
• SSD vs. HDD: WAL performance is significantly higher on SSDs because of faster sequential write speeds and lower fsync latency.

---

📐 Suggested Architecture Primitives

• Append-Only Log File: The WAL itself.
• Buffer Pool: In-memory cache of data pages.
• Checkpoint Scheduler: To manage the frequency of data flushes.
• Recovery Manager: To handle the startup sequence after a crash.