Summary of Chapter# 9 : "Write Ahead Logging (WAL)" from the book "The Internals of PostgreSQL" Part-4
Tags: postgres, apacheage, database, agenssql
Published: 2023-09-10 09:17:54


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

So, Let's Continue:

## WAL Writer Process

- **WAL writer** is a background process to check the WAL buffer periodically and write all unwritten XLOG records into the WAL segments. 

- The purpose of this process is to **avoid burst** of writing of XLOG records. If this process has not been enabled, the writing of XLOG records might have been **bottlenecked** when a large amount of data committed at one time.

- WAL writer is working by default and cannot be disabled. Check interval is set to the configuration parameter **wal_writer_delay**, default value is **200 milliseconds**.

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

## Checkpoint Processing in PostgreSQL

- In PostgreSQL, the **checkpointer** (background) process performs checkpoints. 

- It starts when **one** of the following occurs:

- (1) The interval time set for _checkpoint_timeout_ from the previous checkpoint has been elapsed (the default interval is **300 seconds**).

- (2) In version 9.4 or earlier, the number of WAL segment files set for checkpoint_segments has been consumed since the previous checkpoint (the default number is **3**).

- (3) In version 9.5 or later, the total size of the WAL segment files in the pg_wal directory has exceeded the value of the parameter max_wal_size (the default value is **1GB** (**64 files**)).

- (4) The PostgreSQL server stops in **smart or fast mode**.

- (5) A superuser issues the **CHECKPOINT** command manually.

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

## Database Recovery in PostgreSQL

- PostgreSQL implements **redo log-based** recovery.

- If the database server crashes, PostgreSQL can restore the database cluster by sequentially replaying the **XLOG records** in the WAL segment files from the **REDO** point.

- When PostgreSQL starts up, it first reads the **pg_control** file.

> **Details of the recovery process** in PostgreSQL is depicted in figure below:

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

- (1) PostgreSQL reads all items in the **pg_control** file when it starts. If the state item is '_in production_', PostgreSQL enters **recovery-mode** because this means that the database was not shut down normally. If it is '_shut down_', PostgreSQL enters **normal startup-mode**.

- (2) PostgreSQL reads the latest checkpoint record, the location of which is written in the pg_control file, from the appropriate WAL segment file. It then gets the REDO point from the record. If the latest checkpoint record is invalid, PostgreSQL reads the one prior to it. If both records are unreadable, it gives up recovering by itself.

- (3) The appropriate resource managers read and replay XLOG records in sequence from the REDO point until they reach the end of the latest WAL segment. When an XLOG record is replayed and if it is a backup block, it is overwritten on the corresponding table page regardless of its LSN. Otherwise, a (non-backup block) XLOG record is replayed only if the LSN of the record is greater than the pd_lsn of the corresponding page.

> **Insertion operations during the background writer working** in PostgreSQL is depicted in figure below:

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

- (1) PostgreSQL inserts a tuple into the TABLE_A, and writes an XLOG record at LSN_1.

- (2) The background-writer process writes the TABLE_A page to storage. At this point, this page's pd_lsn is LSN_1.

- (3) PostgreSQL inserts a new tuple into the TABLE_A, and writes a XLOG record at LSN_2. The modified page is not written into the storage yet.

> **Database Recovery** in PostgreSQL is depicted in figure below:

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

- (1) PostgreSQL loads the first XLOG record and the TABLE_A page, but does not replay it because the LSN of the record (LSN_1) is not larger than the LSN of the page (also LSN_1). In fact, it is clear that there is no need to replay it.

- (2) Next, PostgreSQL replays the second XLOG record because the LSN of the record (LSN_2) is greater than the current LSN of the TABLE_A page (LSN_1).

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

## WAL Segment Files Management

- PostgreSQL writes XLOG records to one of the **WAL segment** files stored in the pg_wal subdirectory. A new WAL segment file is switched in if the old one has been filled up. The number of WAL files varies depending on several configuration parameters, as well as server activity. In addition, the management policy for WAL segment files has been improved in version 9.5.

**WAL Segment Switches**

- WAL segment switches occur when one of the following events happens:

- (1) WAL segment is filled up.
- (2) The function pg_switch_xlog is called.
- (3) The archive_mode parameter is enabled and the archive_timeout parameter has expired.
- When a WAL segment file is switched, it is usually **recycled** for future use. However, it may be removed later if it is not needed.

**WAL Segment Management**

- Whenever a checkpoint starts, PostgreSQL estimates and prepares the number of WAL segment files that will be needed for the next checkpoint cycle. This estimate is based on the number of WAL segment files that were consumed in **previous checkpoint** cycles.

- The number of WAL segment files is counted from the segment that contains the prior **REDO** point, and the value must be between the min_wal_size parameter (which default to **80 MB**, or **5 files**) and the max_wal_size parameter (which default to **1 GB**, or **64 files**).

- If a checkpoint starts, PostgreSQL will keep or recycle the necessary WAL segment files, and remove any **unnecessary** files.

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

## Continuous Archiving and Archive Logs

- **Continuous Archiving** is a feature that copies WAL segment files to archival area at the time when WAL segment switches, and is performed by the _archiver (background)_ process.

- The copied file is called an **archive log**.

- This feature is usually used for **hot physical backup** and **PITR (Point-in-Time Recovery)**.

- The path of archival area is set to the configuration parameter **archive_command**.

- **For Example**: `archive_command = 'cp %p /home/postgres/archives/%f'`, where, placeholder **%p** is copied WAL segment, and **%f** is archive log.

> **Continuous archiving** in PostgreSQL is depicted in figure below:

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

- The parameter **archive_command** can be set any Unix commands and tools, so you can transfer the archive logs to other host by setting the **scp** command or any file backup tools instead of ordinary copy command.

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

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

Check out summary of [Chapter : 10 Part-1](https://dev.to/vkt1271/summary-of-chapter-10-base-backup-point-in-time-recovery-from-the-book-the-internals-of-postgresql-5d68)

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