An overview of Write Ahead Logging - WAL in PostgreSQL

#apacheage #postgres #bitnine #apache

Even when a system failure occurs, a database management system is required to not lose any data as it is extremely crucial and hence transaction logs are an extremely essential part of any database management system.

A transaction log is a historical log of all the past transactions that have occurred and it is made to keep sure that no data is lost during events such as a power outage or a server crash.

In the world of computer science, WAL is an acronym for Write Ahead Logging which is a protocol to write both changes and actions made in the database, although in PostgreSQL, this is known as Write Ahead Log. In this short overview, we will take a look at the following subsections:

- The logical and physical structure of WAL

The logical and physical structure of WAL is such that if follows the conventional insertion operations and database recovery techniques used in other databases and PostgreSQL as well.
PostgreSQL will write all data as modifications into a persistent storage to prepare for failures and this historical data is known as XLOG records or WAL data.

- The internal layout of WAL

Logically speaking, PostgreSQL writes *XLOG * records into a transaction log which is a virtual log that consists of a 8-byte long file. A WAL segment is a 16 MB file by default and it is further internally divided into pages of 8192 bytes (8 KB).

- Writing of WAL data

Now, moving on to understanding the writing of the XLOG files, by issuing the following statement:
testdb=# INSERT INTO tbl VALUES ('A');
By invoking the above statement, the internal function exec_simple_query() is invoked. This function writes and flushes all XLOG files to the WAL segment from the WAL buffer.
WAL writer process

Writing operations are usually done using DML (Data Manipulation Language) but even non-DML operations are capable of performing writing operations in PostgreSQL. WAL writer works as a background process to check on the WAL buffer periodically and writes all the unwritten XLOG records into the segments containing the WAL.