This post is a part of my document.

In version 9.5, management policy for WAL segment files has improved. PostgreSQL 9.5 always holds the appropriate number of WAL files in pg_xlog depending on server activity.

WAL segment management

PostgreSQL writes XLOG records to one of the WAL segment files held in the pg_xlog subdirectory, and the number of the WAL files will vary depending on several configuration parameters, as well as server activity.

Version 9.4 or earlier

The management policy in version 9.4 or earlier is the following:

The number of WAL files is normally more than “(2 + checkpoint_completion_target) * checkpoint_segments + 1” or “checkpoint_segments + wal_keep_segments + 1” files, and it could temporarily become up to “3 * checkpoint_segments + 1” files depending on the server activity.

Umm… It is complicated. Simply put, the number of files varies a little. Thus, the parameter checkpoint_segments is set at a small number, checkpoint occurs frequently, which causes a decrease in performance, whereas if set at a large number, the huge disk space is always required for the WAL files, some of which is not always necessary.

Version 9.5

In contrast to earlier versions, in version 9.5, the number of WAL files considerably varies with the server activity. Whenever a checkpoint starts, PostgreSQL 9.5 estimates and prepares the number of WAL files required for the next checkpoint cycle. Such estimate is made with regards to the numbers of files consumed in previous checkpoint cycles. They are counted from the segment that contains the prior REDO point, and the value is to be between min_wal_size (by default, 80 MB, i.e. 5 files) and max_wal_size (1 GB, i.e. 64 files). When a checkpoint starts, necessary files will be held or recycled, while the unnecessary ones removed. (In addition, checkpoint_segments has obsoleted since it is unnecessary.)

Detailed explanation

A specific example is shown in Figure 1. Assuming that there are six files before checkpoint starts, WAL_3 contains the prior REDO point, and PostgreSQL estimates that five files are needed. In this case, WAL_1 will be renamed as WAL_7 for recycling and WAL_2 will be removed.

Figure 1: Recycling and removing WAL segment files at a checkpoint

Note that the files older than the one which contains the prior REDO point can be removed, because, as is clear from the recovery mechanism described in my document, they would never be used. Thus, PostgreSQL 9.5 always holds the necessary and sufficient WAL segment files. (On the other hand, earlier versions often hold unnecessary files.)

If more files are required due to a spike in WAL activity, new files will be created while the total size of WAL files is less than max_wal_size. For example, in Figure 2, if WAL_7 has been filled up, WAL_8 is newly created.

Figure 2: Creating WAL segment file

The number of WAL files varies adaptively. If the amount of WAL data writing has increased constantly, the estimated number of the WAL segment files as well as the total size of WAL files also increases gradually. In the opposite case, these will decrease.

When the total size of the WAL files exceeds max_wal_size, a checkpoint will be started. Figure 3 illustrates this situation. By checkpointing, a new REDO point will be created and the last REDO point will be the prior one; and then unnecessary old files will be recycled. In this way, (I repeat,) PostgreSQL will always hold just the WAL segment files needed for database recovery.

Figure 3: Checkpointing and recycling WAL segment files

Additionally, the configuration parameter wal_keep_segments and the replication slot feature also affect the number of WAL segment files.

See also my document “The Internals of PostgreSQL” if you like this post.