WAL and Durability

How a write-ahead log makes a commit survive a crash: the flush that defines a commit, the caches that can lie about it, group commit, the durability settings that trade safety for speed, and the log as a replication stream.

Overview

Durability is the D in ACID: once the database says a transaction committed, that change must survive a crash, a power cut or a restart. The tool almost every database uses for it is the write-ahead log (WAL): an append-only file where each change is recorded before the data files are touched.

The idea is simple, but the details decide whether a commit is really safe. Data written by a program usually sits in the operating system's memory first, and sometimes in a disk's own cache after that. A commit is durable only when its log record has been forced all the way to storage that survives power loss. That forced write, an fsync call, is the slowest step in a commit, so databases work hard to share it between transactions, and they offer settings that skip it for speed.

This chapter follows one commit from the application to the disk platter or flash cell. The crash-recovery algorithm itself (checkpoints, ARIES redo and undo) is covered in the Database Recovery topic; here the focus is on what makes a commit durable and what each setting risks.

What you will be able to do

  • Say exactly when a commit becomes durable, and why the log, not the data files, is flushed at commit.
  • Explain where data can hide between write() and stable storage, and what fsync does about it.
  • Explain group commit and estimate its effect with simple arithmetic.
  • Compare PostgreSQL's synchronous_commit levels and InnoDB's innodb_flush_log_at_trx_commit, and say what each can lose.
  • Describe how the same log feeds replicas, backups and change data capture.

Key concepts

  • Write-ahead rule
  • fsync and fdatasync
  • Commit point
  • Group commit
  • synchronous_commit
  • innodb_flush_log_at_trx_commit
  • Torn pages
  • Log sequence number (LSN)
  • Synchronous replication
  • Logical decoding

Interview questions

  • Walk me through what happens on disk when a transaction commits.
  • What is group commit, and why does it matter?
  • What is the difference between synchronous_commit = off and fsync = off?
  • Your database reports a commit as durable, then loses it after a power cut. What could be wrong?
  • How do InnoDB's innodb_flush_log_at_trx_commit values differ?
  • Why do databases write full page images to the log?
  • How does the WAL relate to replication and change data capture?
  • Does a larger log or fewer checkpoints make a database faster or slower?

Sign up free to read the full topic

More System Design topics

Start free on Cariara · Coding problems · System design studio