Re: Eagerly scan all-visible pages to amortize aggressive vacuum

Hi!

On 14.01.2025 00:46, Melanie Plageman wrote:
> On Thu, Jan 9, 2025 at 1:24 PM Andres Freund<andres(at)anarazel(dot)de> wrote:
>> On 2025-01-07 15:46:26 -0500, Melanie Plageman wrote:
>>> For table storage options, those related to vacuum but not autovacuum
>>> are in the main StdRdOptions struct. Of those, some are overridden by
>>> VACUUM command parameters which are parsed out into the VacuumParams
>>> struct. Though the members of VacuumParams are initialized in
>>> ExecVacuum(), the storage parameter overrides are determined in
>>> vacuum_rel() and the final value goes in the VacuumParams struct which
>>> is passed all the way through to heap_vacuum_rel().
>>>
>>> Because VacuumParams is what ultimately gets passed down to the
>>> table-AM specific vacuum implementation, autovacuum also initializes
>>> its own instance of VacuumParams in the autovac_table struct in
>>> table_recheck_autovac() (even though no VACUUM command parameters can
>>> affect autovacuum). These are overridden in vacuum_rel() as well.
>>>
>>> Ultimately vacuum_eager_scan_max_fails is a bit different from the
>>> existing members of VacuumParams and StdRdOptions. It is a GUC and a
>>> table storage option but not a SQL command parameter -- and both the
>>> GUC and the table storage parameter affect both vacuum and autovacuum.
>>> And it doesn't need to be initialized in different ways for autovacuum
>>> and vacuum. In the end, I decided to follow the existing conventions
>>> as closely as I could.
>> I think that's fine. The abstractions in this area aren't exactly perfect, and
>> I don't think this makes it worse in any meaningful way. It's not really
>> different from having other heap-specific params like freeze_min_age in
>> VacuumParams.
> Got it. I've left it as is, then.
>
> Attached v6 is rebased over recent changes in the vacuum-related docs.
> I've also updated the "Routine Vacuuming" section of the docs to
> mention eager scanning.
>
> I'm planning to commit 0001 (which updates the code comment at the top
> of vacuumlazy.c to explain heap vacuuming) --barring any objections.
>

Thank you for working on this patch, without this explanation it is
difficult to understand what is happening, to put it mildly.

While working on the vacuum statistics, I'll add you a link to the
thread just in case [0], I discovered some more important points that I
think can be mentioned.

The first of them is related to the fact that vacuum will not clean
tuples referenced in indexes, since it was previously unable to take a
cleanup lock on the index. You can look at the increment of
missed_dead_tuples and vacrel->missed_dead_pages in the
lazy_scan_noprune function. That is, these are absolutely dead tuples
for vacuum that it simply could not clean.

Secondly, I think it is worth mentioning the moment when vacuum urgently
starts cleaning the heap relationship when there is a threat of a
wraparound round. At this point, it skips the index processing phase and
heap relationship truncation.

Thirdly, FreeSpaceMap is updated every time after the complete
completion of index and table cleaning (after the lazy_vacuum function)
and after table heap pruning stage (the lazy_scan_prune function). Maybe
you should add it.

I think it is possible to add additional information about parallel
vacuum - firstly, workers are generated for each index, which perform
their cleaning. Some indexes are defined by vacuum as unsafe for
processing by a parallel worker and can be processed only by a
postmaster (or leader). These are indexes that do not support parallel
bulk-deletion, parallel cleanup (see
parallel_vacuum_index_is_parallel_safe function).

I noticed an interesting point, but I don’t know if it is necessary to
write about it, but for me it was not obvious and informative that the
buffer and wal statistics are thrown by the indexes that were processed
by workers and are thrown separately in (pvs->buffer_usage, pvs->wal_usage).

[0] https://commitfest.postgresql.org/51/5012/

--
Regards,
Alena Rybakina
Postgres Professional