Hi David, On Thu, May 1, 2025 at 2:15 PM David Rowley <dgrowle...@gmail.com> wrote: > > On Thu, 1 May 2025 at 17:35, Junwang Zhao <zhjw...@gmail.com> wrote: > > > > On Thu, May 1, 2025 at 8:12 AM David Rowley <dgrowle...@gmail.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
v2-0002-skip-vacuuming-the-table-if-last-time-vacuum-perc.patch
Description: Binary data
v2-0001-Introduce-autovacuum-vacuum-strategy.patch
Description: Binary data
