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
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.
Shared Buffers. Inside the postgresql.conf file, there is a parameter called shared_buffers. They
are called ‘shared’ buffers because all of the background servers can access them. This parameter
determines the amount of memory allocated to PostgreSQL for caching data. Dealing with memory is faster
than reading from or writing to the files and hence it improves performance. A back-end process requests
the buffer manager for a page and the buffer manager either loads the required page from persistent
storage or returns it from the buffer pool if it is available. Now the back-end process can access the
Dirty Pages. When data is modified in memory, the page in which this data is stored is called a
‘dirty page’, as long as the modifications are not written to disk. So if there is a page that is
different in the shared buffer and the disk, it is called a dirty page. The buffer manager flushes the
dirty pages to storage with the assistance of two subsystems called – checkpointer and background
Checkpointer. As the name suggests, checkpointer creates checkpoints in the Write-Ahead Log.
These checkpoints are used for recovery. A checkpoint indicates that all the information prior to the
checkpoint has been updated. So at every checkpoint, dirty pages are flushed to disk. How often a
checkpoint is begun depends on checkpoint_segments and checkpoint_timeout.
The integer, checkpoint_segments indicates the maximum number of log segments between two checkpoints.
The default value is 3 segments, where each segment is usually 16 MB. This value can be adjusted in the
On the other hand, checkpoint_timeout is an integer that indicates the maximum time between two
checkpoints. The default time is 5 minutes/ 300 seconds. This value can also be adjusted in the
Background Writer. The background writer is a subsystem that assists the checkpointer. What it
does is, move some of the modified data to the disk offloading the checkpointer. You should however be
careful before tuning it as it increases the I/O load, especially in cases where there is some page that
is being dirtied repeatedly. In such a case the background writer would write it repeatedly within a
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
- Synchronous Commit: With this commit, you will not be able to continue our work until all the WAL files
are stored on the disk. With synchronous commit, you can get 900 transactions per second.
- Asynchronous Commit: It allows faster completion of the transaction at the cost of data loss. With this
commit, it returns success as soon as the transaction is completed logically, even before the WAL
records it, they make their way to disk. You get 1500 transactions per second with asynchronous commit
Benefits of Postgre WAL
- As only log files are flushed to disk during transnational commit, it reduces the number of disk
- The cost of syncing your log file is less as the log files are written sequentially.
- It adds data page consistency .
- Postgres WAL offers on-line backup and point-in-time recovery.