Re: Count and log pages set all-frozen by vacuum

On Tue, Nov 26, 2024 at 1:55 PM Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> wrote:
>
> Just to be clear, do users want the number of updated VM bits or the
> number of pages whose visibility information is updated? For example,
>
> > visibility map: 5 pages set all-visible, 4 pages set all-frozen.
>
> IIUC the above log can be interpreted in two ways in terms of the
> number of pages:
>
> (a) 5 pages are marked as all-visible, and other 4
> (already-marked-as-all-visible) pages are marked as all-frozen. That
> is, 9 VM bits for 9 pages in total got updated.
> (b) 1 page is marked as all-visible, and other 4 pages are marked as
> all-frozen (and all-visible as well). That is, 9 VM bits for 5 pages
> in total got updated.
>
> If users want to know "how many VM bits were updated?", the above log
> makes sense. But there is no clear answer to "How many pages were
> updated in terms of VM?".

Ah, good point. With a spin on the earlier example:

create table foo (a int, b int) with (autovacuum_enabled = false);
insert into foo select generate_series(1,1000), 1;
delete from foo where a > 500;

vacuum (verbose) foo;
visibility map: 5 pages set all-visible, 2 pages set all-frozen.

5 pages were set all-visible and, of those, 2 were set all-frozen. So,
3 were set only all-visible. This is like (b) in your description.

However, now if we do:

vacuum (verbose, freeze) foo;
visibility map: 0 pages set all-visible, 3 pages set all-frozen.

Here, 3 already all-visible pages were set all-frozen.
This does currently tell you the number of bits newly set, not the
number of pages' whose VM status changed state.

In fact, you could have a case where it is even more difficult to tell
the total number of pages' whose VM status was updated. Let's say the
first vacuum sets 5 pages newly all-visible, and of those, 2 are set
all-frozen. Separately, 2 all-visible pages elsewhere in the relation
are scanned (say due to SKIP_PAGES_THRESHOLD) and are old enough to
require freezing. The message would be:

visibility map: 5 pages set all-visible, 4 pages set all-frozen.

But, we know 2 pages were set all-visible and all-frozen, 3 were set
only all-visible, and 2 all-visible pages were set all-frozen. That's
seven pages changing state. You would have no idea how many total
pages changed state from the log message.

So, since the transitions that are possible here are:
nothing -> all-visible
nothing -> all-visible and all-frozen
all-visible -> all-visible and all-frozen

What if we changed the message to reflect these state changes:

visibility map: 5 pages newly set all-visible, of which 2 set
all-frozen. 2 all-visible pages newly set all-frozen.

- Melanie