Re: A few new options for CHECKPOINT

Greetings,

* Bossart, Nathan (bossartn(at)amazon(dot)com) wrote:
> Thanks to all for the feedback. I've attached v2 of the patch. I've
> removed the WAIT and FORCE options and renamed IMMEDIATE to FAST.
>
> On 11/25/20, 7:52 AM, "Stephen Frost" <sfrost(at)snowman(dot)net> wrote:
> > I'm a bit confused by the idea here.. The whole point of running a
> > CHECKPOINT is to get the immediate behavior with the IO spike to get
> > things flushed out to disk so that, on crash recovery, there's less
> > outstanding WAL to replay.
> >
> > Avoiding the IO spike implies that you're waiting for a regular
> > checkpoint and that additional WAL is building up since that started and
> > therefore you're going to have to replay that WAL during crash recovery
> > and so you won't end up reducing your recovery time, so I'm failing to
> > see the point..? I don't think you really get to have both.. pay the
> > price at backup time, or pay it during crash recovery.
>
> It's true that you might not lower recovery time as much as if you did
> an immediate checkpoint, but presumably there can still be some
> benefit from doing a non-immediate checkpoint. I think a similar
> argument can be made for pg_start_backup(), which AFAICT is presently
> the only way to manually request a non-immediate checkpoint.

If there isn't any actual outstanding WAL since the last checkpoint, the
only time that the fact that pg_start_backup includes CHECKPOINT_FORCE
is relevant, then performing a checkpoint isn't going to actually reduce
your crash recovery.

Also note that, in all other cases (that is, when there *is* outstanding
WAL since the last checkpoint), pg_start_backup actually just waits for
the existing checkpoint to complete- and while it's waiting for that to
happen, there'll be additional WAL building up since that checkpoint
started that will have to be replayed as part of crash recovery, just as
if you took a snapshot of the system at any other time.

So, either there won't be any WAL outstanding, in which case running a
CHECKPOINT FORCE just ends up creating more WAL without actually being
useful, or there's WAL outstanding and the only thing this does is delay
the snapshot being taken but doesn't actually reduce the amount of WAL
that's going to end up being outstanding and which will have to be
replayed during crash recovery.

Maybe there's a useful reason to have these options, but at least the
stated one isn't it and I wouldn't want to encourage people who are
using snapshot-based backups to use these options since they aren't
going to work the way that this thread is implying they would.

Thanks,

Stephen