PostgreSQL Summary Pt7

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, 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

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

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/