18.5. Write Ahead Log

If this parameter is on, the PostgreSQL server will try to make sure that updates are physically written to disk, by issuing fsync() system calls or various equivalent methods (see wal_sync_method). This ensures that the database cluster can recover to a consistent state after an operating system or hardware crash.

However, using fsync results in a performance penalty: when a transaction is committed, PostgreSQL must wait for the operating system to flush the write-ahead log to disk. When fsync is disabled, the operating system is allowed to do its best in buffering, ordering, and delaying writes. This can result in significantly improved performance. However, if the system crashes, the results of the last few committed transactions might be lost in part or whole. In the worst case, unrecoverable data corruption might occur. (Crashes of the database software itself are not a risk factor here. Only an operating-system-level crash creates a risk of corruption.)

Due to the risks involved, there is no universally correct setting for fsync. Some administrators always disable fsync, while others only turn it off during initial bulk data loads, where there is a clear restart point if something goes wrong. Others always leave fsync enabled. The default is to enable fsync, for maximum reliability. If you trust your operating system, your hardware, and your utility company (or your battery backup), you can consider disabling fsync.

In many situations, turning off synchronous_commit for noncritical transactions can provide much of the potential performance benefit of turning off fsync, without the attendant risks of data corruption.

This parameter can only be set in the postgresql.conf file or on the server command line. If you turn this parameter off, also consider turning off full_page_writes.

Specifies whether transaction commit will wait for WAL records to be written to disk before the command returns a "success" indication to the client. The default, and safe, setting is on. When off, there can be a delay between when success is reported to the client and when the transaction is really guaranteed to be safe against a server crash. (The maximum delay is three times wal_writer_delay.) Unlike fsync, setting this parameter to off does not create any risk of database inconsistency: a crash might result in some recent allegedly-committed transactions being lost, but the database state will be just the same as if those transactions had been aborted cleanly. So, turning synchronous_commit off can be a useful alternative when performance is more important than exact certainty about the durability of a transaction. For more discussion see Section 29.3.

This parameter can be changed at any time; the behavior for any one transaction is determined by the setting in effect when it commits. It is therefore possible, and useful, to have some transactions commit synchronously and others asynchronously. For example, to make a single multi-statement transaction commit asynchronously when the default is the opposite, issue SET LOCAL synchronous_commit TO OFF within the transaction.

Method used for forcing WAL updates out to disk. If fsync is off then this setting is irrelevant, since updates will not be forced out at all. Possible values are:

• open_datasync (write WAL files with open() option O_DSYNC)

• fdatasync (call fdatasync() at each commit)

• fsync_writethrough (call fsync() at each commit, forcing write-through of any disk write cache)

• fsync (call fsync() at each commit)

• open_sync (write WAL files with open() option O_SYNC)

Not all of these choices are available on all platforms. The default is the first method in the above list that is supported by the platform. The open_* options also use O_DIRECT if available. This parameter can only be set in the postgresql.conf file or on the server command line.

When this parameter is on, the PostgreSQL server writes the entire content of each disk page to WAL during the first modification of that page after a checkpoint. This is needed because a page write that is in process during an operating system crash might be only partially completed, leading to an on-disk page that contains a mix of old and new data. The row-level change data normally stored in WAL will not be enough to completely restore such a page during post-crash recovery. Storing the full page image guarantees that the page can be correctly restored, but at a price in increasing the amount of data that must be written to WAL. (Because WAL replay always starts from a checkpoint, it is sufficient to do this during the first change of each page after a checkpoint. Therefore, one way to reduce the cost of full-page writes is to increase the checkpoint interval parameters.)

Turning this parameter off speeds normal operation, but might lead to a corrupt database after an operating system crash or power failure. The risks are similar to turning off fsync, though smaller. It might be safe to turn off this parameter if you have hardware (such as a battery-backed disk controller) or file-system software that reduces the risk of partial page writes to an acceptably low level (e.g., ReiserFS 4).

Turning off this parameter does not affect use of WAL archiving for point-in-time recovery (PITR) (see Section 24.3).

This parameter can only be set in the postgresql.conf file or on the server command line. The default is on.

The amount of memory used in shared memory for WAL data. The default is 64 kilobytes (64kB). The setting need only be large enough to hold the amount of WAL data generated by one typical transaction, since the data is written out to disk at every transaction commit. This parameter can only be set at server start.

Increasing this parameter might cause PostgreSQL to request more System V shared memory than your operating system's default configuration allows. See Section 17.4.1 for information on how to adjust those parameters, if necessary.

Specifies the delay between activity rounds for the WAL writer. In each round the writer will flush WAL to disk. It then sleeps for wal_writer_delay milliseconds, and repeats. The default value is 200 milliseconds (200ms). Note that on many systems, the effective resolution of sleep delays is 10 milliseconds; setting wal_writer_delay to a value that is not a multiple of 10 might have the same results as setting it to the next higher multiple of 10. This parameter can only be set in the postgresql.conf file or on the server command line.

Time delay between writing a commit record to the WAL buffer and flushing the buffer out to disk, in microseconds. A nonzero delay can allow multiple transactions to be committed with only one fsync() system call, if system load is high enough that additional transactions become ready to commit within the given interval. But the delay is just wasted if no other transactions become ready to commit. Therefore, the delay is only performed if at least commit_siblings other transactions are active at the instant that a server process has written its commit record. The default is zero (no delay).

Minimum number of concurrent open transactions to require before performing the commit_delay delay. A larger value makes it more probable that at least one other transaction will become ready to commit during the delay interval. The default is five transactions.

Maximum number of log file segments between automatic WAL checkpoints (each segment is normally 16 megabytes). The default is three segments. Increasing this parameter can increase the amount of time needed for crash recovery. This parameter can only be set in the postgresql.conf file or on the server command line.

Maximum time between automatic WAL checkpoints, in seconds. The default is five minutes (5min). Increasing this parameter can increase the amount of time needed for crash recovery. This parameter can only be set in the postgresql.conf file or on the server command line.

Specifies the target length of checkpoints, as a fraction of the checkpoint interval. The default is 0.5. This parameter can only be set in the postgresql.conf file or on the server command line.

Write a message to the server log if checkpoints caused by the filling of checkpoint segment files happen closer together than this many seconds (which suggests that checkpoint_segments ought to be raised). The default is 30 seconds (30s). Zero disables the warning. This parameter can only be set in the postgresql.conf file or on the server command line.

Controls which completed WAL segments are sent to archive storage by setting archive_command. Valid values are none, unsent and all. all archives all the WAL files. unsent archives only the WAL files which are not sent to the standby server by replication. If replication is not in progress, unsent also archives all the WAL files. Note that the standby server archives all the replicated WAL files if all or unsent is specified in the standby. none archives none of the WAL files. Note that none in the primary server disallows replication.

archive_mode and archive_command are separate variables so that archive_command can be changed without leaving archiving mode. The default is none. This parameter can only be set at server start.

The shell command to execute to archive a completed segment of the WAL file series. Any %p in the string is replaced by the path name of the file to archive, and any %f is replaced by the file name only. (The path name is relative to the working directory of the server, i.e., the cluster's data directory.) Use %% to embed an actual % character in the command. For more information see Section 24.3.1. This parameter can only be set in the postgresql.conf file or on the server command line. It is ignored unless archive_mode was enabled at server start. If archive_command is an empty string (the default) while archive_mode is enabled, then WAL archiving is temporarily disabled, but the server continues to accumulate WAL segment files in the expectation that a command will soon be provided.

It is important for the command to return a zero exit status if and only if it succeeds. Examples:

archive_command = 'cp "%p" /mnt/server/archivedir/"%f"'
archive_command = 'copy "%p" "C:\\server\\archivedir\\%f"'  # Windows

The archive_command is only invoked on completed WAL segments. Hence, if your server generates little WAL traffic (or has slack periods where it does so), there could be a long delay between the completion of a transaction and its safe recording in archive storage. To put a limit on how old unarchived data can be, you can set archive_timeout to force the server to switch to a new WAL segment file periodically. When this parameter is greater than zero, the server will switch to a new segment file whenever this many seconds have elapsed since the last segment file switch. Note that archived files that are closed early due to a forced switch are still the same length as completely full files. Therefore, it is unwise to use a very short archive_timeout — it will bloat your archive storage. archive_timeout settings of a minute or so are usually reasonable. This parameter can only be set in the postgresql.conf file or on the server command line.

Specifies what will make transaction commit wait before the command returns a "success" indication to the client, when replication is in progress. Valid values are async, write and fsync. write or fsync makes transaction commit wait for WAL records to be written or fsynced to the disk of the standby server, respectively. async doesn't make transaction commit wait for replication, so there can be a delay between when success is reported to the client and when the WAL records are actually sent to the standby server. (The maximum delay is wal-sender-delay.) Setting this parameter to async does not create any risk of database inconsistency: a crash at the primary server might result in some recent allegedly-committed transactions being lost at the standby server, but the database state of the standby will be just the same as if those transactions had been aborted cleanly. So, turning synchronous_replication async can be a useful alternative when performance is more important than exact certainty about the durability of a transaction. For more discussion see Chapter 26.

The default, and safe, setting is write. This parameter can be changed at any time; the behavior for any one transaction is determined by the setting in effect when it commits. It is therefore possible, and useful, that some transactions are replicated synchronously and others asynchronously.

Maximum time to wait until WAL records are replicated to the standby server, in milliseconds. Abort replication that takes over the specified time, starting from the time the server process begins waiting for acknowledgment of receipt of WAL from the standby server. This awaken any waiters. The normal operation of the current or any other backend is not affected, but replication stops until the standby server is restarted. A value of zero (the default) turns off the limitation. Only superusers can change this setting.

Maximum delay for WAL records to reach the standby server, in milliseconds. This parameter is mostly useful for asynchronous replication (synchronous_replication is async) to guarantee the maximum loss of transactions in case of server failure. The WAL sender periodically checks for any pending WAL transfers, sends the pending WAL to the standby server and sleeps for wal_sender_delay if there is nothing to do. The default value is 200 milliseconds (200ms). Note that on many systems, the effective resolution of sleep delays is 10 milliseconds; setting wal_sender_delay to a value that is not a multiple of 10 might have the same results as setting it to the next higher multiple of 10. This parameter can only be set in the postgresql.conf file or on the server command line.