VI EN
System Design

Write-Ahead Log: the database said “saved”, then the power died — is the data safe?

Binh 2 min read Đọc bài này bằng tiếng Việt

TL;DR

With a Write-Ahead Log (WAL), every change is appended to a log on disk before the client gets “OK”. Tables are updated later. After a power cut, the database replays the log on startup and recovers every acknowledged change.

Watch on Facebook

The database returns "OK" for your write, and immediately afterwards the power goes out. Is the data still there? For most serious databases the answer is yes, thanks to the Write-Ahead Log (WAL). The principle: every change is appended to a log on disk first, then "OK" is returned to the client; the data tables are updated later. After a power loss, the database replays the log on startup to recover every acknowledged change.

What happens

  1. A client writes 6 records. Each is appended to the log on disk, and an ack is returned as soon as that's done.
  2. The data table (in RAM) lags behind: only 3 records so far.
  3. Power loss. Everything in RAM is gone; the log on disk still has all 6.
  4. On restart, the database replays the log from record 1 to 6 into the table. Nothing is lost.

Why is it fast and still safe?

Writing directly to tablesWriting the WAL first
Random writes across many data pages: slowSequential appends to the end of the log: fast
A power loss mid-write can leave pages half-corruptedThe log is the source of truth for recovery
—Also used for replication and point-in-time recovery

Sequential disk writes are much faster than random ones, so adding a log write doesn't slow writes much; on the contrary, it lets table updates be deferred and batched.

Related concepts

  • Flush/fsync: forces the OS to actually write to disk; data is only truly durable once the flush completes.
  • Checkpoint: periodically pushes in-memory changes to the data tables, so old log segments can be discarded and recovery time shortens.
  • Replay/Recovery: on startup, the database reads the log from the latest checkpoint and reapplies changes.
  • Replication: ships the log to replicas so they apply the same changes.

How to apply it

  • Know what "ack" means: in PostgreSQL, the ack comes from WAL flushed to disk (with synchronous_commit on), not from the table.
  • Weigh the trade-off: disabling synchronous flush for speed means you may lose the last few transactions in a crash.
  • Operations: monitor WAL volume and archive WAL for backups and point-in-time recovery.
  • Infrastructure: disks with unsafe write caches can break the guarantee; check your storage configuration.

Sources & references

  1. Write-Ahead Logging (WAL) — PostgreSQL Docs
More on System Design