Write-Ahead Logs (WAL)

📋 Overview

A Write-Ahead Log (WAL) is a foundational technique in database systems used to provide atomicity and durability (ACID). The core principle is simple: any change to the database must be recorded in a persistent, append-only log on disk before it is applied to the actual data pages. This ensures that the system can always recover to a consistent state after a crash.

---

🏗️ Core Principles & Characteristics

• Sequential Writes: Appending to a log is a sequential I/O operation, which is significantly faster than the random I/O required to update data pages across a disk.
• Log Before Data: The "Write-Ahead" rule ensures that if the system crashes while updating the main data file, the log contains a record of the intent, allowing for a "Redo" or "Undo" operation.
• Check-pointing: Periodically, the database flushes all in-memory changes to the main data files and marks a "Checkpoint" in the WAL. This limits the amount of log that must be replayed during recovery.
• Redo & Undo Logs:
  • Redo: Stores the new values to re-apply committed transactions after a crash.
  • Undo: Stores the old values to roll back uncommitted transactions during recovery.

---

⚖️ Trade-offs: Pros & Cons

Pros

• Durability: Guarantees that once a transaction is acknowledged as "Committed," it will survive even a total power failure.
• Performance: By converting random writes into sequential log appends, WAL significantly improves the write throughput of the database.
• Crash Recovery: Provides a deterministic way to restore the database to its last consistent state.

Cons

• Log Management: WAL files can grow very large if not properly managed or archived, leading to disk space issues.
• Latency: Every write now requires two disk operations (one to the log, one eventually to the data page), though the first is fast sequential I/O.
• Recovery Time: If the time between checkpoints is too long, the recovery process (replaying the log) after a crash can be slow.

---

🌍 Real-World Implementation

• PostgreSQL: Uses WAL for all durability and as the basis for "Streaming Replication" to replicas.
• MySQL (InnoDB): Implements WAL through "Redo Logs" (ib_logfile) and "Undo Logs."
• SQLite: Supports a "WAL Mode" that allows for better concurrency (multiple readers and one writer).
• Distributed Systems: Apache Kafka and Cassandra's "Commit Log" use the same sequential append-only principle to ensure high-performance durability.

---

💡 Interview "Gotchas" & Tips

• FSYNC: Explain that a WAL write is only "Durable" once the fsync() system call has successfully flushed the OS cache to the physical disk.
• Group Commit: A performance optimization where multiple concurrent transactions are "batched" and written to the WAL in a single disk flush.
• Replication: Mention that WAL is the primary way modern databases handle replication—the "Leader" sends its WAL stream to the "Follower," who replays it.
• WAL vs. Journaling: They are similar concepts. Journaling is often used in file systems (like EXT4), while WAL is the term used in databases.

---

📐 Suggested Architecture Primitives

• Append-Only Log: The core data structure for WAL.
• LSM-Trees: Many modern NoSQL DBs (like Cassandra) use WALs in conjunction with Memtables and SSTables.
• Replication Stream: Sending WAL segments to standby servers.
• Log Archiver: To move old WAL segments to cold storage (like S3) for point-in-time recovery.