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

On Wed, Jan 22, 2025 at 5:48 PM Melanie Plageman
<melanieplageman(at)gmail(dot)com> wrote:
> Circling back to benchmarking, I've been running the most adversarial
> benchmarks I could devise and can share a bit of what I've found.
>
> I created a "hot tail" benchmark where 16 clients insert some data and
> then update some data older than what they just inserted but still
> towards the end of the relation. The adversarial part is that I bulk
> delete all the data older than X hours where X hours is always after
> the data is eligible to be frozen but before it would be aggressively
> vacuumed.
>
> That means that there are a bunch of pages that will never be frozen
> on master but are frozen with the patch -- wasting vacuum resources. I
> tuned vacuum_freeze_min_age and vacuum_freeze_table_age and picked the
> DELETE window to specifically have this behavior.
>
> With my patch, I do see a 15-20% increase in the total time spent
> vacuuming over the course of the multi-hour benchmark. (I only see a
> 1% increase in the total WAL volume, though.)

How much time is that in absolute terms? If the benchmark runs for 3
hours and during that time we have 1 autovacuum worker active for 30
minutes out of those 3 hours, that is different than if we have 5
autovacuum workers active nearly all the time. Or, maybe a clearer way
to put it, what percentage of the total work was the VACUUM work? If
the total work was $100 and the VACUUM work accounted for $80 of that,
then a 15-20% increase is pretty significant; if the total work was
$100 and the VACUUM work accounted for $5 of that, then a 15-20%
increase matters a lot less.

But tentatively I'm inclined to say this is fine. Some of the work
that VACUUM is doing is probably work that otherwise would have needed
to happen in the foreground. For instance, the speedup in DELETEs that
you observed might be not only because the pages are cached but
perhaps also because DELETE doesn't need to do any non-trivial
visibility checks. Also, users don't have to anti-optimize their
configuration settings for their workload as you did when constructing
the adversarial case.

--
Robert Haas
EDB: http://www.enterprisedb.com