Postgres WAL

Write-Ahead Log (WAL)

Sep 11, 2021 | - views

Write-Ahead Logging (WAL) is a standard method for ensuring data integrity. A detailed description can be found in most (if not all) books about transaction processing. Briefly, WAL's central concept is that changes to data files (where tables and indexes reside) must be written only after those changes have been logged, that is, after log records describing the changes have been flushed to permanent storage. If we follow this procedure, we do not need to flush data pages to disk on every transaction commit, because we know that in the event of a crash we will be able to recover the database using the log: any changes that have not been applied to the data pages can be redone from the log records. (This is roll-forward recovery, also known as REDO.)

Using WAL results in a significantly reduced number of disk writes, because only the log file needs to be flushed to disk to guarantee that a transaction is committed, rather than every data file changed by the transaction. The log file is written sequentially, and so the cost of syncing the log is much less than the cost of flushing the data pages. This is especially true for servers handling many small transactions touching different parts of the data store. Furthermore, when the server is processing many small concurrent transactions, one fsync of the log file may suffice to commit many transactions.

With most installations and packages, 16 MB is the size of the wal segments. Unless your transaction rate is through the roof, 16 MB size is good enough. You can change this size by adjusting the –with-wal-segsize configure option.

So, as long as you keep making changes to the data in your database, WAL files will keep building up in your pg_wal directory. (pg_wal directory is used to store WAL files in PostgreSQL 10. In the previous versions, the directory was called pg_xlog). You can, of course, control the number of these files you want to store by using wal_keep_segments. Or you can control the total size of WAL files by using max_wal_size.

As new records are written, they are appended to WAL logs. Its position is defined by a Log Sequence Number. The Log Sequence Number (LSN) is a unique identifier in the transaction log. It represents a position in the WAL stream. That is, as records are added to the Postgres WAL log, their insert positions are described by the Log Sequence Number. pg_lsn is the datatype in which a Log Sequence Number is returned.

WAL Configuration

Performance of Postgres WAL

PostgreSQL WAL files are written one by one during the regular work of server. The functionality of HDD also works conveniently, but this kind of workload is different from the data accessing load. It is always recommended to store Postgres WAL files on a different physical disk mounted to the file system of your server.

Benefits of Postgre WAL