9. Write Ahead Logging (WAL)

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

A naive approach to preventing data loss is to synchronously write (flush and sync) an updated page from shared memory to storage whenever a transaction inserts, updates, or deletes a tuple. However, this method results in extremely poor performance.

Practical database systems use transaction logs instead of writing entire pages for each tuple change. These logs serve as a historical record of all changes and actions within the system. This record enables the database server to recover the database cluster by replaying the changes following a server crash. Writing transaction logs is significantly lighter and more efficient than writing full pages.

In computer science, WAL stands for Write-Ahead Logging, a protocol requiring that all changes and actions be written to a transaction log before the data itself. However, in PostgreSQL, WAL also stands for Write Ahead Log. PostgreSQL uses the term WAL interchangeably with “transaction log,” and it also refers to the implemented mechanism for writing these logs. Although this terminology is potentially confusing, this documentation adopts the PostgreSQL definition.

The WAL mechanism was first implemented in version 7.1 to mitigate the impacts of server crashes. This mechanism also enabled 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 covering the following topics:

• The logical and physical structures of the WAL (transaction log).
• The internal layout of WAL data.
• Writing of WAL data.
• WAL writer process.
• The checkpoint processing.
• The database recovery processing.
• Managing WAL segment files.
• Continuous archiving.