WAL documentation - Mailing list pgsql-hackers

Here is documentation for WAL, as text for immediate review and as SGML
source, generated from Vadim's original text with my editing.

Please review for correctness.

=========================== WAL chapter ==========================

Write-Ahead Logging (WAL) in Postgres
   Author: Written by Vadim Mikheev and Oliver Elphick.



General description

Write Ahead Logging (WAL) is a standard approach to transaction logging.
Its detailed description may 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 indices reside) must be written only after
those changes have been logged - that is, when log records have been
flushed to permanent storage. When 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
will first be redone from the log records (this is roll-forward recovery,
also known as REDO) and then changes made by uncommitted transactions
will be removed from the data pages (roll-backward recovery - UNDO).



Immediate benefits of WAL

The first obvious benefit of using WAL is a significantly reduced number
of disk writes, since only the log file needs to be flushed to disk at
the time of transaction commit; in multi-user environments, commits of
many transactions may be accomplished with a single fsync() of the log
file. Furthermore, the log file is written sequentially, and so the cost
of syncing the log is much less than the cost of syncing the data pages.

The next benefit is consistency of the data pages. The truth is that,
before WAL, PostgreSQL was never able to guarantee consistency in the
case of a crash.  Before WAL, any crash during writing could result in:

1. index tuples pointing to non-existent table rows;
2. index tuples lost in split operations;
3. totally corrupted table or index page content, because of  partially written data pages.

(Actually, the first two cases could even be caused by use of the "pg_ctl
-m {fast | immediate} stop" command.)  Problems with indices (problems
1 and 2) might have been capable of being fixed by additional fsync()
calls, but it is not obvious how to handle the last case without WAL;
WAL saves the entire data page content in the log if that is required
to ensure page consistency for after-crash recovery.



Future benefits

In this first release of WAL, UNDO operation is not implemented, because
of lack of time. This means that changes made by aborted transactions
will still occupy disk space and that we still need a permanent pg_log
file to hold the status of transactions, since we are not able to re-use
transaction identifiers.  Once UNDO is implemented, pg_log will no longer
be required to be permanent; it will be possible to remove pg_log at
shutdown, split it into segments and remove old segments.

With UNDO, it will also be possible to implement SAVEPOINTs to allow
partial rollback of invalid transaction operations (parser errors caused
by mistyping commands, insertion of duplicate primary/unique keys and
so on) with the ability to continue or commit valid operations made by
the transaction before the error.  At present, any error will invalidate
the whole transaction and require a transaction abort.

WAL offers the opportunity for a new method for database on-line backup
and restore (BAR).  To use this method, one would have to make periodic
saves of data files to another disk, a tape or another host and also
archive the WAL log files.  The database file copy and the archived
log files could be used to restore just as if one were restoring after a
crash. Each time a new database file copy was made the old log files could
be removed.  Implementing this facility will require the logging of data
file and index creation and deletion; it will also require development of
a method for copying the data files (O/S copy commands are not suitable).



Implementation

WAL is automatically enabled from release 7.1 onwards.    No action is
required from the administrator with the exception of ensuring that the
additional disk-space requirements of the WAL logs are met, and that
any necessary tuning is done (see below).

WAL logs are stored in $PGDATA/pg_xlog, as a set of segment files, each
16Mb in size.  Each segment is divided into 8Kb pages.    The log record
headers are described in access/xlog.h; record content is dependent on the
type of event that is being logged.  Segment files are given sequential
numbers as names, starting at 0000000000000000.  The numbers do not wrap,
at present, but it should take a very long time to exhaust the available
stock of numbers.

The WAL buffers and control structure are in shared memory, and are
handled by the backends; they are protected by spinlocks.  The demand
on shared memory is dependent on the number of buffers; the default size
of the WAL buffers is 64Kb.

It is desirable for the log to be located on another disk than the main
database files.  This may be achieved by moving the directory, pg_xlog,
to another filesystem (while the postmaster is shut down, of course)
and creating a symbolic link from $PGDATA to the new location.

The aim of WAL, to ensure that the log is written before database
records are altered, may be subverted by disk drives that falsely report
a successful write to the kernel, when, in fact, they have only cached
the data and not yet stored it on the disk.  A power failure in such a
situation may still lead to irrecoverable data corruption; administrators
should try to ensure that disks holding PostgreSQL's data and log files
do not make such false reports.



WAL parameters

There are several WAL-related parameters that affect database
performance. This section explains their use.

There are two commonly used WAL functions - LogInsert and LogFlush.
LogInsert is used to place a new record into the WAL buffers in shared
memory. If there is no space for the new record, LogInsert will have to
write (move to OS cache) a few filled WAL buffers. This is undesirable
because LogInsert is used on every database low level modification
(for example, tuple insertion) at a time when an exclusive lock is held
on affected data pages and the operation is supposed to be as fast as
possible; what is worse, writing WAL buffers may also cause the creation
of a new log segment, which takes even more time. Normally, WAL buffers
should be written and flushed by a LogFlush request, which is made,
for the most part, at transaction commit time to ensure that transaction
records are flushed to permanent storage. On systems with high log output,
LogFlush requests may not occur often enough to prevent WAL buffers'
being written by LogInsert. On such systems one should increase the
number of WAL buffers by modifying the "WAL_BUFFERS" parameter. The
default number of WAL buffers is 8.  Increasing this value will have an
impact on shared memory usage.

Checkpoints are points in the sequence of transactions at which it is
guaranteed that the data files have been updated with all information
logged before the checkpoint.  At checkpoint time, all dirty data pages
are flushed to disk and a special checkpoint record is written to the
log file. As result, in the event of a crash, the recoverer knows from
what record in the log (known as the redo record) it should start the
REDO operation, since any changes made to data files before that record
are already on disk. After a checkpoint has been made, any log segments
written before the redo record may be removed/archived, so checkpoints
are used to free disk space in the WAL directory. The checkpoint maker
is also able to create a few log segments for future use, so as to avoid
the need for LogInsert or LogFlush to spend time in creating them.

The WAL log is held on the disk as a set of 16Mb files called segments.
By default a new segment is created only if more than 75% of the current
segment is used. One can instruct the server to create up to 64 log
segments at checkpoint time by modifying the "WAL_FILES" parameter.

For faster after-crash recovery, it would be better to create checkpoints
more often.  However, one should balance this against the cost of flushing
dirty data pages; in addition, to ensure data page consistency,the first
modification of a data page after each checkpoint results in logging
the entire page content, thus increasing output to log and the log's size.

By default, the postmaster spawns a special backend process to create the
next checkpoint 300 seconds after the previous checkpoint's creation.
One can change this interval by modifying the "CHECKPOINT_TIMEOUT"
parameter.  It is also possible to force a checkpoint by using the SQL
command, CHECKPOINT.

Setting the "WAL_DEBUG" parameter to any non-zero value will result in
each LogInsert and LogFlush WAL call's being logged to standard error.
At present, it makes no difference what the non-zero value is.

The "COMMIT_DELAY" parameter defines for how long the backend will be
forced to sleep after writing a commit record to the log with LogInsert
call but before performing a LogFlush. This delay allows other backends
to add their commit records to the log so as to have all of them flushed
with a single log sync. Unfortunately, this mechanism is not fully
implemented at release 7.1, so there is at present no point in changing
this parameter from its default value of 5 microseconds.

===================== CHECKPOINT manual page ======================

CHECKPOINT -- Forces a checkpoint in the transaction log

Synopsis

CHECKPOINT

Inputs

None

Outputs

CHECKPOINT

This signifies that a checkpoint has been placed into the transaction log.

Description

Write-Ahead Logging (WAL) puts a checkpoint in the log every 300 seconds
by default. (This may be changed by the parameter CHECKPOINT_TIMEOUT
in postgresql.conf.)

The CHECKPOINT command forces a checkpoint at the point at which the
command is issued. The next automatic checkpoint will happen the default
time after the forced checkpoint.

Restrictions

Use of the CHECKPOINT command is restricted to users with administrative
access.

Usage

To force a checkpoint in the transaction log:

CHECKPOINT;

Compatibility

SQL92

CHECKPOINT is a Postgres language extension. There is no CHECKPOINT
command in SQL92.

Note: The handling of database storage and logging is a matter that the
standard leaves to the implementation.

Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47  6B 7E 39 CC 56 E4 C1 47
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
========================================   "Look not every man on his own interests, but every man     also on the
thingsof others. Let this mind be in you,     which was also in Christ Jesus"
Philippians 2:4,5