WAL-related parameters to control pg_wal/pg_xlog size

Luciano Botti
Luciano Botti
  • Updated

There are several WAL-related configuration parameters that affect database performance and the amount of WAL files generated and held back by the database.

This article explains their use and the effects they may produce on the wal directory.

WAL files

Postgres stored a number of WAL files in the pg_wal/pg_xlog directory, with a size of 16 MB each as the default value. Normally when old wal files are no longer needed, they are removed or recycled, which means they are renamed to become future segments in a numbered sequence.

The total number of WAL segment files in pg_wal/pg_xlog directory depends on the configuration parameters max_wal_size, min_wal_size, wal_keep_segments and also by the amount of WAL generated in previous checkpoint cycles.

max_wal_size

The max_wal_size lets you set the maximum size in Bytes of the WAL directory, but most importantly it's used to trigger an automatic checkpoint by reaching that limit of WAL files generated. Actually, the automatic checkpoint will start at max_wal_size or checkpoint_timeout whichever comes first.

wal_keep_segments

The parameter wal_keep_segmentsspecifies the minimum number of WAL files (in the past) and behind the current WAL file position that postgres will retain.

Postgres will try to keep wal_keep_segments+1 WAL files in the past all the time independently of the max_wal_size parameter, and it could even exceed the wal_keep_segments and max_wal_size settings under special circumstances. For example:

  • Postgres server is under heavy load.
  • A failing archive_command, if WAL archiving is used.
  • Inactive replication slots holding WAL files.

min_wal_size

This setting lets you fix a minimum number of WAL files for future use in the pg_wal directory. In this case the min_wal_size parameter could be used so postgres knows how many WAL files for future usage there should be. Basically this controls if postgres should recycle or delete a WAL file on a checkpoint, recycling the WAL files if there are less than min_wal_size for future segments, or removing them in other case.

Note that min_wal_size specifies the total amount in Bytes that postgres will reserve in pg_wal (or pg_xlog before PostgreSQL 10) directory for future WAL files.

Configuration example

Take a look at the following configuration example:

max_wal_size = 2GB min_wal_size = 500MB wal_keep_segments = 256

With the above configuration and the default WAL file size of 16MB, postgres will always try to keep 84 future WAL files and 256 WAL files in the past. The max_wal_size has no effect here in the number of wal files in the pg_wal/pg_xlog directory. As with the wal_keep_segments you already have 4GB, plus the 500MB from min_wal_size, which is larger than the 2GB set there.

Although max_wal_size value will still be used to start an automatic checkpoint when reaching nearly 2GB.

Related to

Was this article helpful?

0 out of 0 found this helpful