Write-Ahead Logging (WAL) in PostgreSQL and Redo Logs in Oracle have some things in common. Here I want to compare both with respect to database configuration.
WAL and Redo Logs both have the same purpose: to guarantee data
integrity the database management system must write changes to some kind
of stable storage. But instead of writing data directly to the location
where it belongs and from where it will be retrieved later on, records
describing the changes (in Oracle sometimes called change vectors) are
written to dedicated log files. After the log has been flushed to disk
the DBMS has enough information available to recreate the original data
changes after a crash. The data eventually will be written to the
correct location by an operation called checkpoint. When the
checkpoint is completed the log is no longer needed for crash recovery
(but it could still be valuable for log based replication or disaster
recovery).The invention of these logs is probably the consequence of plain old rotational disks being the prevailing kind of stable storage for most of the Information Age. The throughput of writing sequentially to a disk is much larger than writing single blocks to different spots on the disk. Therefore it will in general be much faster to write the log with a change description than to write the changed data itself to the database files.
The most visible difference between WAL and redo logs is their configuration. An Oracle DBA has to decide on the number of redo logs (two or more; to simplify things I will ignore mirrored redo logs here) and their size. So the disk space used by the redo logs is fixed and will only change if the DBA modifies the configuration. For PostgreSQL on the other hand the size of a WAL file is always 16 megabytes (unless the server software has been recompiled with a different value). The exact number of WAL files depends on a couple of configuration parameters (
checkpoint_completion_target, checkpoint_segments & wal_keep_segments) and the current load profile.So what does this mean for the database operation? Both DBMS will reuse a log when the contained data is no longer needed for recovery. That implies that the checkpoint for the relevant data blocks must have finished. The log must also have been archived successfully if archiving is enabled.
Oracle can only use the pre-configured logs and will emit warnings or error messages when a log cannot be reused. The message “
checkpoint not complete”
in the alert log indicates that Oracle has to postpone database changes
because switching to the next redo log is not yet possible due to the
still running checkpoint. Obviously this reduces the performance of the
application. It gets worse when the archiver is unable to archive a log.
In this case the error message “ORA-00257: archiver error. Connect internal only, until freed” comes up and the database freezes until the issue is resolved by the DBA.PostgreSQL on the other hand will start to add more WAL files to the directory
pg_xlog
when it can not reuse an existing log. This prevents the database from
stalling if checkpoints do not finish in time or the archiver process
can't keep up with the work. Of course an adequate amount of disk space
must be reserved for PostgreSQL to make this possible without failures.Both DBMS offer parameters to control the frequency of checkpoints. In PostgreSQL the parameter
checkpoint_timeout is used to define the maximum time interval between two checkpoints. Oracle has a similar parameter called log_checkpoint_timeout. But nowadays the checkpoint frequency is often automatically adjusted to satisfy the crash recovery time. The parameter fast_start_mttr_target
sets the target for the duration of a crash recovery and Oracle
triggers checkpoints to achieve that goal. So in this case the
configuration of Oracle uses a more business aligned number.In both systems the amount of changes done also triggers a checkpoint. PostgreSQL has the parameter
checkpoint_segments
to set the number of segments written to the WAL files before a
checkpoint happens. As said above, the segments normally have a size of
16 megabytes. So this gives at least 16 megabytes written to WAL until
this type of checkpoint is triggered. For Oracle the parameter log_checkpoint_interval
specifies the number of OS blocks (containing 512 bytes on most
platforms) between checkpoints. A checkpoint is also triggered by a redo
log switch and therefore the amount of changes is also defined by the
size of the redo logs. A DBA can create redo logs much smaller than 16
megabytes so this could lead to very frequent checkpoints in Oracle. In
this case the DBA will have to increase the size of the redo logs to
reduce the frequency of these checkpoints. In PostgreSQL the same can by
achieved by increasing the parameter checkpoint_segments.The first step when looking into checkpoint activity is to write the relevant details to a logfile. PostgreSQL uses the parameter
log_checkpoints
to write details of every checkpoint into the server log. The log even
shows if the checkpoint has been triggered by the timeout (indicated by checkpoint starting: time) or by the amount of changes (indicated by checkpoint starting: xlog). Oracle uses the parameter log_checkpoints_to_alert to write details into the alert log.
