Contents
Write-Ahead Logging (WAL)
When processing data, PostgreSQL first logs transactions to the Write-Ahead Log (WAL, also referred to as the transaction log) before writing the changes to the affected data files themselves. This helps ensure data integrity as in the event of a server crash PostgreSQL can replay any changes which have not yet been applied to the data files from the WAL log. It also improves I/O performance, as writes to the WAL log are performed sequentially, making the cost of syncing the WAL log to disk less than syncing individual data pages.
WAL files can also be used to apply the same changes to another database ("log shipping") or as part of a backup solution together with a base backup.
At certain intervals - checkpoints - transactions recorded in the WAL log are written to their respective data files and the checkpoint is recorded in the WAL log. The contents of the WAL log before the checkpoint are no longer required and can be removed/recycled (or archived if required for replication etc.).
WAL files are stored in the pg_wal (PostgreSQL 9.6 and earlier: pg_xlog) subdirectory of the server's data directory.
WAL functionality was introduced in PostgreSQL 7.1 (release notes).
WAL filesize
Default WAL filesize is 16MB. This value is defined in src/include/pg_config_manual.h as:
/*
* This is default value for wal_segment_size to be used at initdb when run
* without --walsegsize option. Must be a valid segment size.
*/
#define DEFAULT_XLOG_SEG_SIZE (16*1024*1024)
As the code comment indicates, this value can be overridden by initdb with the --wal-segsize option (PostgreSQL 11 and later only).
This value is reported by the read-only configuration parameter wal_segment_size.
Source code
Header file src/include/access/xlog_internal.h contains a number of useful definitions and macros for manipulating WAL files, including:
- XLOG_FNAME_LEN
References
- PostgreSQL documentation: Introduction to Write-Ahead Logging
- PostgreSQL documentation: wal_segment_size