Re: Why is INSERT-driven autovacuuming based on pg_class.reltuples?

On Fri, 28 Jan 2022 at 09:20, Peter Geoghegan <pg(at)bowt(dot)ie> wrote:
> Both VACUUM and ANALYZE update pg_class.reltuples. But this code seems
> to assume that it's only something that VACUUM can ever do.

Like Justin I'm also not quite following what the problem is here.
pg_class.reltuples is only used to estimate how many tuples the scale
factor is likely to be. It does not matter if that was set by ANALYZE
or VACUUM, it's simply an estimate.

I quoted the text above as I get the idea that you've gotten the wrong
end of the stick about how this works. reltuples is just used to
estimate what the number of tuples for the insert threshold is based
on the scale factor. It does not matter if that was estimated by
VACUUM or ANALYZE.

If ANALYZE runs and sets pg_class.reltuples to 1 million, then we
insert 500k tuples, assuming a 0 vacuum_ins_threshold and a
vacuum_ins_scale_factor of 0.2, then we'll want to perform a vacuum as
"vac_ins_base_thresh + vac_ins_scale_factor * reltuples" will come out
at 200k. auto-vacuum will then trigger and update reltuples hopefully
to some value around 1.5 million, then next time it'll take 300k
tuples to trigger an insert vacuum.

I'm not quite following where the problem is with that. (Of course
with the exception of the fact that ANALYZE and VACUUM have different
methods how they decide what to set pg_class.reltuples to. That's not
a new problem)

David