Pebellslearn by building
0%
RoadmapsMatrixCoursesSandboxSign in
High Level Database Concepts  ›  Lesson

The Write-Ahead Log

Write-ahead Log 14 minPostgreSQL records every change in the write-ahead log and flushes it at commit, so after a crash it can replay the log from the last checkpoint.

The Write-Ahead Log

The power fails in Pedal's server room one second after a payment commits. The changed table page was still only in memory. Yet when the server comes back, the payment is there. The reason is one rule PostgreSQL never breaks.

The rule

Log the change first, flush it at commit, write the tables later.

Every change is first described in the write-ahead log (WAL): an append-only stream of records such as "in page 812 of payments, insert this row". The WAL lives in the pg_wal directory, split into segment files of 16 MB by default.

  • Log first: a data page may not reach disk before the WAL record describing its change.
  • Flush at commit: COMMIT returns only after the transaction's WAL records are safely on disk. That is where durability comes from.
  • Tables later: the changed data pages stay in shared_buffers and are written out lazily by the background writer and the checkpointer.

Sequential appends to one log are far cheaper than scattered writes to many table files, so this design is fast as well as safe.

A checkpoint writes every dirty page to the data files and records a point in the WAL where the files are known to be complete. After a crash, PostgreSQL starts from the last checkpoint and replays the WAL forward. WAL older than the last checkpoint is no longer needed for recovery and is recycled.

The same stream powers two more DBA tools:

  • Replication: a standby server receives the WAL and replays it continuously.
  • Point-in-time recovery: a base backup plus archived WAL can rebuild the database as of any moment.
SHOW wal_level;             -- replica by default
SELECT pg_current_wal_lsn(); -- the current position in the log

The shortcut

For any "what survives?" question, ask: was the WAL for it flushed? If yes, recovery will rebuild it. If no, it never happened.

The payment and the power cutStep 1 of 4

  1. INSERT

    The new payment row goes into a page in memory, and a WAL record describing it is added to the WAL buffer.

The trap is turning fsync off to "speed things up". Then even flushed WAL may not really be on disk, and a crash can corrupt the whole cluster. The safe speed knob is synchronous_commit = off, which risks losing only the last fraction of a second of commits, never corruption.

Drills

Answer fast — trust the rule.

quick drill

When does COMMIT return to the application?

quick drill

Where does PostgreSQL keep its WAL files?

quick drill

After a crash, where does recovery start replaying the WAL?

quick drill

Which setting trades a small window of lost commits for speed, without risking corruption?

Remember

  • Log the change first, flush it at commit, write the tables later.
  • Ask "was the WAL flushed?" to know what survives a crash.
  • Checkpoints bound recovery time; never turn fsync off.

Practice — level up

0/1 solved

Write your answer, then compare it with a rubric and a model answer.

ExplainTell the crash story

Explain to a developer why a payment committed one second before a power cut is still there after restart, even though its table page was only in memory. Mention checkpoints and one other use of the WAL.