Re: Introduce some randomness to autovacuum

Hi David,

On Thu, May 1, 2025 at 2:15 PM David Rowley <dgrowleyml(at)gmail(dot)com> wrote:
>
> On Thu, 1 May 2025 at 17:35, Junwang Zhao <zhjwpku(at)gmail(dot)com> wrote:
> >
> > On Thu, May 1, 2025 at 8:12 AM David Rowley <dgrowleyml(at)gmail(dot)com> wrote:
> > > It sounds like the aim is to fix the problem with autovacuum vacuuming
> > > the same table over and over and being unable to remove enough dead
> > > tuples due to something holding back the oldest xmin horizon. Why
> > > can't we just fix that by remembering the value that
> > > VacuumCutoffs.OldestXmin and only coming back to that table once
> > > that's moved forward some amount?
> >
> > Users expect the tables to be auto vacuumed when:
> > *dead_tuples > vac_base_thresh + vac_scale_factor * reltuples*
> > If we depend on xid moving forward to do autovacuum, I think
> > there are chances some bloated tables won't be vacuumed?
>
> Can you explain why you think that? The idea is to start vacuum other
> tables that perhaps can have dead tuples removed instead of repeating
> vacuums on the same table over and over without any chance of being
> able to remove any more dead tuples than we could during the last
> vacuum.

Sorry, I must have misinterpreted this sentence
**by remembering the value that VacuumCutoffs.OldestXmin**.

How about reporting the number of dead tuples removed by
the last autovacuum and using that statistic to determine whether
to vacuum the table in the next round?

I can think of a simple algorithm like this:

If the last autovacuum removed fewer than 10% of the dead tuples,
then skip vacuuming this table for a while. Numbers used are chosen
arbitrarily, and this is patch v2-0002.

v2-0001 is the same as the last email.

>
> David

--
Regards
Junwang Zhao