Summary of Chapter# 9 : "Write Ahead Logging (WAL)" from the book "The Internals of PostgreSQL"
Tags: postgres, apacheage, database, agenssql
Published: 2023-08-02 04:01:58

This blog aims to assist you in understanding the intital concepts of Chapter:9 [Write Ahead Logging (WAL)] from the book **_[The Internals of PostgreSQL](https://www.interdb.jp/pg/)_**.

> _**Note: Ensure that you have a thorough understanding of [Chapter 8 Part-1](https://dev.to/vkt1271/summary-of-chapter-8-buffer-manager-from-the-book-the-internals-of-postgresql-4i2d), [Chapter 8 Part-2](https://dev.to/vkt1271/summary-of-chapter-8-buffer-manager-from-the-book-the-internals-of-postgresql-part-2-4f6o), [Chapter 8 Part-3](https://dev.to/vkt1271/summary-of-chapter-8-buffer-manager-from-the-book-the-internals-of-postgresql-part-3-3fhj) and basics of PostreSQL before we proceed to Chapter 9, as it forms the foundation for our exploration.**_

So, Let's Start:

## Introduction to Chapter

- **Transaction log** is an essential part of database, because all of the database management system is required not to lose any data even when a system failure occurs.

- It is a history log of **all changes** and actions in a database system so as to ensure that no data has been **lost** due to failures, such as a **power failure**, or some other server failure that causes the **server crash**.

- **Log** contains sufficient information about each transaction executed already, the database server should be able to **recover** the database cluster by **replaying** changes and actions in the transaction log in case of the server crash.

- In PostgreSQL, **WAL** is an acronym of **_Write Ahead Log_**. The term is used as synonym of transaction log, and also used to refer to an implemented mechanism related to writing action to a transaction log (WAL).

--------------------------------------------------------------

## Overview of Chapter

**Insertion Operations without WAL**

- To provide efficient access to the relation's pages, every DBMS implements shared **buffer pool**.

- Assume that we insert some data tuples into **TABLE_A** on PostgreSQL which does not implement the WAL feature.

> **Insertion operations without WAL** in PostgreSQL is depicted in figure below:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/wg1famqp6kple5cast29.png)

- (1) Issuing the first INSERT statement, PostgreSQL loads the TABLE_A's page from a database cluster into the in-memory shared buffer pool, and inserts a tuple into the page.

- (2) Issuing the second INSERT statement, PostgreSQL inserts a new tuple into the page on the buffer pool. This page has not been written into the storage yet.

- (3) If the operating system or PostgreSQL server should fail for any reasons such as a power failure, all of the inserted data would be lost.

> Database without **WAL** is vulnerable to the system failures.

**Insertion Operations and Database Recovery**

- PostgreSQL writes all modifications as history data into a persistent storage, to prepare for failures.

- The history data are known as **XLOG record(s)** or **WAL data**.

- XLOG records are written into the in-memory **WAL buffer** by change operations such as _insertion, deletion, or commit action_.

- They are immediately written into a **WAL segment file** on the storage when a transaction commits/aborts.

- **LSN (Log Sequence Number)** of XLOG record represents the location where its record is written on the transaction log. 

- LSN of record is used as the **unique id of XLOG record**.

- PostgreSQL start to recover from the **REDO point**; that is, the location to write the XLOG record at the moment when the latest **checkpoint** is started.

- The database **recovery processing** is strongly linked to the **checkpoint processing** and both of these processing are inseparable.

> **Insertion operations with WAL** in PostgreSQL is depicted in figure below:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/6x252a4pmit5munxn8u2.png)

- (1) A checkpointer, a background process, periodically performs checkpointing. Whenever the checkpointer starts, it writes a XLOG record called **checkpoint record** to the current WAL segment. This record contains the location of the latest **REDO** point.

- (2) Issuing the first INSERT statement, PostgreSQL loads the TABLE_A's page into the shared buffer pool, inserts a tuple into the page, creates and writes a XLOG record of this statement into the WAL buffer at the location LSN_1, and updates the TABLE_A's LSN from LSN_0 to LSN_1.

- In this **example**, this XLOG record is a pair of a header-data and the tuple entire.

- (3) As this transaction commits, PostgreSQL creates and writes a XLOG record of this commit action into the WAL buffer, and then, writes and flushes all XLOG records on the WAL buffer to the WAL segment file, from LSN_1.

- (4) Issuing the second INSERT statement, PostgreSQL inserts a new tuple into the page, creates and writes this tuple's XLOG record to the WAL buffer at LSN_2, and updates the TABLE_A's LSN from LSN_1 to LSN_2.

- (5) When this statement's transaction commits, PostgreSQL operates in the same manner as in step (3).

- (6) Imagine when the operating system failure should occur. Even though all of data on the shared buffer pool are lost, all modifications of the page have been written into the WAL segment files as history data.

- PostgreSQL will automatically enter into the **recovery-mode** by restarting. PostgreSQL sequentially will read and replay **XLOG** records within the appropriate **WAL segment files** from the REDO point.

> **Database recovery using WAL** in PostgreSQL is depicted in figure below:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/1ktc0fh9bketzvnk45fb.png)

- (1) PostgreSQL reads the XLOG record of the first INSERT statement from the appropriate WAL segment file, loads the TABLE_A's page from the database cluster into the shared buffer pool.

- (2) Before trying to replay the XLOG record, PostgreSQL shall compare the XLOG record's LSN with the corresponding page's LSN. 

- The rules of the replaying XLOG records are shown below:

1. If the XLOG record's LSN is **larger** than the page's LSN, the data-portion of the XLOG record is to be inserted into the page and the page's LSN is updated to the XLOG record's LSN. 

2. If the XLOG record’s LSN is **smaller**, there is nothing to do other than to read next WAL data.

- For **Example**, the XLOG record is replayed since the XLOG record's LSN (LSN_1) is larger than the TABLE_A's LSN (LSN_0); then, TABLE_A's LSN is updated from LSN_0 to LSN_1.

- (3) PostgreSQL replays the remaining XLOG record(s) in the same way.

> PostgreSQL does not support **UNDO** log.

--------------------------------------------------------------

I hope, this blog has helped you in understanding the initial concepts of Write Ahead Logging (WAL) in PostreSQL.

Check out summary of [Chapter : 9 Part-2](https://dev.to/vkt1271/summary-of-chapter-9-write-ahead-logging-wal-from-the-book-the-internals-of-postgresql-part-2-4mn2)

If you want to understand [PostgreSQL In-Depth](https://www.youtube.com/playlist?list=PLhr1KZpdzukeAZuX0pNZ1lLDkaZWBOhQT).