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

On Mon, Dec 23, 2024 at 12:50 PM Melanie Plageman
<melanieplageman(at)gmail(dot)com> wrote:
>
> The other "worst case" is just that you always scan and fail to freeze
> an extra 3% of the relation while vacuuming the table. This one is
> much easier to achieve. As such, it seems worthwhile to add a GUC and
> table option to tune the EAGER_SCAN_MAX_FAILS_PER_REGION such that you
> can disable eager scanning altogether (or increase or decrease how
> aggressive it is).

Attached v5 adds the GUC and table storage option controlling the
maximum number of eager scan failures tolerated for each region of the
table.

0001 is a version of a docs patch proposed in [1]. It adds a new
Vacuuming subsection of the Server Configuration docs where the
proposed GUC in this patch is added in 0003.

While adding the table storage option and GUC, I struggled a bit with
where in the code to actually determine the final value of
vacuum_eager_scan_max_fails.

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.

- Melanie

[1] https://www.postgresql.org/message-id/CAAKRu_aQUOaMYrcjNuXeSkJtaX9oRUzKP57bsYbC0gVVWS%2BcbA%40mail.gmail.com