WAL
Write-Ahead Log
16
Copyright
© Postgres Professional, 2016–2026
Authors: Egor Rogov, Pavel Luzanov, Ilya Bashtanov, Igor Gnatyuk
Translated by: Elena Sharafutdinova
Photo: Oleg Bartunov (Phu Monastery and Bhrikuti Peak, Nepal)
Use of Course Materials
Non-commercial use of course materials (presentations, demonstrations) is
allowed without restrictions. Commercial use is possible only with the written
permission of Postgres Professional. It is prohibited to make changes to the
course materials.
Feedback
Please send your feedback, comments and suggestions to:
edu@postgrespro.ru
Disclaimer
Postgres Professional assumes no responsibility for any damages and
losses, including loss of income, caused by direct or indirect, intentional or
accidental use of course materials. Postgres Professional company
specifically disclaims any warranties on course materials. Course materials
are provided “as is,” and Postgres Professional company has no obligations
to provide maintenance, support, updates, enhancements, or modifications.
2
Topics
Write-Ahead Log (WAL)
Logical and Physical Structure of the WAL
Write-Ahead Logging and Recovery
3
Write-Ahead Log
Primary function
to restore data consistency after a crash
Mechanism
when data is modified, the operation is also written to WAL
WAL record reaches the disk earlier than the modified data
crash recovery is achieved by replaying the lost operations using WAL
records
The fundamental purpose of the WAL is to restore data consistency after a
crash that results in the loss of RAM contents, including the buffer cache.
This ensures the Durability property (letter D in the ACID transaction
properties).
When data is modified, a WAL record is created simultaneously, containing
sufficient information to replay that operation. The WAL record must be
flushed to disk before the modified page, that is why it is named as Write-
Ahead Log.
After a crash, the system can read the WAL and replay the operations that
were already performed but whose results had not been written to disk by
that time.
4
WAL-logged Operations
Changes to any pages in the buffer cache
including table and index pages
except for unlogged and temporary tables
Transaction commits and aborts — CLOG buffers
File operations
creation and deletion of files
addition and removal of file pages
creation and deletion of directories
It is required to log all operations that can lead to incomplete write of
changes in case of a failure.
In particular, the following operations are WAL-logged:
●
Changes to pages in the buffer cache (typically table and index pages).
This is necessary since a modified page is not written to disk immediately.
●
Transaction commits and aborts. Similarly, the status change occurs in
the CLOG buffer and it is not written to disk immediately.
●
File operations (creating and deleting files and directories, e.g., when a
table is created). These operations must be synchronized with data
changes.
The following operations are not WAL-logged:
●
Operations on unlogged tables, as the name implies.
●
Operations on temporary tables, since the lifetime of such tables does not
exceed the lifetime of the session that created them.
5
sequence of records
record ID is a 64-bit LSN (Log Sequence Number)
special pg_lsn data type
Logical Structure
LSN
record
ID
0
header:
transaction ID,
resource manager,
checksum
data
Logically, we can describe WAL as a sequence of variable-length records.
Each record contains data about a particular operation, preceded by a
header. Among other things, the header includes:
●
Transaction ID for the record.
●
Resource manager, the system component responsible for this record
that knows how to interpret the data in the record. There are separate
managers for tables, various index types, transaction status, etc.
●
Checksum (CRC).
Starting with version 16, extensions can create their own resource managers
that use a custom format for their WAL records. This helps developers
create extensions that implement new table and index access methods.
To refer to a particular record, the pg_lsn data type is used. It represents a
64-bit offset in bytes from the start of the WAL to the record.
The start of each record is aligned to a machine word boundary,
which is one of the reasons for the binary incompatibility of WAL across
different platforms.
7
Physical Structure
wal_buffers
$PGDATA/pg_wal/
000000010000000100000001
000000010000000100000002
000000010000000100000003
000000010000000100000004
000000010000000100000005
000000010000000100000006
In shared memory
circular buffer cache
wal_buffers = –1
On disk
16 MB files (segments)
1/32 of shared_buffers
On disk, the WAL is stored as files (segments) in the PGDATA/pg_wal
directory. Each file is 16 MB by default. The segment size can be set during
cluster initialization.
WAL records are written to the current file. When it fills up, the system
moves on to the next one.
In shared memory, special buffers are allocated for the WAL. The
wal_buffers parameter specifies the size of the cache (the default value
implies automatic setting: 1/32 of the buffer cache is allocated, but no less
than 64 KB and no more than the size of one WAL segment).
The WAL cache is structured similarly to the buffer cache, but operates
mainly as a circular buffer: records are added to the head, while they are
flushed to disk from the tail.
9
Write-Ahead Logging
• • •
CLOG WAL
pg_current_wal_insert_lsn()
LSN
Let’s illustrate the concept of Write-Ahead Logging discussed earlier. The
slide shows three important areas of the shared memory:
●
Buffer cache (size set byshared_buffers)
●
WAL buffer cache described above (size set by wal_buffers)
●
Transaction status cache, also known as CLOG (size of 128 pages)
When a data page is modified in the buffer cache, a WAL record is created.
This record is placed in a WAL page, and a reference to the record (more
precisely, its LSN + its length, i.e., the LSN of the next record) is written to a
special LSN field in the data page header.
The current recording position can be obtained using the
pg_current_wal_insert_lsn() function.
10
Write-Ahead Logging
• • •
CLOG WAL
pg_current_wal_insert_lsn()
LSN
LSN
Let’s assume the transaction is committed. Another WAL record is
generated, the status bit is changed in a CLOG page, and a reference to this
record is placed in the LSN field of the modified page.
When the record is inserted, the pg_current_wal_insert_lsn pointer moves
forward.
Note that WAL records belonging to one transaction can be interleaved with
records from other transactions, which may belong to any database within
the cluster. The WAL is shared across the entire cluster.
11
Write-Ahead Logging
• • •
CLOG WAL
pg_current_wal_insert_lsn()
LSN
LSN
pg_current_wal_flush_lsn()
At some point (the specific point will be covered in the WAL Tuning lesson),
the WAL records that have not yet been written to disk must be flushed.
The pg_current_wal_flush_lsn() function shows the last record that has
already been written to disk.
12
Write-Ahead Logging
• • •
CLOG WAL
LSN
LSN
Modified data pages can only be written to disk after their corresponding
WAL records have been flushed. This sequence is controlled by tracking
the LSN of the last modification to the page and the value of
pg_current_wal_flush_lsn(). Meanwhile, the operation continues, and new
WAL records are constantly being generated. For the page to be written, it is
important that the WAL record with the LSN of its last modification must
already be on disk.
If a data page needs to be written to disk (for instance, during page eviction
from the buffer cache) but its corresponding WAL record has not yet been
flushed, the WAL buffers are forcibly flushed to disk first.
14
Recovery
Algorithm (simplified)
during server startup after a crash
(the cluster state in pg_control is not “shut down”):
1 For each WAL record:
1.1 Identify the page this record relates to
1.2 Apply the record if its LSN is greater than the LSN stored in the page
2 Overwrite unlogged tables with init files
The server crash is identified at the subsequent server start. If the startup
process spawned by the postmaster at the very beginning checks the
pg_control file and finds out a status other than “shut down”, automatic
recovery will be performed.
The startup process will read the WAL sequentially and apply the records to
pages, if necessary. The need can be determined by comparing LSN of the
page on disk with LSN of the WAL record. During recovery, pages are
changed in the buffer cache, as in normal operation. To do this, the
postmaster launches the necessary background processes.
WAL records are applied to files in a similar way: for example, if it is clear
from a record that the file must exist, but it does not, the file is created.
At the end of the process, all unlogged tables are overwritten using the forks
stored in the init files.
Starting with PostgreSQL 15, the system can prefetch WAL data during
recovery using the mechanism controlled by the recovery_prefetch
parameter. Its default value is try (prefetching is used if supported by the
OS). When prefetch is enabled, the wal_decode_buffer_size parameter
determines how far ahead the server will look into the WAL to identify the
required page numbers.
The algorithm described here is simplified. In particular, we have not
mentioned where to start reading WAL records from (this will be covered in
the Checkpoint lesson).
15
Takeaways
The use of buffers in RAM requires the use of WAL
The WAL contains information required to replay operations
after a crash and restore database consistency
The WAL is always written to disk before the corresponding
modified data pages are written
16
Practice
1. Create a table with a primary key and insert several rows into it.
How many bytes do the generated WAL records occupy?
2. What accounts for a relatively large number of them?
Examine the headers of these WAL records and verify your
assumptions.
3. Modify the rows you inserted into the table. Modify the rows
again, but do not commit the transaction. Simulate a crash by
terminating the postmaster process.
Start the server and verify that the committed changes are still
present, while the uncommitted transaction has been aborted.
Find the information about the crash recovery in the server log.
2. You can use the pg_waldump tool or the pg_walinspect extension.
3. Use the command
$ sudo kill -QUIT process-id
You can find the process ID in the first line of the postmaster.pid file
located in the server’s PGDATA directory.
