PostgreSQL Summary Pt7
Tags: postgres, postgressql
Published: 2023-07-08 22:29:20


Write Ahead Log (WAL) is a transaction logging mechanism implemented in PostgreSQL 7.1. 
I'll be discussing some things needed to understand WAL.

### Why is it useful?
- It ensures that no data is lost even when a system failure occurs.
- It also makes possible the implementation of Point-in-Time Recovery (PITR) and Streaming Replication (SR). 

### How?
The log contains information about each transaction executed, allowing database server to recover the database cluster by **replaying** changes and actions.

Here are some points that will be useful later:
- Postgres writes all modifications as history data (aka **XLOG records** or **WAL data**) to persistent storage.
- XLOG records are written into a buffer in memory called **WAL buffer**. When a transaction commits or aborts (and others), they are immediately written into a **WAL segment file** on the storage. 
- As mentioned in [part 2](https://dev.to/yosefahab/postgresql-summary-pt2-5cnh), **LSN (Log Sequence Number)** is the unique id of an XLOG record and represents the location where it is written on the transaction log.
- Postgres starts to recover from the **REDO point**, which is the location to write the XLOG record at the moment when the latest **checkpoint** is started.
- The database *recovery process* and the _checkpoint process_ always occur together.

## Insertion with WAL

![Insertion with WAL](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/zyq2hzbif89t3rlsfvgb.png)
The following are the steps that Postgres performs when an `INSERT` statement is issued:
1. Whenever the checkpointer starts, it writes an 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 database table'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's LSN from _LSN_0_ to _LSN_1_.   
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'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).

## WAL Segment structure
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](https://github.com/postgres/postgres/blob/master/src/include/access/xlog_internal.h), 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. 

![WAL Segment Structure](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/4qzd3etsr2hvt7bfjxw2.png)

## XLOG Record Structure
An XLOG record comprises the general header portion and each associated data portion.

### Header Structure
The header structure is the same for all XLOG records. It consists of the following fields:
- `version`: The XLOG record version number.
- `type`: The type of XLOG record.
- `timestamp`: The timestamp of the XLOG record.
- `length`: The length of the XLOG record.

### Data Portion (Version 9.4 or Earlier)
The data portion of an XLOG record in version 9.4 or earlier is specific to the type of XLOG record. For example, the data portion of a `COMMIT` XLOG record contains the transaction ID of the committed transaction.

### Data Portion (Version 9.5)
The data portion of an XLOG record in version 9.5 has a common format. It consists of the following fields:
* `data_header`: A header that describes the data portion.
* `data`: The actual data.

The `data_header` field contains the following information:
* `data_format`: The data format version number.
* `data_length`: The length of the `data` field.

The `data` field can contain any type of data. The specific format of the data is determined by the `data_format` field.

#### References 
1. https://www.interdb.jp/pg/pgsql09.html
2. https://www.postgresql.org/docs/current/wal-intro.html
3. https://en.wikipedia.org/wiki/Write-ahead_logging
4. https://www.interdb.jp/blog/pgsql/pg95walformat/