DEV Community

Haseeb Ashraf
Haseeb Ashraf

Posted on

2 1

An overview of Write Ahead Logging - WAL in PostgreSQL

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.

AWS Security LIVE!

Tune in for AWS Security LIVE!

Join AWS Security LIVE! for expert insights and actionable tips to protect your organization and keep security teams prepared.

Learn More

Top comments (1)

Collapse
 
mpfdev profile image
Matheus 🇧🇷

Great overview of WAL in PostgreSQL.

Thanks for sharing!

A Workflow Copilot. Tailored to You.

Pieces.app image

Our desktop app, with its intelligent copilot, streamlines coding by generating snippets, extracting code from screenshots, and accelerating problem-solving.

Read the docs

👋 Kindness is contagious

Explore a sea of insights with this enlightening post, highly esteemed within the nurturing DEV Community. Coders of all stripes are invited to participate and contribute to our shared knowledge.

Expressing gratitude with a simple "thank you" can make a big impact. Leave your thanks in the comments!

On DEV, exchanging ideas smooths our way and strengthens our community bonds. Found this useful? A quick note of thanks to the author can mean a lot.

Okay