Re: New criteria for autovacuum

On Thu, Apr 3, 2025 at 4:37 PM Sami Imseih <samimseih(at)gmail(dot)com> wrote:
>
> From what I can tell in your example, you ran the manual vacuum ( session 1)
> while you had an open transaction (session 2), so vacuum could not remove
> the dead tuples or update the visibility map. Once you committed session 2,
> autovacuum came in and did its job after the autovacuum_naptime passed
> (default 1 minute). Checkpoint does not update the visibility map, only
> vacuum does.
>
> IMO, I don't think we need this patch for vacuum, as simply making sure
> autovacuum runs more frequently on the table that is accessed via
> index-only scans often is a way to deal with this already, i.e
> lowering autovacuum_vacuum_scale_factor. Maybe others have
> a different opinion?

Yea, I mean if you have a table that isn't meeting the threshold for
being vacuumed by autovacuum based on inserted/modified tuples that
you are querying a lot, then you should probably change the autovacuum
table storage options for that table.

I don't fully understand if the example provided here is in that
situation (i.e. autovac not being triggered because they were below
the thresholds).

Historically, we haven't updated the VM when doing on-access pruning
because we wanted vacuum to have to vacuum those pages and freeze them
before an aggressive vacuum was required. This release, in
052026c9b903, I added functionality to vacuum to scan more all-visible
pages during regular vacuums. I think this enables us to update the VM
during on-access pruning. This is something I plan to work on in 19.
It seems like it would alleviate situations like this.

> As far as extra metrics go for the scenario in which and index only scan must
> visit a table, pg_stat_all_indexes and pg_stat_all_tables do have a
> idx_tup_fetch
> counter which increases anytime an index scan visits the table, i.e.
> index-only scan
> with heap fetches or a regular index scan. I think having a counter
> specifically for
> heap fetches due to index-only scans could be valuable.

What do you imagine using the heap fetches during index-only scans counter for?

- Melanie