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 avoid bursts of XLOG writing. If the WAL writer is not enabled, XLOG writing could become a bottleneck when a large amount of data is committed at once.

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

9.6.2. WAL Summarizer Process

Introduced in version 17 (2024) to support incremental backups (described in Section 10.5), the WAL summarizer process tracks changes to all database blocks, including relations and visibility maps.

It writes these modifications to WAL summary files in the $PGDATA/pg_wal/summaries/ directory.

The summarize_wal configuration parameter enables this process; it is disabled by default.

Note that the WAL summarizer does not track the free-space map fork 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, the process reads WAL segment files from the previous REDO point to the current REDO point.
  2. The process tracks changes to all blocks of all relations (including visibility maps) using the WAL segment files.
  3. The process writes the results to WAL summary files in the “pg_wal/summaries/” directory.

In this context, the “previous REDO point” and “current REDO point” are referred to as start_lsn and end_lsn, respectively.

The summary file name pattern is as follows:

  • Summary File Pattern: {Timeline}{start_lsn}{end_lsn}.summary

The following is an example of summary files:

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

... snip ...

The pg_available_wal_summaries() function displays 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 ...

PostgreSQL removes summary files automatically after the period set by 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, the following example creates four tables (t1, t2, t3, and t4), each consisting 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, the following operations are performed:

  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

The pg_wal_summary_contents(timeline, start_lsn, end_lsn) function shows all changed blocks between ‘start_lsn’ and ’end_lsn’. The output includes the filenode (OID), block number, fork number, and the ‘is_limit_block’ flag.

[1] Modified blocks

Two rows in table t1 are updated.

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

The pg_wal_summary_contents() function retrieves the summary data:

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 indicates that the 0th and 3rd blocks of table t1 were modified.

Figure 9.14 illustrates these changes based on the summary data.

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

Table t2 has 150 new rows added.

The 3rd block was modified, and a 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.15 illustrates these additions.

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

When blocks are deleted after a certain block number, the process records the boundary block and sets is_limit_block to true. This limit block acts as a virtual termination block.

In table t3, 500 rows are deleted.

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)

In this case, the 2nd block of table t3 is marked as the limit block by setting its is_limit_block to true; the corresponding visibility map (fork 2) is updated in the same manner.

Consequently, the output shows that the 2nd and 3rd blocks were removed, the 2nd block of t3’s visibility map was removed, and the remaining 0th and 1st blocks were modified.

Figure 9.16 illustrates these modifications.

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

When table t4 is truncated, the block number for all related blocks is set to 0, and is_limit_block is 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.17 illustrates the truncation.

Figure 9.17. The modification of table t4.

The same result occurs when a DROP TABLE command is executed.

[5] Created new table

When a new table is created, the block number is set to 0, and is_limit_block is set to true.

For table t5, the summary initially contains the 0th block with is_limit_block set to true. Subsequent insertions then create blocks 0 through 3, with is_limit_block set to false.

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.18 illustrates the creation and population of table t5.

Figure 9.18. The modification of table t5.