9.6. WAL related processes

9.6.1. WAL Writer Process

The WAL writer is a background process that periodically checks the WAL buffer and writes all unwritten XLOG records to the WAL segments. This process helps to avoid bursts of XLOG record writing. If the WAL writer is not enabled, writing XLOG records could be bottlenecked when a large amount of data is committed at once.

The WAL writer is enabled by default and cannot be disabled. The check interval is set to the configuration parameter wal_writer_delay, which defaults to 200 milliseconds.

9.6.2. WAL Summarizer Process

Introduced in version 17 to support the incremental backups (described in Section 10.5). the WAL Summarizer process tracks changes to all database blocks (including relations and visibility maps) and writes these modifications to WAL summary files located in the pg_wal/summaries/ directory.

Note that the free-space map fork is not tracked because it is not properly WAL-logged.

9.6.2.1. Outline of how WAL Summarizer process works

The WAL Summarizer process operates as follows:

  1. During each checkpoint, it reads WAL segment files from the previous REDO point to the current REDO point.
  2. It tracks changes to all blocks of all relations (including visibility maps) using the WAL segment files.
  3. It writes the summary to WAL summary files located in the pg_wal/summaries/ directory.

In the context of the WAL Summarizer process, “previous REDO point” and “current REDO point” are referred to as “start_lsn” and “end_lsn,” respectively.

The summary file name format is as follows:

{Timeline}{start_lsn}{end_lsn}.summary

Here’s an example of summary files:

$ ls -1 data/pg_wal/summaries/
00000001000000000100002800000000010B1D30.summary
0000000100000000010B1D300000000001473DE0.summary
000000010000000001473DE00000000001473EE0.summary
000000010000000001473EE0000000000147A8A8.summary
00000001000000000147A8A8000000000147A9A8.summary

... snip ...

The pg_available_wal_summaries() function can show the wal summaries:

testdb=# SELECT tli, start_lsn, end_lsn FROM pg_available_wal_summaries() ORDER BY start_lsn;
 tli | start_lsn  |  end_lsn
-----+------------+------------
   1 | 0/1000028  | 0/10B1D30
   1 | 0/10B1D30  | 0/1473DE0
   1 | 0/1473DE0  | 0/1473EE0
   1 | 0/1473EE0  | 0/147A8A8
   1 | 0/147A8A8  | 0/147A9A8

... snip ...

Summary files are automatically removed after the configured wal_summary_keep_time (default 10 days) has passed since their creation.

9.6.2.2. Contents of a Summary File

To illustrate the contents of a summary file, we will create four tables, t1, t2, t3, and t4, each containing of four blocks:

testdb=# CREATE TABLE t1 (id int);
CREATE TABLE
testdb=# INSERT INTO t1 SELECT  GENERATE_SERIES(1, 800);
INSERT 0 800
testdb=# SELECT * FROM pg_freespace('t1');
 blkno | avail
-------+-------
     0 |     0
     1 |     0
     2 |     0
     3 |     0
(4 rows)

testdb=# CREATE TABLE t2 (id int);
CREATE TABLE
testdb=# INSERT INTO t2 SELECT  GENERATE_SERIES(1, 800);
INSERT 0 800
testdb=# CREATE TABLE t3 (id int);
CREATE TABLE
testdb=# INSERT INTO t3 SELECT  GENERATE_SERIES(1, 800);
INSERT 0 800
testdb=# CREATE TABLE t4 (id int);
CREATE TABLE
testdb=# INSERT INTO t4 SELECT  GENERATE_SERIES(1, 800);
INSERT 0 800
testdb=# CHECKPOINT;
CHECKPOINT

After a CHECKPOINT, we will perform the following operations:

  1. Update two rows in t1.
  2. Insert 150 rows into t2.
  3. Delete 300 rows from t3.
  4. Truncate all rows from t4.
  5. Create a new table t5 and insert 800 rows into it.
testdb=# -- [1] Update two rows to modify the blocks of t1
testdb=# UPDATE t1 SET id = id + 1000 WHERE id = 1 OR id = 200;
UPDATE 2
testdb=# -- [2] Insert 150 rows to modify the last block and add a new block
testdb=# INSERT INTO t2 SELECT GENERATE_SERIES(1, 150);
INSERT 0 150
testdb=# -- [3] Delete 500 rows to remove blocks
testdb=# DELETE FROM t3 WHERE id > 300;
DELETE 500
testdb=# -- [4] Truncate all blocks
testdb=# TRUNCATE t4;
TRUNCATE TABLE
testdb=# -- [5] Create new table
testdb=# CREATE TABLE t5 (id int);
CREATE TABLE
testdb=# INSERT INTO t5 SELECT  GENERATE_SERIES(1, 800);
INSERT 0 800
testdb=# CHECKPOINT;
CHECKPOINT

Following these operations, we will examine the summary of changes to all relation blocks generated by the WALsummarizer process, using the pg_wal_summary_contents() function.

The pg_wal_summary_contents(timeline, start_lsn, end_lsn) function shows all changed blocks from start_lsn to end_lsn. The output contains: filenode (OID), block number, fork number, is_limit_block (explained later), etc.

[1] Modified blocks

Table t1 has been updated for two rows.

testdb=# UPDATE t1 SET id = id + 1000 WHERE id = 1 OR id = 200;
UPDATE 2

Using the pg_wal_summary_contents() function, we can retrieve the summary data for table t1 as follows:

testdb=# SELECT p.relname, s.relforknumber, s.relblocknumber, s.is_limit_block
testdb-# 	FROM pg_wal_summary_contents(1, '0/1F4225F8', '0/1F476450') AS s, pg_class AS p
testdb-#  	WHERE s.relfilenode = p.oid AND p.relname = 't1';
 relname | relforknumber | relblocknumber | is_limit_block
---------+---------------+----------------+----------------
 t1      |             0 |              0 | f
 t1      |             0 |              3 | f
(2 rows)

The output shows that the 0th and 3rd blocks of table t1 have been modified due to the UPDATE command.

Figure 9.13 illustrates the modification of table t1, which reflects the summary data for t1.

Figure 9.13. The modification of table t1.
[2] Added blocks

Table t2 has 150 new rows added.

As a result, we can confirm that the 3rd block was changed and the new 4th block was added.

testdb=# SELECT p.relname, s.relforknumber, s.relblocknumber, s.is_limit_block
testdb-# 	FROM pg_wal_summary_contents(1, '0/1F4225F8', '0/1F476450') AS s, pg_class AS p
testdb-#  	WHERE s.relfilenode = p.oid AND p.relname = 't2';
 relname | relforknumber | relblocknumber | is_limit_block
---------+---------------+----------------+----------------
 t2      |             0 |              3 | f
 t2      |             0 |              4 | f
(2 rows)

testdb=# select * from pg_freespace('t2');
 blkno | avail
-------+-------
     0 |     0
     1 |     0
     2 |     0
     3 |     0
     4 |     0
(5 rows)

Figure 9.14 illustrates the modification of table t2, which reflects the summary data for t2.

Figure 9.14. The modification of table t2.
[3] Removed blocks

Table t3 has deleted 500 rows.

testdb=# SELECT p.relname, s.relforknumber, s.relblocknumber, s.is_limit_block
testdb-# 	FROM pg_wal_summary_contents(1, '0/1F4225F8', '0/1F476450') AS s, pg_class AS p
testdb-#  	WHERE s.relfilenode = p.oid AND p.relname = 't3';
 relname | relforknumber | relblocknumber | is_limit_block
---------+---------------+----------------+----------------
 t3      |             0 |              2 | t
 t3      |             0 |              1 | f
 t3      |             0 |              0 | f
 t3      |             2 |              2 | t
 t3      |             2 |              0 | f
(5 rows)

testdb=# select * from pg_freespace('t3');
 blkno | avail
-------+-------
     0 |     0
     1 |  5472
(2 rows)

According to the output, the 2nd and 3rd blocks were removed, and the remaining blocks, i.e., 0th and 1st blocks, were modified. Moreover, the 2nd block of t3’s visibility map was removed, and the 0th block was modified.

In this way, if blocks after a certain block number were deleted, the block on the boundary of the deleted blocks is also recorded, and is_limit_block is set to true.

In this case, the 2nd block of table t3 and the 2nd block of table t3’s visibility space map are both set to is_limit_block = true, because both the 2nd block and the subsequent blocks were deleted.

Figure 9.15 illustrates the modification of table t3, which reflects the summary data for t3. As shown in Figure 9.15, the limit block acts as a virtual termination block.

Figure 9.15. The modification of table t3.
[4] Truncated all blocks

Since the truncated table t4 and visibility-map files were removed, the block number for all related blocks was set to 0, and is_limit_block was set to true.

testdb=# SELECT p.relname, s.relforknumber, s.relblocknumber, s.is_limit_block
testdb-# 	FROM pg_wal_summary_contents(1, '0/1F4225F8', '0/1F476450') AS s, pg_class AS p
testdb-#  	WHERE s.relfilenode = p.oid AND p.relname = 't4';
 relname | relforknumber | relblocknumber | is_limit_block
---------+---------------+----------------+----------------
 t4      |             0 |              0 | t
 t4      |             2 |              0 | t
 t4      |             3 |              0 | t  <= fork_num 3 is a special number,
                                                  so the explanation is omitted here.
(3 rows)

testdb=# select * from pg_freespace('t4');
 blkno | avail
-------+-------
(0 rows)

Figure 9.16 illustrates the modification of table t4, which reflects the summary data for t4.

Figure 9.16. The modification of table t4.

This same result returns when DROP TABLE is executed.

[5] Created new table

Not only when data blocks are removed, but also when a new table is created, the block number of the created relation is set to 0, and is_limit_block is set to true.

In this case, we created table t5 and inserted rows into it. Therefore, the summary file contains two rows that are both for the 0th block of table t5, but is_limit_block is true and false, respectively.

testdb=# SELECT p.relname, s.relforknumber, s.relblocknumber, s.is_limit_block
testdb-# 	FROM pg_wal_summary_contents(1, '0/1F4225F8', '0/1F476450') AS s, pg_class AS p
testdb-#  	WHERE s.relfilenode = p.oid AND p.relname = 't5';
 relname | relforknumber | relblocknumber | is_limit_block
---------+---------------+----------------+----------------
 t5      |             0 |              0 | t
 t5      |             0 |              0 | f
 t5      |             0 |              1 | f
 t5      |             0 |              2 | f
 t5      |             0 |              3 | f
 t5      |             2 |              0 | f
(6 rows)

Figure 9.17 illustrates the modification of table t5, which reflects the summary data for t5.

Figure 9.17. The modification of table t5.