9. Write-Ahead Logging (WAL)

Database systems must maintain high performance during normal operations while simultaneously ensuring data durability in the event of a system failure.

A naive approach to preventing data loss is to synchronously write (flush and sync) a modified page from shared memory to storage whenever a transaction inserts, updates, or deletes a tuple. However, this method results in extremely poor performance because each write involves a whole page and may require I/O to different locations in storage.

Practical relational database systems use a transaction log instead of this approach. Rather than writing entire pages for each tuple change, the system records changes as small records appended sequentially, which is significantly lighter and more efficient. After a server crash, the database server can recover the database cluster by replaying those logged changes.

In computer science, the mechanism of recording changes in a transaction log before writing the corresponding data pages to storage is called Write-Ahead Logging (WAL).

PostgreSQL introduced WAL in version 7.1 (2001). WAL also provides the foundation for Point-in-Time Recovery (PITR), Streaming Replication (SR), and Logical Replication, which are described in Chapters 10, 11, and 12, respectively.

This chapter provides a detailed explanation of the following topics:

  • The logical and physical structures of WAL (the transaction log).
  • The internal layout of WAL data.
  • Writing WAL data.
  • The WAL writer process and WAL Summarizer Process.
  • Checkpoint processing.
  • Database recovery processing.
  • Managing WAL segment files.
  • Continuous archiving.
Historical Information

In PostgreSQL, the term WAL was historically used with two meanings: the write-ahead logging protocol itself and the transaction log produced by the protocol, referred to as the write-ahead log. The log was also referred to as XLOG.

Since PostgreSQL 10 (2017), PostgreSQL has increasingly distinguished the logging mechanism from the log itself in its documentation and source code.

Chapter Contents