This blog aims to assist you in understanding the intital concepts of Chapter:9 [Write Ahead Logging (WAL)] from the book The Internals of PostgreSQL.
Note: Ensure that you have a thorough understanding of Chapter 8 Part-1, Chapter 8 Part-2, Chapter 8 Part-3 and basics of PostreSQL before we proceed to Chapter 9, as it forms the foundation for our exploration.
So, Let's Start:
Introduction to Chapter
Transaction log is an essential part of database, because all of the database management system is required not to lose any data even when a system failure occurs.
It is a history log of all changes and actions in a database system so as to ensure that no data has been lost due to failures, such as a power failure, or some other server failure that causes the server crash.
Log contains sufficient information about each transaction executed already, the database server should be able to recover the database cluster by replaying changes and actions in the transaction log in case of the server crash.
In PostgreSQL, WAL is an acronym of Write Ahead Log. The term is used as synonym of transaction log, and also used to refer to an implemented mechanism related to writing action to a transaction log (WAL).
Overview of Chapter
Insertion Operations without WAL
To provide efficient access to the relation's pages, every DBMS implements shared buffer pool.
Assume that we insert some data tuples into TABLE_A on PostgreSQL which does not implement the WAL feature.
Insertion operations without WAL in PostgreSQL is depicted in figure below:
(1) Issuing the first INSERT statement, PostgreSQL loads the TABLE_A's page from a database cluster into the in-memory shared buffer pool, and inserts a tuple into the page.
(2) Issuing the second INSERT statement, PostgreSQL inserts a new tuple into the page on the buffer pool. This page has not been written into the storage yet.
(3) If the operating system or PostgreSQL server should fail for any reasons such as a power failure, all of the inserted data would be lost.
Database without WAL is vulnerable to the system failures.
Insertion Operations and Database Recovery
PostgreSQL writes all modifications as history data into a persistent storage, to prepare for failures.
The history data are known as XLOG record(s) or WAL data.
XLOG records are written into the in-memory WAL buffer by change operations such as insertion, deletion, or commit action.
They are immediately written into a WAL segment file on the storage when a transaction commits/aborts.
LSN (Log Sequence Number) of XLOG record represents the location where its record is written on the transaction log.
LSN of record is used as the unique id of XLOG record.
PostgreSQL start to recover from the REDO point; that is, the location to write the XLOG record at the moment when the latest checkpoint is started.
The database recovery processing is strongly linked to the checkpoint processing and both of these processing are inseparable.
Insertion operations with WAL in PostgreSQL is depicted in figure below:
(1) A checkpointer, a background process, periodically performs checkpointing. Whenever the checkpointer starts, it writes a 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 TABLE_A'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_A's LSN from LSN_0 to LSN_1.
In this example, this XLOG record is a pair of a header-data and the tuple entire.
(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_A'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).
(6) Imagine when the operating system failure should occur. Even though all of data on the shared buffer pool are lost, all modifications of the page have been written into the WAL segment files as history data.
PostgreSQL will automatically enter into the recovery-mode by restarting. PostgreSQL sequentially will read and replay XLOG records within the appropriate WAL segment files from the REDO point.
Database recovery using WAL in PostgreSQL is depicted in figure below:
(1) PostgreSQL reads the XLOG record of the first INSERT statement from the appropriate WAL segment file, loads the TABLE_A's page from the database cluster into the shared buffer pool.
(2) Before trying to replay the XLOG record, PostgreSQL shall compare the XLOG record's LSN with the corresponding page's LSN.
The rules of the replaying XLOG records are shown below:
If the XLOG record's LSN is larger than the page's LSN, the data-portion of the XLOG record is to be inserted into the page and the page's LSN is updated to the XLOG record's LSN.
If the XLOG record’s LSN is smaller, there is nothing to do other than to read next WAL data.
For Example, the XLOG record is replayed since the XLOG record's LSN (LSN_1) is larger than the TABLE_A's LSN (LSN_0); then, TABLE_A's LSN is updated from LSN_0 to LSN_1.
(3) PostgreSQL replays the remaining XLOG record(s) in the same way.
PostgreSQL does not support UNDO log.
I hope, this blog has helped you in understanding the initial concepts of Write Ahead Logging (WAL) in PostreSQL.
Check out summary of Chapter : 9 Part-2
If you want to understand PostgreSQL In-Depth.
Top comments (0)