11 WAL Modes
PostgreSQL 14 · Translated from Russian by Liudmila Mantrova · Chinese translation by Xiong Cancan
11.1 Performance
While the server is running normally, WAL files are being constantly written to disk. However, these writes are sequential: there is almost no random access, so even HDDs can cope with this task. Since this type of load is very different from a typical data file access, it may be worth setting up a separate physical storage for WAL files and replacing the PGDATA/pg_wal catalog by a symbolic link to a directory in a mounted file system.
There are a couple of situations when WAL files have to be both written and read. The first one is the obvious case of crash recovery; the second one is stream replication. The walsender1 process reads WAL entries directly from files.2 So if a replica does not receive WAL entries while the required pages are still in the OS buffers of the primary server, the data has to be read from disk. But the access will still be sequential rather than random.
WAL entries can be written in one of the following modes:
The synchronous mode forbids any further operations until a transaction commit saves all the related WAL entries to disk.
The asynchronous mode implies instant transaction commits, with WAL entries being written to disk later in the background.
The current mode is defined by the synchronous_commit parameter. (default: on)
Synchronous mode. To reliably register the fact of a commit, it is not enough to simply pass WAL entries to the operating system; you have to make sure that disk synchronization has completed successfully. Since synchronization implies actual I/O operations (which are quite slow), it is beneficial to perform it as seldom as possible.
For this purpose, the backend that completes the transaction and writes WAL entries to disk can make a small pause as defined by the commit_delay parameter. However, it will only happen if there are at least commit_siblings active transactions in the system:3 during this pause, some of them may finish, and the server will manage to synchronize all the WAL entries in one go. It is a lot like holding doors of an elevator for someone to rush in. (default: 0s) (default: 5)
By default, there is no pause. It makes sense to modify the commit_delay parameter only for systems that perform a lot of short OLTP transactions.
After a potential pause, the process that completes the transaction flushes all the accumulated WAL entries to disk and performs synchronization (it is important to save the commit entry and all the previous entries related to this transaction; the rest is written just because it does not increase the cost).
From this time on, the ACID’s durability requirement is guaranteed—the transaction is considered to be reliably committed.4 That’s why the synchronous mode is the default one.
The downside of the synchronous commit is longer latencies (the COMMIT command does not return control until the end of synchronization) and lower system throughput, especially for OLTP loads.
Asynchronous mode. You have to turn off the synchronous_commit parameter to enable asynchronous commits.5
In the asynchronous mode, WAL entries are written to disk by the walwriter6 process, which alternates between work and sleep. The duration of pauses is defined by the wal_writer_delay value. (default: 200ms)
Waking up from a pause, the process checks the cache for new completely filled WAL pages. If any such pages have appeared, the process writes them to disk, skipping the current page. Otherwise, it writes the current half-empty page since it has woken up anyway.7
The purpose of this algorithm is to avoid flushing the same page several times, which brings noticeable performance gains for workloads with intensive data changes.
Although WAL cache is used as a ring buffer, walwriter stops when it reaches the last page of the cache; after a pause, the next writing cycle starts from the first page. So in the worst case walwriter needs three runs to get to a particular WAL entry: first, it will write all full pages located at the end of the cache, then it will get back to the beginning, and finally, it will handle the underfilled page containing the entry. But in most cases it takes one or two cycles.
Synchronization is performed each time the wal_writer_flush_after amount of data is written, and once again at the end of the writing cycle. (default: 1MB)
Asynchronous commits are faster than synchronous ones since they do not have to wait for physical writes to disk. But reliability suffers: you can lose the data committed within the 3 × wal_writer_delay timeframe before a failure (which is 0.6 seconds by default).
In the real world, these two modes complement each other. In the synchronous mode, WAL entries related to a long transaction can still be written asynchronously to free WAL buffers. And vice versa, a WAL entry related to a page that is about to be evicted from the buffer cache will be immediately flushed to disk even in the asynchronous mode— otherwise, it is impossible to continue operation.
In most cases, a hard choice between performance and durability has to be made by the system designer.
The synchronous_commit parameter can also be set for particular transactions. If it is possible to classify all transactions at the application level as either absolutely critical (such as handling financial data) or less important, you can boost performance while risking to lose only non-critical transactions.
To get some idea of potential performance gains of the asynchronous commit, let’s compare latency and throughput in the two modes using a pgbench test.8
First, initialize the required tables:
Start a 30-second test in the synchronous mode:
And now run the same test in the asynchronous mode:
In the asynchronous mode, this simple benchmark shows a significantly lower latency and higher throughput (TPS). Naturally, each particular system will have its own figures depending on the current load, but it is clear that the impact on short OLTP transactions can be quite tangible.
Let’s restore the default settings:
11.2 Fault Tolerance
It is self-evident that write-ahead logging must guarantee crash recovery under any circumstances (unless the persistent storage itself is broken). There are many factors that can affect data consistency, but I will cover only the most important ones: caching, data corruption, and non-atomic writes.9
Caching
Before reaching a non-volatile storage (such as a hard disk), data can pass through various caches.
A disk write simply instructs the operating system to place the data into its cache (which is also prone to crashes, just like any other part of RAM). The actual writing is performed asynchronously, as defined by the settings of the I/O scheduler of the operating system.
Once the scheduler decides to flush the accumulated data, this data is moved to the cache of a storage device (like an HDD). Storage devices can also defer writing, for example, to group of adjacent pages together. A RAID controller adds one more caching level between the disk and the operating system.
Unless special measures are taken, the moment when the data is reliably stored on disk remains unknown. It is usually not so important because we have the WAL, but WAL entries themselves must be reliably saved on disk right away.10 It is equally true for the asynchronous mode—otherwise, it is impossible to guarantee that WAL entries get do disk ahead of the modified data.
The checkpointer process must also save the data in a reliable way, ensuring that dirty pages make it to disk from the OS cache. Besides, it has to synchronize all the file operations that have been performed by other processes (such as page writes or file deletions): when the checkpoint completes, the results of all these actions must be already saved on disk.11
There are also some other situations that demand fail-safe writing, such as executing unlogged operations at the minimal WAL level.
Operating systems provide various means to guarantee immediate writing of data into a non-volatile storage. All of them boil down to the following two main approaches: either a separate synchronization command is called after writing (such as fsync or fdatasync), or the requirement to perform synchronization (or even direct writing that bypasses OS cache) is specified when the file is being opened or written into.
The pg_test_fsync utility can help you determine the best way to synchronize the WAL depending on the particular OS and file system; the preferred method can be specified in the wal_sync_method parameter. For other operations, an appropriate synchronization method is selected automatically and cannot be configured.12
A subtle aspect here is that in each particular case the most suitable method depends on the hardware. For example, if you use a controller with a backup battery, you can take advantage of its cache, as the battery will protect the data in case of a power outage.
You should keep in mind that the asynchronous commit and lack of synchronization are two totally different stories. Turning off synchronization (by the fsync parameter) boosts system performance, yet any failure will lead to fatal data loss. The asynchronous mode guarantees crash recovery up to a consistent state, but some of the latest data updates may be missing. (default: on)
Data Corruption
Technical equipment is imperfect, and data can get damaged both in memory and on disk, or while it is being transferred via interface cables. Such errors are usually handled at the hardware level, yet some can escape.
To catch issues in good time, PostgreSQL always protects WAL entries by checksums.
Checksums can be calculated for data pages as well.13 It is done either during cluster initialization or by running the pg_checksums14 utility when the server is stopped.15 (v. 12)
In production systems, checksums must always be enabled, despite some (minor) calculation and verification overhead. It raises the chance of timely corruption discovery, even though some corner cases still remain:
Checksum verification is performed only when the page is accessed, so data corruption can go unnoticed for a long time, up to the point when it gets into all backups and leaves no source of correct data.
A zeroed page is considered correct, so if the file system zeroes out a page by mistake, this issue will not be discovered.
Checksums are calculated only for the main fork of relations; other forks and files (such as transaction status in CLOG) remain unprotected.
Let’s take a look at the read-only data_checksums parameter to make sure that checksums are enabled:
Now stop the server and zero out several bytes in the zero page of the main fork of the table:
Start the server again:
In fact, we could have left the server running—it is enough to write the page to disk and evict it from cache (otherwise, the server will continue using its cached version). But such a workflow is harder to reproduce.
Now let’s attempt to read the table:
If the data cannot be restored from a backup, it makes sense to at least try to read the damaged page (risking to get garbled output). For this purpose, you have to enable the ignore_checksum_failure parameter: (default: off)
Everything went fine in this case because we have damaged a non-critical part of the page header (the LSN of the latest WAL entry), not the data itself.
Non-Atomic Writes
A database page usually takes 8 kB, but at the low level writing is performed by blocks, which are often smaller (typically 512 bytes or 4 kB). Thus, if a failure occurs, a page may be written only partially. It makes no sense to apply regular WAL entries to such a page during recovery.
To avoid partial writes, PostgreSQL saves a full page image (FPI) in the WAL when this page is modified for the first time after the checkpoint start. This behavior is controlled by the full_page_writes parameter, but turning it off can lead to fatal data corruption. (p. 174) (default: on)
If the recovery process comes across an FPI in the WAL, it will unconditionally write it to disk (without checking its LSN); just like any WAL entry, FPIs are protected by checksums, so their damage cannot go unnoticed. Regular WAL entries will then be applied to this state, which is guaranteed to be correct.
There is no separate WAL entry type for setting hint bits: this operation is considered non-critical because any query that accesses a page will set the required bits anew. However, any hint bit change will affect the page’s checksum. So if checksums are enabled (or if the wal_log_hints parameter is on), hint bit modifications are logged as FPIs.16 (p. 71) (default: off)
Even though the logging mechanism excludes empty space from an FPI,17 the size of the generated WAL files still significantly increases. The situation can be greatly improved if you enable FPI compression via the wal_compression parameter. (default: off)
Let’s run a simple experiment using the pgbench utility. We will perform a checkpoint and immediately start a benchmark test with a hard-set number of transactions:
Here is the size of the generated WAL entries:
In this example, FPIs take more than half of the total WAL size. You can see it for yourself in the collected statistics that show the number of WAL entries (N), the size of regular entries (Record size), and the FPI size for each resource type (Type):
This ratio will be smaller if data pages get modified between checkpoints several times. It is yet another reason to perform checkpoints less often.
We will repeat the same experiment to see if compression can help.
Here is the WAL size with compression enabled:
To sum it up, when there is a large number of FPIs caused by enabled checksums or full_page_writes (that is, almost always), it makes sense to use compression despite some additional CPU overhead.
11.3 WAL Levels
The main objective of write-ahead logging is to enable crash recovery. But if you extend the scope of logged information, a WAL can be used for other purposes too. PostgreSQL provides minimal, replica, and logical logging levels. Each level includes everything that is logged on the previous one and adds some more information.
The level in use is defined by the wal_level parameter; its modification requires a server restart. (default: replica)
Minimal
The minimal level guarantees only crash recovery. To save space, the operations on relations that have been created or truncated within the current transaction are not logged if they incur insertion of large volumes of data (like in the case of CREATE TABLE AS SELECT and CREATE INDEX commands).18 Instead of being logged, all the required data is immediately flushed to disk, and system catalog changes become visible right after the transaction commit.
If such an operation is interrupted by a failure, the data that has already made it to disk remains invisible and does not affect consistency. If a failure occurs when the operation is complete, all the data required for applying the subsequent WAL entries is already saved to disk.
The volume of data that has to be written into a newly created relation for this optimization to take effect is defined by the wal_skip_threshold parameter. (v. 13) (default: 2MB)
Let’s see what gets logged at the minimal level.
By default, a higher replica level is used, which supports data replication. If you choose the minimal level, you also have to set the allowed number of walsender processes to zero in the max_wal_senders parameter: (v. 10) (default: 10)
The server has to be restarted for these changes to take effect:
Note the current WAL position:
Truncate the table and keep inserting new rows within the same transaction until the wal_skip_threshold is exceeded:
Instead of creating a new table, I run the TRUNCATE command as it generates fewer WAL entries.
Let’s examine the generated WAL using the already familiar pg_waldump utility.
The first entry logs creation of a new file for the relation (since TRUNCATE virtually rewrites the table). (p. 140)
The next four entries are associated with system catalog operations. They reflect the changes in the pg_class table and its three indexes.
Finally, there is a commit-related entry. Data insertion is not logged.
Replica
During crash recovery, WAL entries are replayed to restore the data on disk up to a consistent state. Backup recovery works in a similar way, but it can also restore the database state up to the specified recovery target point using a WAL archive. The number of archived WAL entries can be quite high (for example, they can span several days), so the recovery period will include multiple checkpoints. Therefore, the minimal WAL level is not enough: it is impossible to repeat an operation if it is unlogged. For backup recovery, WAL files must include all the operations.
The same is true for replication: unlogged commands will not be sent to a replica and will not be replayed on it.
Things get even more complicated if a replica is used for executing queries. First of all, it needs to have the information on exclusive locks acquired on the primary server since they may conflict with queries on the replica. Second, it must be able to capture snapshots, which requires the information on active transactions. When we deal with a replica, both local transactions and those running on the primary server have to be taken into account. (p. 204) (p. 80)
The only way to send this data to a replica is to periodically write it into WAL files.19 It is done by the bgwriter20 process, once in 15 seconds (the interval is hard-coded).
The ability to perform data recovery from a backup and use physical replication is guaranteed at the replica level.
The replica level is used by default, so we can simply reset the parameters configured above and restart the server: (v. 10)
Let’s repeat the same workflow as before (but now we will insert only one row to get a neater output):
Check out the generated WAL entries.
Apart from what we have seen at the minimal level, we have also got the following entries:
replication-related entries of the
Standbyresource manager:RUNNING_XACTS(active transactions) andLOCKthe entry that logs the
INSERT+INIToperation, which initializes a new page and inserts a new row into this page
Logical
Last but not least, the logical level enables logical decoding and logical replication. It has to be activated on the publishing server.
If we take a look at WAL entries, we will see that this level is almost the same as replica: it adds the entries related to replication sources and some arbitrary logical entries that may be generated by applications. For the most part, logical decoding depends on the information about active transactions (RUNNING_XACTS) because it requires capturing a snapshot to track system catalog changes.
backend/access/transam/xlog.c, XLogFlush function ↩︎
backend/access/transam/xlog.c, RecordTransactionCommit function ↩︎
backend/access/transam/xlog.c, XLogBackgroundFlush function ↩︎
backend/access/transam/xlog.c, issue_xlog_fsync function ↩︎
backend/storage/file/fd.c, pg_fsync function ↩︎
backend/storage/buffer/bufmgr.c, MarkBufferDirtyHint function ↩︎
backend/access/transam/xloginsert.c, XLogRecordAssemble function ↩︎
include/utils/rel.h, RelationNeedsWAL macro ↩︎
backend/storage/ipc/standby, LogStandbySnapshot function ↩︎