Home Explore Blog CI



postgresql

10th chunk of `doc/src/sgml/wal.sgml`
f0c7bed68e1507830624e8d66241127223a54606e59b1c120000000100000fa3
 <varname>checkpoint_timeout</varname>).
   The I/O rate is adjusted so that the checkpoint finishes when the
   given fraction of
   <varname>checkpoint_timeout</varname> seconds have elapsed, or before
   <varname>max_wal_size</varname> is exceeded, whichever is sooner.
   With the default value of 0.9,
   <productname>PostgreSQL</productname> can be expected to complete each checkpoint
   a bit before the next scheduled checkpoint (at around 90% of the last checkpoint's
   duration).  This spreads out the I/O as much as possible so that the checkpoint
   I/O load is consistent throughout the checkpoint interval.  The disadvantage of
   this is that prolonging checkpoints affects recovery time, because more WAL
   segments will need to be kept around for possible use in recovery.  A user
   concerned about the amount of time required to recover might wish to reduce
   <varname>checkpoint_timeout</varname> so that checkpoints occur more frequently
   but still spread the I/O across the checkpoint interval.  Alternatively,
   <varname>checkpoint_completion_target</varname> could be reduced, but this would
   result in times of more intense I/O (during the checkpoint) and times of less I/O
   (after the checkpoint completed but before the next scheduled checkpoint) and
   therefore is not recommended.
   Although <varname>checkpoint_completion_target</varname> could be set as high as
   1.0, it is typically recommended to set it to no higher than 0.9 (the default)
   since checkpoints include some other activities besides writing dirty buffers.
   A setting of 1.0 is quite likely to result in checkpoints not being
   completed on time, which would result in performance loss due to
   unexpected variation in the number of WAL segments needed.
  </para>

  <para>
   On Linux and POSIX platforms <xref linkend="guc-checkpoint-flush-after"/>
   allows you to force OS pages written by the checkpoint to be
   flushed to disk after a configurable number of bytes.  Otherwise, these
   pages may be kept in the OS's page cache, inducing a stall when
   <literal>fsync</literal> is issued at the end of a checkpoint.  This setting will
   often help to reduce transaction latency, but it also can have an adverse
   effect on performance; particularly for workloads that are bigger than
   <xref linkend="guc-shared-buffers"/>, but smaller than the OS's page cache.
  </para>

  <para>
   The number of WAL segment files in <filename>pg_wal</filename> directory depends on
   <varname>min_wal_size</varname>, <varname>max_wal_size</varname> and
   the amount of WAL generated in previous checkpoint cycles. When old WAL
   segment files are no longer needed, they are removed or recycled (that is,
   renamed to become future segments in the numbered sequence). If, due to a
   short-term peak of WAL output rate, <varname>max_wal_size</varname> is
   exceeded, the unneeded segment files will be removed until the system
   gets back under this limit. Below that limit, the system recycles enough
   WAL files to cover the estimated need until the next checkpoint, and
   removes the rest. The estimate is based on a moving average of the number
   of WAL files used in previous checkpoint cycles. The moving average
   is increased immediately if the actual usage exceeds the estimate, so it
   accommodates peak usage rather than average usage to some extent.
   <varname>min_wal_size</varname> puts a minimum on the amount of WAL files
   recycled for future usage; that much WAL is always recycled for future use,
   even if the system is idle and the WAL usage estimate suggests that little
   WAL is needed.
  </para>

  <para>
   Independently of <varname>max_wal_size</varname>,
   the most recent <xref linkend="guc-wal-keep-size"/> megabytes of
   WAL files plus one additional WAL file are
   kept at all times. Also, if WAL archiving is used, old segments cannot be
   removed or recycled until they are archived. If WAL archiving cannot keep up
   with the pace that

Title: Checkpoint Configuration and WAL Management
Summary
The checkpoint process in PostgreSQL adjusts its I/O rate to finish within a specified fraction of the checkpoint timeout, and various parameters such as checkpoint_completion_target, checkpoint_flush_after, and wal_keep_size can be tuned to optimize performance, reduce transaction latency, and manage WAL segment files, while balancing recovery time and system resources.