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

This blog aims to assist you in understanding the medial 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 9 Part-1](https://dev.to/vkt1271/summary-of-chapter-9-write-ahead-logging-wal-from-the-book-the-internals-of-postgresql-56kn) and basics of PostreSQL before we proceed to Chapter 9 Part-2, as it forms the foundation for our exploration.**_

So, Let's Continue:

**Full-Page Writes**

- Suppose that the TABLE_A's page-data on the storage is corrupted, because the operating system has failed while the background-writer process has been writing the dirty pages. As XLOG records cannot be replayed on the corrupted page, we would need an additional feature that is **full-page writes** to deal with such failures.

- In PostgreSQL, such a XLOG record containing the entire page is referred to as **backup block** or **full-page image**.

> **Full page writes** in PostgreSQL is depicted in figure below:

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

- (1) The checkpointer starts a checkpoint process.

- (2) In the insertion of the first INSERT statement, though PostgreSQL operates in the almost the same manner as in the previous subsection, this XLOG record is the backup block of this page (i.e. it contains the page entirety), because this is the first writing of this page after the latest checkpoint.

- (3) As this transaction commits, PostgreSQL operates in the same manner as in the previous subsection.

- (4) In the insertion of the second INSERT statement, PostgreSQL operates in the same manner as in the previous subsection since this XLOG record is not a backup block.

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

- (6) To demonstrate the effectiveness of full-page writes, here we consider the case in which the TABLE_A's page on the storage has been corrupted due to the operating system failure occurred while the background-writer has been writing it into the HDD.

> **Database recovery with backup block** in PostgreSQL is depicted in figure below:

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

- (1) PostgreSQL reads the XLOG record of the first INSERT statement and loads the corrupted TABLE_A's page from the database cluster into the shared buffer pool. In this example, the XLOG record is a backup block, because the first XLOG record of each page is always its backup block according to the writing rule of full-page writes.

- (2) When a XLOG record is its backup block, another rule of replaying is applied: the record's data-portion (i.e. the page itself) is to be overwritten onto the page regardless of the values of both LSNs, and the page's LSN updated to the XLOG record's LSN.

- For **Example**, PostgreSQL overwrites the data-portion of the record to the corrupted page, and updates the TABLE_A's LSN to LSN_1. In this way, the corrupted page is restored by its backup block.

- (3) Since the second XLOG record is a non-backup block, PostgreSQL operates in the same manner as the instruction in the previous subsection.

- In this way, PostgreSQL can recover the database even if some data write errors occur due to a process or operating system down.

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

## Transaction Log and WAL Segment Files

- Logically, PostgreSQL writes XLOG records into the transaction log which is a virtual file **8-byte** long (**16** ExaByte).

- A transaction log capacity is effectively **unlimited** and so can be said that **8-byte** address space is vast enough, it is impossible for us to handle a file with the capacity of 8-byte length. So, a transaction log in PostgreSQL is divided into files of **16-Mbyte**, by default, each of which known as **WAL segment**.

> **Transaction log and WAL segment files** in PostgreSQL is depicted in figure below:

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

- Using the built-in function **pg_xlogfile_name** (version 9.6 or earlier) or **pg_walfile_name** (versoin 10 or later), we can find the WAL segment file name that contains the specified LSN.

- For **Example**:

```
testdb=# SELECT pg_xlogfile_name('1/00002D3E');  # In version 10 or later, "SELECT pg_walfile_name('1/00002D3E');"
     pg_xlogfile_name     
--------------------------
 000000010000000100000000
(1 row)
```

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

## Internal Layout of WAL Segment

- A WAL segment is a **16 MB** file, by default, and it is internally divided into pages of **8192 bytes** (8 KB). The first page has a header-data defined by the structure **XLogLongPageHeaderData**, while the headings of all other pages have the page information defined by the structure **XLogPageHeaderData**. 

- Following the page header, XLOG records are written in each page from the beginning in **descending** order.

> **Internal layout of a WAL segment file** in PostgreSQL is depicted in figure below:

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

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

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

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

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