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

Hi Melanie

On Wed, 30 Oct 2024 at 21:42, Melanie Plageman <melanieplageman(at)gmail(dot)com>
wrote:

> ...
>
The number of pages set all-frozen in the visibility map by a given
> vacuum can be useful when analyzing which normal vacuums reduce the
> number of pages future aggressive vacuum need to scan.
>
> Also, empty pages that are set all-frozen in the VM don't show up in
> the count of pages with newly frozen tuples. When making sense of the
> result of visibilitymap_summary() for a relation, it's helpful to know
> how many pages were set all-frozen in the VM by each vacuum.
>
> I agree that this data would be useful for analysing the impact of vacuum
operations.

The values returned in a case pages are removed (cases where the empty
pages are at the end of the table) are a bit confusing.

In an example similar to yours, but with a normal vacuum operation, since
that seems to be the most useful case for this feature:

CREATE TABLE the_table (first int, second int) WITH (autovacuum_enabled =
false);
INSERT INTO the_table SELECT generate_series(1,1000), 1;
DELETE FROM the_table WHERE first > 50;
VACUUM (VERBOSE) the_table;

pages: 4 removed, 1 remain, 5 scanned (100.00% of total)
tuples: 950 removed, 50 remain, 0 are dead but not yet removable
removable cutoff: 763, which was 1 XIDs old when operation ended
new relfrozenxid: 761, which is 1 XIDs ahead of previous value
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen. 4 pages
set all-frozen in the VM

It looks like 4 pages out of 1 are set all-frozen. So there are -3 to scan
for the next aggressive vacuum? The four pages which were set to all frozen
are the same four which have been removed from the end of the table.

For an example where the empty pages which are marked all frozen are at the
start of the table (deleting values < 950 in the example), the results are
more intuitive

pages: 0 removed, 5 remain, 5 scanned (100.00% of total)
tuples: 949 removed, 51 remain, 0 are dead but not yet removable
removable cutoff: 768, which was 0 XIDs old when operation ended
new relfrozenxid: 766, which is 1 XIDs ahead of previous value
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen. 4 pages
set all-frozen in the VM

Can the pages which are removed from the end of the table not be counted
towards those set all frozen to make the arithmetic a bit more intuitive
for this edge case?

Thanks
Alastair