Re: finding changed blocks using WAL scanning

On Thu, Apr 18, 2019 at 04:25:24PM -0400, Robert Haas wrote:
>On Thu, Apr 18, 2019 at 3:51 PM Bruce Momjian <bruce(at)momjian(dot)us> wrote:
>> How would you choose the STARTLSN/ENDLSN? If you could do it per
>> checkpoint, rather than per-WAL, I think that would be great.
>
>I thought of that too. It seems appealing, because you probably only
>really care whether a particular block was modified between one
>checkpoint and the next, not exactly when during that interval it was
>modified.

That's probably true for incremental backups, but there are other use
cases that could leverage this information.

Some time ago there was a discussion about prefetching blocks during
recovery on a standby, and that's a great example of a use case that
benefit from this - look which blocks where modified in the next chunk
of WAL, prefetch them. But that requires fairly detailed information
about which blocks were modified in the next few megabytes of WAL.

So just doing it once per checkpoint (or even anything above a single
WAL segment) and removing all the detailed LSN location makes it useless
for this use case.

regards

--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services