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.
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.
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.
The parameter wal_keep_segments
specifies 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.
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.
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