Re: A few new options for CHECKPOINT

Greetings,

* Bossart, Nathan (bossartn(at)amazon(dot)com) wrote:
> On 11/28/20, 9:50 AM, "Stephen Frost" <sfrost(at)snowman(dot)net> wrote:
> >> Granted, I used a rather aggressive checkpoint_timeout, but I think
> >> this demonstrates that waiting for a non-immediate checkpoint to
> >> complete can lower the amount of WAL needed for recovery, even though
> >> it might not lower it as much as waiting for an immediate checkpoint
> >> would.
> >
> > The difference here feels like order of magnitudes to me, between an
> > immediate checkpoint and a non-immediate one, vs. a much smaller
> > difference as you've shown here (though, still, kill'ing the postmaster
> > isn't exactly the same as what your patch would be doing, so I don't
> > really like using this particular analysis to answer this question...).
>
> I agree that using an immediate checkpoint is the best for reducing
> recovery time. IMO reducing the amount of WAL to recover by up to 50%
> from doing no checkpoint at all is also a reasonable use case,
> especially if avoiding an IO spike is important.

Checkpoints are always happening though, that's kind of my point..?
Sure, you get lucky sometimes that the time you snapshot might have less
outstanding WAL than at some other time, but I'm not convinced that this
patch is really going to give a given user that much reduced amount of
WAL that has to be replayed more than just randomly timing the
snapshot, and if it's not clearly better, always, then I don't think we
can reasonably document it as such or imply that this is how folks
should implement snapshot-based backups.

> > If the use-case here was just that you wanted to add more options to the
> > CHECKPOINT command because we have them internally and maybe they'd be
> > useful to expose then these things probably wouldn't matter so much, but
> > to imply that this is really going to cut down on the amount of WAL
> > replay required a lot isn't really coming through even with these
> > results.
>
> This is how I initially presented this patch. I only included this
> use case because I was asked for concrete examples of how it might be
> useful.

Did you have other concrete examples that we could reference as to when
it would be useful to use these options?

Thanks,

Stephen