Thread: Re: CHECKPOINT unlogged data
Hi, On 2025-05-30 18:17:45 +0200, Christoph Berg wrote: > A customer reported to use CHECKPOINT before shutdowns to make the > shutdowns themselves faster and asked if it was possible to make > CHECKPOINT optionally also write out unlogged table data for that > purpose. I think the idea makes sense, so there's the patch. I've seen the need for this quite a few times, fwiw. So +1 to the idea. Greetings, Andres Freund
Hi, On May 30, 2025 12:55:28 PM EDT, Christoph Berg <myon@debian.org> wrote: >Re: Andres Freund >> Hi, >> >> On 2025-05-30 18:17:45 +0200, Christoph Berg wrote: >> > A customer reported to use CHECKPOINT before shutdowns to make the >> > shutdowns themselves faster and asked if it was possible to make >> > CHECKPOINT optionally also write out unlogged table data for that >> > purpose. I think the idea makes sense, so there's the patch. >> >> I've seen the need for this quite a few times, fwiw. So +1 to the idea. > >Do we want to officially mention this use case in checkpoint.sgml? I've >already replaced the "is not intended for use during normal operation" >wording by "not required during normal operation", but we might go one >step further and endorse it. Yes, I think it's good to mention what it is useful for. Greetings, Andres -- Sent from my Android device with K-9 Mail. Please excuse my brevity.
On Fri, May 30, 2025 at 12:39:02PM -0400, Andres Freund wrote: > On 2025-05-30 18:17:45 +0200, Christoph Berg wrote: >> A customer reported to use CHECKPOINT before shutdowns to make the >> shutdowns themselves faster and asked if it was possible to make >> CHECKPOINT optionally also write out unlogged table data for that >> purpose. I think the idea makes sense, so there's the patch. > > I've seen the need for this quite a few times, fwiw. So +1 to the idea. This patch also adds an IMMEDIATE option, which I proposed some time ago [0]. I ended up withdrawing it due to general skepticism about its usefulness. FWIW I have no concerns about adding a few retail options to CHECKPOINT, but others might balk at options without solid use-cases. The unlogged table one seems reasonable enough. [0] https://postgr.es/m/17A03557-CF5C-4D4B-B719-A1D98DD75E75%40amazon.com -- nathan
Re: Nathan Bossart
> This patch also adds an IMMEDIATE option, which I proposed some time ago
> [0]. I ended up withdrawing it due to general skepticism about its
Thanks for the pointer, I did not go that far back when looking for
older threads.
When writing the patch, I was also thinking about naming the option
"fast" or "spread" but ultimately went with "immediate" because that's
what the log message is using:
=# checkpoint;
2025-05-30 18:23:17.433 CEST [579834] LOG: Checkpoint beginnt: immediate force wait
SQL command "(options)" tend to be booleans, hence "immediate {on|off}".
Introducing two separate keywords "fast" and "spread" seemed
confusing, and there is no precedent for "fast=on" in other tools or
the replication protocol.
> usefulness. FWIW I have no concerns about adding a few retail options to
> CHECKPOINT, but others might balk at options without solid use-cases. The
> unlogged table one seems reasonable enough.
I think the two options immediate and flush_all are actually useful in
combination for the shutdown case. If operation is to continue
normally until just before the shutdown, it might make sense to run
these 3 commands (or just #1 and #3):
checkpoint (flush_all, immediate false);
checkpoint (flush_all);
pg_ctl stop
(I also thought about a VERBOSE option, but since the checkpoint
messages are generated by a different process, it's probably harder
than I initially thought.)
Christoph
On 2025-05-30 19:23:04 +0200, Christoph Berg wrote:
> Re: Nathan Bossart
> > This patch also adds an IMMEDIATE option, which I proposed some time ago
> > [0]. I ended up withdrawing it due to general skepticism about its
>
> Thanks for the pointer, I did not go that far back when looking for
> older threads.
>
> When writing the patch, I was also thinking about naming the option
> "fast" or "spread" but ultimately went with "immediate" because that's
> what the log message is using:
>
> =# checkpoint;
> 2025-05-30 18:23:17.433 CEST [579834] LOG: Checkpoint beginnt: immediate force wait
>
> SQL command "(options)" tend to be booleans, hence "immediate {on|off}".
> Introducing two separate keywords "fast" and "spread" seemed
> confusing, and there is no precedent for "fast=on" in other tools or
> the replication protocol.
I'd add a 'mode' that can be set to an arbitrary string, which then can be
validated in C code. That seems more future proof.