Skip to content

WAL (Write-Ahead Log)

1. What is it? (2-3 lines)

Write-Ahead Logging (WAL) is an append-only logging technique where database state modifications are written and flushed to non-volatile disk storage before the corresponding changes are applied to the main database data pages (heap/B+ Tree).

2. Why does it exist?

Random disk I/O to update B+ Tree data pages in-place is extremely slow (\(O(\log N)\) random I/O). WAL converts slow random disk writes into fast sequential append-only writes (\(O(1)\) sequential I/O). Crucially, it guarantees the Durability (D) and Atomicity (A) of ACID transactions, allowing immediate crash recovery without requiring instant data page flushes to disk.

3. Where is it used?

  • PostgreSQL: Write-Ahead Log (WAL) files in pg_wal.
  • MySQL InnoDB: Redo Log (ib_logfile0).
  • Distributed Databases / Consensus: Raft/Paxos log engines, Apache Cassandra (CommitLog), SQLite (WAL mode).

4. How does it work? (simple flow)

  1. Transaction Mutation: User executes UPDATE accounts SET balance = 500 WHERE id = 1.
  2. Append Log Record: Database appends a binary log record (Log Sequence Number - LSN) detailing the exact change to the WAL memory buffer.
  3. Flush Log (fsync): On transaction COMMIT, the WAL memory buffer is flushed sequentially to disk (fsync).
  4. Dirty Page Modification: Data pages in memory (Buffer Pool) are updated in-place as "dirty pages".
  5. Asynchronous Checkpointing: Background threads (Checkpointer) periodically write dirty data pages from RAM to main database disk storage in batches.
  6. Crash Recovery: If the node crashes, the engine replays LSN log records from the last valid checkpoint up to the end of the WAL to restore in-memory state.

5. Advantages

  • Converts random disk I/O into sequential append-only disk writes (massive throughput gain).
  • Enables fast transaction commits (only the small WAL log entry must be flushed to disk via fsync).
  • Guarantees crash recovery and point-in-time recovery (PITR) by replaying log records.

6. Limitations

  • Disk Bottleneck: fsync bottlenecking under high commit QPS if disk write latency is high (requires NVMe SSDs).
  • WAL Accumulation: Long-running transactions or slow replication consumers cause massive WAL accumulation, risking disk space exhaustion.
  • Checkpoint Spikes: Heavy background checkpointing can cause temporary disk I/O stalls (checkpoint spikes).

7. Common interview questions (10)

  1. What is the difference between Redo Log (WAL) and Undo Log in InnoDB?
  2. How does fsync configuration (innodb_flush_log_at_trx_commit=1 vs 2 vs 0) affect ACID durability and throughput?
  3. What is a Checkpoint in a WAL-based database engine?
  4. How does Log Sequence Number (LSN) guarantee transaction ordering and recovery?
  5. How is WAL utilized for Physical Streaming Replication in PostgreSQL?
  6. What happens during database recovery if a crash occurs during a checkpoint?
  7. How does WAL mode in SQLite differ from Rollback Journal mode?
  8. How does Cassandra's CommitLog differ from a relational database WAL?
  9. What is Group Commit in WAL processing and how does it batch disk writes under high QPS?
  10. How does WAL enable Point-In-Time Recovery (PITR) when restoring backups?

8. Common misconceptions

  • Misconception: Data pages are written to disk on transaction COMMIT. Fact: Only the WAL is flushed to disk on commit; data pages are modified in RAM and flushed asynchronously during checkpoints.
  • Misconception: WAL files grow forever. Fact: WAL files are recycled or deleted once the changes are safely written to data pages via checkpointing.

10. 30-second cheat sheet

  • Golden Rule: Always write to log on disk BEFORE writing data to main database pages.
  • Performance Key: Sequential disk append (\(O(1)\)) vs Random page write (\(O(\log N)\)).
  • Durability Mechanism: COMMIT succeeds as soon as WAL buffer is fsync'd to disk.
  • Group Commit: Batches multiple concurrent transaction commits into 1 single disk fsync call to maximize QPS.