From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Christoph Berg <myon@debian.org>
To: Nathan Bossart <nathandbossart@gmail.com>
Cc: Fujii Masao <masao.fujii@oss.nttdata.com>
Cc: Andres Freund <andres@anarazel.de>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Subject: Re: CHECKPOINT unlogged data
Date: Thu, 12 Jun 2025 09:14:08 +0200
Message-ID: <086ab9ad5a2c06eb16f6f4e50a04764ba6fafd1b.camel@cybertec.at> (raw)
In-Reply-To: <aEmma1MPm_UgmLP3@msg.df7cb.de>
References: <aDnpeJ99HhoMgJ40@msg.df7cb.de>
<7x6gsmev36zzh6lfzydkddbhpnxqgu2k2l2ci2k6foxhrstvj2@bn55g54wjrl4>
<aEK88AwaYBNK1p-O@msg.df7cb.de>
<fc1ed66f-e95a-4d10-a4f7-7fa4bb9a7084@oss.nttdata.com>
<aEL6sDr9RxdSkPm-@msg.df7cb.de>
<aEMIrLUDOKgmE_0P@nathan>
<aEMVRbmqqg-aaxAN@msg.df7cb.de>
<aEMYYEkQb2VeQoBo@nathan>
<aEmIijETyhj9DsWG@msg.df7cb.de>
<aEmLzinvZ-W8EPiW@nathan>
<aEmma1MPm_UgmLP3@msg.df7cb.de>
On Wed, 2025-06-11 at 17:53 +0200, Christoph Berg wrote:
> I haven't implemented a WAIT option yet since I didn't want to decide
> that without more votes in either direction.
I had a look at it, and I have suggestions for the documentation.
> --- a/doc/src/sgml/ref/checkpoint.sgml
> +++ b/doc/src/sgml/ref/checkpoint.sgml
> [...]
> <para>
> - The <command>CHECKPOINT</command> command forces a fast
> - checkpoint when the command is issued, without waiting for a
> - regular checkpoint scheduled by the system (controlled by the settings in
> - <xref linkend="runtime-config-wal-checkpoints"/>).
> - <command>CHECKPOINT</command> is not intended for use during normal
> - operation.
> + Running <command>CHECKPOINT</command> is not required during normal
> + operation; the system schedules checkpoints automatically (controlled by
> + the settings in <xref linkend="runtime-config-wal-checkpoints"/>).
> + <command>CHECKPOINT</command> can be beneficial to run before operations
> + such as binary backups or shutting down <productname>PostgreSQL</productname>
> + as this can reduce the amount of data to be flushed for the next checkpoint
> + within these operations. Specifically, <literal>UNLOGGED</literal> table
> + data is otherwise only flushed by the shutdown checkpoint.
> </para>
How about the following for added clarity:
Running an explicit <command>CHECKPOINT</command> is not required during
normal operation; the system schedules checkpoints automatically (controlled
by the settings in <xref linkend="runtime-config-wal-checkpoints"/>).
However, it can be useful to perform an explicit checkpoint immediately
before shutting down the server or performing an online file system backup,
if you want the checkpoint implicit in these operations to be as fast as
possible. In particular, <literal>UNLOGGED</literal> table data only get
flushed to disk during a shutdown checkpoint, so you should use the option
<literal>FLUSH_UNLOGGED</literal> for an explicit checkpoint right before a
shutdown.
> <para>
> If executed during recovery, the <command>CHECKPOINT</command> command
> - will force a restartpoint (see <xref linkend="wal-configuration"/>)
> - rather than writing a new checkpoint.
> + will force a restartpoint rather than writing a new checkpoint. (The
> + operation will be a no-op if there is no corresponding checkpoint in the
> + write-ahead log.) If a checkpoint is already being written when a
> + <command>CHECKPOINT</command> is issued, the running checkpoint is upgraded
> + with this command's <literal>MODE</literal> and
> + <literal>FLUSH_UNLOGGED</literal> options.
> </para>
Oh, interesting; I wasn't aware of that. That means that running CHECKPOINT
(FLUSH_UNLOGGED) is less useful than I thought, since there is often already
a spread checkpoint running. Would it be useful to recommend that you should
run the command twice when aiming for a fast shutdown?
@@ -58,6 +69,54 @@ CHECKPOINT
</para>
</refsect1>
+ <refsect1>
+ <title>Parameters</title>
+
+ <variablelist>
+ <varlistentry>
+ <term><literal>MODE</literal></term>
+ <listitem>
+ <para>
+ The <command>CHECKPOINT</command> command forces a fast checkpoint by
+ default when the command is issued, without waiting for a regular
+ checkpoint scheduled by the system.
+ </para>
+ <para>
+ A <literal>SPREAD</literal> checkpoint will instead spread out the write
+ load as determined by the
+ <xref linkend="guc-checkpoint-completion-target"/> setting, like the
+ system-scheduled checkpoints.
+ </para>
+ </listitem>
+ </varlistentry>
I find this somewhat confusing; how about
<para>
The default <literal>FAST</literal> mode causes the checkpoint to be
performed as fast as possible. A <literal>SPREAD</literal> checkpoint
will instead spread out the write load as determined by the
<xref linkend="guc-checkpoint-completion-target"/> setting, like the
system-scheduled checkpoints.
</para>
+ <varlistentry>
+ <term><literal>FLUSH_UNLOGGED</literal></term>
+ <listitem>
+ <para>
+ All data files except for those marked as <literal>UNLOGGED</literal>
+ are flushed to disk during a checkpoint. Enabling this option will also
+ flush <literal>UNLOGGED</literal> relations; the default is off.
+ </para>
+ </listitem>
+ </varlistentry>
How about:
<varlistentry>
<term><literal>FLUSH_UNLOGGED</literal></term>
<listitem>
<para>
Normally, data files marked as <literal>UNLOGGED</literal> are not
flushed to disk during a checkpoint. Enabling this option will also
flush <literal>UNLOGGED</literal> relations. This option is disabled
by default.
</para>
</listitem>
</varlistentry>
Yours,
Laurenz Albe
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: laurenz.albe@cybertec.at, myon@debian.org, nathandbossart@gmail.com, masao.fujii@oss.nttdata.com, andres@anarazel.de, pgsql-hackers@lists.postgresql.org
Subject: Re: CHECKPOINT unlogged data
In-Reply-To: <086ab9ad5a2c06eb16f6f4e50a04764ba6fafd1b.camel@cybertec.at>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox