WAL
Checkpoint
16
Copyright
© Postgres Professional, 2016–2026
Authors: Egor Rogov, Pavel Luzanov, Ilya Bashtanov, Igor Gnatyuk
Translated by: Elena Sharafutdinova
Photo: Oleg Bartunov (Phu Monastery and Bhrikuti Peak, Nepal)
Use of Course Materials
Non-commercial use of course materials (presentations, demonstrations) is
allowed without restrictions. Commercial use is possible only with the written
permission of Postgres Professional. It is prohibited to make changes to the
course materials.
Feedback
Please send your feedback, comments and suggestions to:
edu@postgrespro.ru
Disclaimer
Postgres Professional assumes no responsibility for any damages and
losses, including loss of income, caused by direct or indirect, intentional or
accidental use of course materials. Postgres Professional company
specifically disclaims any warranties on course materials. Course materials
are provided “as is,” and Postgres Professional company has no obligations
to provide maintenance, support, updates, enhancements, or modifications.
2
Topics
Checkpointer Process
Background Writer Process
Monitoring
3
Checkpoint
Why Checkpoint Is Required
Checkpoint Procedure
Crash Recovery Algorithm
Tuning
4
Why Checkpoint Is Required
Size of stored WAL records
which WAL segment to start recovery from?
actively used page may not be evicted from cache
Recovery time
how long does crash recovery take?
If no special measures are taken, an actively used page, once loaded into
the buffer cache, might never get evicted. This means that crash recovery
procedure will have to scan all the WAL records created since the server
was started.
In practice, it is indeed unacceptable for two main reasons. First, WAL files
take up a lot of space, and all of them have to be stored on the server.
Second, the recovery time could be extremely long as countless WAL
records need to be processed.
To prevent this, the checkpoint procedure flushes all dirty pages to disk
without evicting them from the cache. A dedicated background process
called checkpointer periodically performs a checkpoint. Once a checkpoint is
completed, WAL records that precede its start are no longer required for
recovery.
5
Checkpoint Procedure
dirty CLOG buffers are written to disk
CLOG
Let’s examine in more detail what happens when a checkpoint is performed.
First, RAM contains structures other than the buffer cache whose contents
must be persisted to disk.
In particular, the checkpoint flushes transaction status buffers (CLOG) to
disk. Since the number of these buffers is small (only 128), they get written
immediately.
6
Checkpoint Procedure
3
1 12
1 0
modified pages in the buffer cache are marked
Second, the main function of a checkpoint is to flush all pages from the
buffer cache that were dirty at the moment the checkpoint started.
Since the buffer cache can be very large, flushing all pages at once is
problematic, as it would interrupt normal server operations and put
excessive load on the I/O subsystem. Therefore, the checkpoint is spread
out over time and turns from a point into an interval.
Initially, all pages modified up to this point are marked in the buffer header
with a special flag...
7
Checkpoint Procedure
3
1 12
1 0
marked pages are gradually written to disk,
the flag in the buffer header is reset
checkpoint_completion_target = 0.9
became dirty
after the checkpoint
start
... and then the checkpoint gradually iterates through all the buffers and
flushes the marked pages to disk. Note that the pages are only written to
disk, but not evicted from the cache. Therefore, the checkpoint procedure
ignores both the buffer’s usage count and pin count.
The marked buffers can also be written by backends, whichever reaches the
buffer first. In any case, the flag is reset when the buffer is written, so the
buffer will not be written twice.
Pages continue to change in the buffer cache during the checkpoint.
However, newly dirty buffers are not processed by the checkpoint, as they
were not dirty when it started.
The rate at which dirty buffers are written is controlled by the
checkpoint_completion_target configuration parameter. It specifies the
fraction of time between two adjacent checkpoints that should be used for
writing pages. With the default value of 0.9, PostgreSQL should complete
the checkpoint procedure shortly before the next scheduled one. Using
values higher than the default 0.9 is not recommended, as the process may
actually take a bit longer than specified by the parameter.
8
Checkpoint Procedure
a checkpoint completion record is written to the WAL
indicating its starting point
checkpoint’s LSN is written to the pg_control file
WAL
…
Latest checkpoint location: 0/1BA0CD8
PGDATA/global/pg_control
checkpoint
was started at
this moment
checkpoint
completion
record
Latest checkpoint’s REDO location: 0/1BA0CA0
…
When completed, the checkpoint creates a WAL record marking the end of
the checkpoint. This WAL record contains the LSN of the checkpoint’s start.
Since the checkpoint does not write a WAL record when it starts, this LSN
can reference any valid WAL record.
Besides, a reference to this WAL record is written to the PGDATA/global/
pg_control file. This allows the system to quickly identify the last completed
checkpoint.
10
Recovery
During server startup after a crash
1 Find LSN
0
corresponding to the start of the last completed
checkpoint
2 Apply each WAL record starting from LSN
0
,
if the record’s LSN is greater than the page's LSN
3 Abort transactions that were started but not committed
4 Overwrite unlogged tables using their init files
5 Perform a checkpoint
xid
checkpoint
checkpoint crash
required WAL files
start
of recovery
If the server fails, at the subsequent start, the startup process detects this by
looking into the pg_control file to find the status different from "shut down“.
Automatic recovery is done in this case.
The process first reads the LSN of the last completed checkpoint record
from that same pg_control file. From this record, the process determines
LSN0, the starting position of that checkpoint.
(In case of recovery from a backup, the backup_label file is present, and the
checkpoint record is read from that file instead. For more details, see the
DBA3 course.)
Next, the startup process reads the WAL records one by one, starting from
the determined position, sequentially applying records to pages, if necessary
(the need is determined by comparing LSN of the on-disk page with LSN of
the WAL record). Page modifications occur in the buffer cache, just as they
do during normal operations.
Records related to CLOG pages restore the status of transactions.
Transactions not committed by the end of the recovery are considered
aborted; their changes are not visible in any data snapshots.
WAL records are applied to files in a similar way: for example, if it is clear
from a record that the file should be created, but it does not exist, the file is
created.
At the end of the process, all unlogged tables are overwritten using the
templates stored in the init files. At this point, the startup process completes
the recovery and performs a checkpoint.
12
Checkpoint frequency
WAL size
checkpoint_timeout
↑
max_wal_size
time
checkpoint_timeout = 5min
max_wal_size = 1GB
between
checkpoints
since the start of the
current checkpoint
Checkpoints are typically set up based on the following factors.
First, we need to determine an acceptable checkpoint frequency (based on
the recovery time requirements and the size of WAL files generated during
that period under normal load). The less frequently we need to run
checkpoints, the better, as this reduces overhead.
The acceptable frequency is set using the checkpoint_timeout parameter
(the default value of 5 minutes is too short, it is often increased up to 30
minutes).
However, the load may exceed our planned capacity and too many WAL
records may be generated within the specified time. To prevent this, we
specify the total permissible size of WAL records in the max_wal_size
parameter.
For crash recovery, the server needs to keep WAL files from the start of the
last completed checkpoint until the start of the current one (the size between
checkpoints), plus WAL files that have accumulated during the current
checkpoint. Therefore, the total WAL size can be estimated as:
(1 + checkpoint_completion_target) × size between checkpoints.
Thus, most checkpoints are performed on schedule, once every
checkpoint_timeout. However, under high load, checkpoints are performed
more frequently to manage not to exceed the max_wal_size limit.
13
WAL File Size
Server stores WAL files
required for recovery (typically < max_wal_size)
not yet consumed by replication slots
not yet archived, if continuous archiving is enabled
not exceeding wal_keep_size limit
not exceeding min_wal_size threshold (during recycling)
Parameters
max_wal_size = 1GB
wal_keep_size = 0
wal_recycle = on
min_wal_size = 80MB
Note that the size specified in the max_wal_size parameter can be
exceeded. This is not a strict limit, but a target for the checkpointer, which
regulates its intensity when flushing dirty buffers.
Moreover, the server cannot remove WAL files that have not been
consumed by replication slots, and files that have not been archived during
continuous archiving. This can lead to excessive disk space usage. So,
when these features are used, continuous monitoring is essential.
In cases where a replica falls behind, we can use the wal_keep_size
parameter to set a minimum amount of WAL files that will be kept after a
checkpoint. While this does not guarantee that a specific WAL record will be
kept until the replica needs it, it allows the replication to proceed without a
replication slot.
By default, a certain number of WAL files are not deleted, instead they are
renamed and reused. The min_wal_size parameter sets the minimum
amount of WAL to keep. This avoids the overhead of repeatedly creating
and deleting files. However, in copy-on-write filesystems, it is faster to create
a new file, so it is recommended to disable recycling for them by setting
wal_recycle=off.
15
Background Writer
Background Writer Process
Tuning
16
Background Writer Process
3
1 1
next victim
3
can be
evicted, needs to
be flushed
1 0
next to be written
When a backend process needs to evict a page from the buffer cache, it
may find that the buffer is dirty and will have to write its contents to disk. To
reduce the frequency of such situations, there is also a background writer
process (bgwriter) in addition to the checkpointer process.
The background writer uses the same buffer search algorithm as backends,
but it operates its own pointer which can be ahead of the next victim pointer
but never falls behind it.
The background writer flushes a buffer that meets all of the following criteria:
●
Contain changed data (dirty)
●
Not pinned (pin count = 0)
●
Have usage count = 0
Thus, by running ahead, the background writer identifies buffers
that are likely to be evicted soon. This strategy increases the probability that
a victim buffer chosen by a backend is already clean.
17
Background Writer Process
Algorithm
sleep for bgwriter_delay
if the average number of buffers requested per cycle is N, then write
N × bgwriter_lru_multiplier ≤ bgwriter_lru_maxpages
dirty buffers
Parameters
bgwriter_delay = 200ms
bgwriter_lru_maxpages = 100
bgwriter_lru_multiplier = 2.0
bgwriter_flush_after = 512kB
The background writer process operates in cycles of up to
bgwriter_lru_maxpages, sleeping for bgwriter_delay between cycles.
(So, if bgwriter_lru_maxpages is set to zero, the background writer will be
disabled.)
The exact number of buffers to write is determined by the average number
of buffers recently requested by backends (using a moving average to
smooth out variations between cycles while avoiding reliance on distant
history). This calculated number of buffers is then multiplied by the
bgwriter_lru_multiplier.
If the process does not find any dirty buffers (indicating no system activity), it
enters a sleep state, from which it is awakened by a backend’s request for a
buffer. After that, the process wakes up and resumes normal operation.
When large amounts of data need to be written, the server instructs the OS
to perform an intermediate flush to storage (fsync) once the volume
specified by the bgwriter_flush_after parameter is reached. This reduces
transaction commit latency and the time needed for synchronization at the
end of a checkpoint.
The background writer should be configured after checkpointer tuning is
complete. Both processes together should keep up with writing dirty buffers
before backends require them.
18
Monitoring
Checkpoints
checkpoint_warning = 30s
log_checkpoints = on
startup and checkpointer process statuses
Dirty buffer writes by processes
pg_stat_bgwriter and pg_stat_io views
The checkpoint_warning parameter allows to issue a warning in the log if
checkpoints are performed more often than defined by the value. If this
occurs frequently, consider increasing max_wal_size or decreasing the
checkpoint_timeout interval.
The log_checkpoints parameter, which is enabled by default, logs detailed
information about each completed checkpoint.
When the server is in recovery mode, we can monitor the status of the
startup and checkpointer processes using OS utilities, such as ps.
Statistics for processes writing dirty buffers (checkpoint, background writer,
and backends), are displayed in the pg_stat_bgwriter view, and starting from
version 16, in the pg_stat_io view.
In PostgreSQL version 17, a new pg_stat_checkpointer view will be
introduced, which contains checkpointer process statistics currently found in
pg_stat_bgwriter.
20
Takeaways
The checkpoint procedure limits the amount of stored WAL files
and reduces recovery time
The checkpointer and background writer processes flush dirty
buffers to disk
Backends can flush dirty buffers to disk, but as a last resort only
21
Practice
1. Set up checkpoints to run every 30 seconds. Set both
min_wal_size and max_wal_size parameters to 32 MB.
2. Use the pgbench utility to generate a load of 100 transactions per
second for several minutes.
3. Get the WAL size generated during this period.
4. Review the statistics data and estimate the average size per
checkpoint. Were all checkpoints executed on schedule?
Explain the result.
5. Reset the parameters to their default values.
