9.9. WAL Segment Files Management

PostgreSQL writes XLOG records to one of the WAL segment files stored in the pg_wal subdirectory (in versions 9.6 or earlier, pg_xlog subdirectory). A new WAL segment file is switched in if the old one has been filled up. The number of WAL files varies depending on several configuration parameters, as well as server activity. In addition, the management policy for WAL segment files has been improved in version 9.5.

The following subsections describe how WAL segment files are switched and managed.

9.9.1. WAL Segment Switches

WAL segment switches occur when one of the following events happens:

  1. WAL segment is filled up.

  2. The function pg_switch_xlog is called.

  3. The archive_mode parameter is enabled and the archive_timeout parameter has expired.

When a WAL segment file is switched, it is usually recycled (renamed and reused) for future use. However, it may be removed later if it is not needed.

9.9.2. WAL Segment Management

Whenever a checkpoint starts, PostgreSQL estimates and prepares the number of WAL segment files that will be needed for the next checkpoint cycle. This estimate is based on the number of WAL segment files that were consumed in previous checkpoint cycles.

The number of WAL segment files is counted from the segment that contains the prior REDO point, and the value must be between the min_wal_size parameter (which default to 80 MB, or 5 files) and the max_wal_size parameter (which default to 1 GB, or 64 files).

If a checkpoint starts, PostgreSQL will keep or recycle the necessary WAL segment files, and remove any unnecessary files.

A specific example is shown in Figure 9.22. Assuming that there are six WAL segment files before a checkpoint starts, WAL_3 contains the prior REDO point (in versions 10 or earlier; in versions 11 or later, the REDO point), and PostgreSQL estimates that five files will be needed. In this case, WAL_1 will be renamed as WAL_7 for recycling and WAL_2 will be removed.

Info

The files older than the one that contains the prior REDO point can be removed, because, as is clear from the recovery mechanism described in Section 9.8, they would never be used.

Figure 9.22. Recycling and removing WAL segment files at a checkpoint.

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

Figure 9.23. Creating WAL segment file.

The number of WAL segment files adapts to the server activity. If the amount of WAL data writing has been constantly increasing, the estimated number of WAL segment files, as well as the total size of the WAL segment files, will gradually increase. In the opposite case (i.e., the amount of WAL data writing has decreased), these will decrease.

If the total size of the WAL segment files exceeds the max_wal_size parameter, a checkpoint will be started. Figure 9.24 illustrates this situation. By checkpointing, a new REDO point will be created and the prior REDO point will be discarded; then, unnecessary old WAL segment files will be recycled. In this way, PostgreSQL will always keep just the WAL segment files that are needed for database recovery.

Figure 9.24. Checkpointing and recycling WAL segment files.

The wal_keep_size (or wal_keep_segments in versions 12 or earlier) and the replication slot feature also affect the number of WAL segment files.