Systems and protocols · Intermediate

PostgreSQL WAL: log changes before data pages

Connect durable log records, delayed page writes and redo without confusing recovery with backups.

Ask Logeia about this articleTry the three questionsReview your answers

Updated

On this page
  1. What must reach durable storage first?
  2. A crash after commit
  3. Checkpoints reduce recovery work
  4. Recovery is not a complete backup plan
  5. Trace durable logging while a data page lags
  6. Match the acknowledgment point to durability settings
  7. Read an LSN as a position, not a universal time estimate
  8. Use restoration to verify the complete recovery chain
  9. Ask Logeia about this articleCheck your understanding

What must reach durable storage first?

WAL means write-ahead logging: information needed to reproduce a page change must become durable before that changed data page is written. This ordering allows data pages to be written later while preserving a basis for crash recovery. It avoids requiring every modified table and index page to be flushed for every commit.

Writing into a memory buffer and flushing to durable storage are different events. When reasoning about commit acknowledgment, state the durability settings being assumed. A log record that exists only in volatile memory cannot support recovery after that memory is lost.

A crash after commit

Imagine a committed row update followed by a crash before the changed data page was written. If the required WAL is durable, recovery can redo the change and bring the data page forward. This is why delayed data-page writes need not contradict a durable commit.

Do not infer that a transaction failed merely because its final data page had not yet reached storage. The relevant question is which log and page states survived under the configured durability policy. Recovery uses that persistent information rather than the client’s memory of when a write happened.

Checkpoints reduce recovery work

Checkpoints advance persistent page state and help manage the point from which recovery must proceed. They do not make WAL unnecessary and should not be pictured as a complete backup performed for every request. Log activity and data-page writing remain related but distinct work.

Write-heavy workloads can be limited by WAL generation or flush latency. When comparing configurations, check whether durability guarantees changed. A faster run obtained by accepting a larger loss window is not a like-for-like performance improvement over a more durable configuration.

Recovery is not a complete backup plan

WAL supports crash recovery and other mechanisms, including replication, but retained log files alone are not a universal backup plan. Physical restoration requires a suitable base state and the required log sequence. Retention gaps and an untested restore procedure can still prevent recovery.

Disk loss, accidental deletion and an incorrect committed update are different scenarios. Test restoration in a separate environment rather than assuming that the presence of WAL proves recoverability. Reconstructing a consistent storage state and selecting the business point to restore are separate decisions.

Trace durable logging while a data page lags

Consider a balance changing from 100 to 90 under ordinary durability settings. The in-memory page and WAL buffers first reflect the update. Commit-required WAL is made durable before success is acknowledged. If a crash follows while the data-file page still contains 100, recovery can redo the required surviving WAL to reconstruct the committed balance of 90. Adding a record only to a volatile log buffer would not provide the same power-loss guarantee. These stages form a worked reasoning example, not a measured crash experiment.

The write-ahead order prevents a changed data page from becoming durable without the logging information needed for recovery. It does not require every changed data page to be flushed at every commit. Checkpoints manage page-writing progress and a recovery starting point. A suitable base backup plus continuous required WAL supports physical restoration and point-in-time recovery. Record commit acknowledgment, durable WAL, durable data pages and backup coverage separately to explain which failures the system is prepared to handle.

StageInformation and meaning
Update reaches buffersNot necessarily durable yet
Required WAL becomes durableBasis for ordinary durable commit acknowledgment
Crash before data-page writeSurviving required WAL can support redo
Storage medium is lostSeparate restore data and procedure are needed

Match the acknowledgment point to durability settings

Ordinary durable-commit reasoning assumes that PostgreSQL waits for the required local WAL flush and that storage honors its persistence contract. With synchronous_commit disabled, a transaction recently reported successful can be lost after a crash. That is a different issue from leaving the database structurally inconsistent. Local persistence, transport to another server, remote writing, remote flushing, and remote replay are also distinct milestones. Merely adding a replica does not make every commit wait for all of them. When comparing throughput, state the completion contract and the failures it covers rather than presenting differently durable settings as equivalent ways to perform the same job.

Read an LSN as a position, not a universal time estimate

A log sequence number identifies a position in WAL and helps distinguish generation, sending, writing, flushing, and replay. A replica can have received records that it has not yet replayed, so receive progress alone does not establish query visibility. Position differences can express a byte gap, but converting that gap directly into seconds assumes a generation or replay rate that may change. Examine workload, I/O, long operations, and waits alongside the positions. Use observations from compatible timelines and the same deployment. A live network connection and a moving receive counter are useful signals, but neither by itself proves that reads on the replica include the latest acknowledged primary transaction.

Use restoration to verify the complete recovery chain

Point-in-time recovery needs an appropriate base backup and the continuous WAL required from that base to the intended target. Having many later segments does not automatically repair one missing segment in the middle. Test restoration into a separate environment and confirm the data expected at the chosen point; a directory listing of backup files is insufficient evidence. Archive capacity, retention, credentials or encryption keys, and recovery duration are part of the operational plan. Manually deleting files managed inside the active WAL directory to reclaim space can remove information still required for recovery or replication. Investigate why retention grew and which consumers need it before choosing a remedy.

Reading and practice are recorded separately in this browser, for each language.

Learn this in order

Understand the systems behind the web · STEP 13

Choose a learning path