9.2. Transaction Log and WAL Segment Files
Logically, PostgreSQL writes XLOG records into a virtual file that is 8 bytes long (16 ExaBytes).
Since a transaction log capacity is effectively unlimited and so can be said that 8-bytes of address space is vast enough, it is impossible for us to handle a file with a capacity of 8 bytes. Therefore, a transaction log in PostgreSQL is divided into files of 16 megabytes, by default, each of which is known as a WAL segment. See Fig. 9.6.
In versions 11 or later, the size of WAL segment file can be configured using –wal-segsize option when PostgreSQL cluster is created by initdb command.
Fig. 9.6. Transaction log and WAL segment files
The WAL segment filename is in hexadecimal 24-digit number and the naming rule is as follows:
$$
\begin{align}
\text{WAL segment file name} = \text{timelineId} + (\text{uint32}) \frac{\text{LSN}-1}{16\text{M}*256}
+ ( \text{uint32})\left(\frac{\text{LSN}-1}{16\text{M}}\right) % 256
\end{align}
$$
PostgreSQL’s WAL contains the concept of timelineId (4-byte unsigned integer), which is for Point-in-Time Recovery (PITR) described in Chapter 10. However, the timelineId is fixed to 0x00000001 in this chapter because this concept is not required in the following descriptions.
The first WAL segment file is 000000010000000000000001. If the first one has been filled up with the writing of XLOG records, the second one 000000010000000000000002 would be provided. Files are used in ascending order in succession. After 0000000100000000000000FF has been filled up, the next one 000000010000000100000000 will be provided. In this way, whenever the last 2-digit carries over, the middle 8-digit number increases one.
Similarly, after 0000000100000001000000FF has been filled up, 000000010000000200000000 will be provided, and so on.
Using the built-in function pg_xlogfile_name (versions 9.6 or earlier) or pg_walfile_name (version 10 or later), we can find the WAL segment file name that contains the specified LSN. An example is shown below:
testdb=# SELECT pg_xlogfile_name('1/00002D3E'); # In versions 10 or later, "SELECT pg_walfile_name('1/00002D3E');"
pg_xlogfile_name
--------------------------
000000010000000100000000
(1 row)