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) the updated page from shared memory to storage whenever a tuple is inserted, updated, or deleted. However, this method results in extremely poor performance.

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

In the field of computer science, WAL is an acronym for Write-Ahead Logging, which is a protocol or rule that requires both changes and actions to be written to a transaction log. However, in PostgreSQL, WAL is also an acronym for Write Ahead Log. In PostgreSQL, the term WAL is used interchangeably with transaction log, and it also refers to the implemented mechanism for writing actions to a transaction log (WAL). Although this can be confusing, this document will adopt the PostgreSQL definition.

The WAL mechanism was first implemented in version 7.1 to mitigate the impacts of server crashes. It also made possible the implementation of the Point-in-Time Recovery (PITR) and Streaming Replication (SR), both of which are described in Chapters 10 and 11, respectively.

Although understanding the WAL mechanism is essential for system integrations and administration using PostgreSQL, the complexity of this mechanism makes it impossible to summarize its description in brief. Therefore, the complete explanation of WAL in PostgreSQL is as follows:

• 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.