Re: log_autovacuum in Postgres 14 -- ordering issue

On Wed, Aug 25, 2021 at 5:23 PM Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> wrote:
> > The question of whether or not we do an index scan (i.e. index
> > vacuuming) depends entirely on the number of LP_DEAD items that heap
> > pruning left behind in the table structure. [...]
>
> Ooh, this was illuminating -- thanks for explaining. TBH I would have
> been very confused if asked to explain what that log line meant; and now
> that I know what it means, I am even more convinced that we need to work
> harder at it :-)

The way that VACUUM and ANALYZE do dead tuple accounting is very
confusing. In fact, it's so confusing that even autovacuum can get
confused! I think that we need to treat LP_DEAD items and pruned
tuples even more differently than we do in Postgres 14, probably in a
number of different areas (not just VACUUM).

I've found that if I set autovacuum_vacuum_scale_factor and
autovacuum_analyze_scale_factor to 0.02 with a HOT-heavy workload
(almost stock pgbench), then autovacuum workers are launched almost
constantly. If I then increase autovacuum_vacuum_scale_factor to 0.05,
but make no other changes, then the system decides that it should
actually never launch an autovacuum worker, even once (except for
anti-wraparound purposes) [1]. This behavior is completely absurd, of
course. To me this scenario illustrates an important general point:
VACUUM has the wrong idea. At least when it comes to certain specific
details. Details that have plenty of real world relevance.

VACUUM currently fails to understand anything about the rate of change
-- which, as I've said, is often the most important thing in the real
world. That's what my absurd scenario seems to show. That's how I view
a lot of these things.

> I'll see if I can come up with something ...

Thanks.

The message itself probably does need some work. But documentation
seems at least as important. It's slightly daunting, honestly, because
we don't even document HOT itself (unless you count passing references
that don't even explain the basic idea). I did try to get people
interested in this stuff at one point not too long ago [2]. That
thread went an entirely different direction to the one I'd planned on,
though, so I became discouraged. I should pick it up again now,
though.

[1] https://postgr.es/m/CAH2-Wz=sJm3tm+FpXbyBhEhX5tbz1trQrhG6eOhYk4-+5uL=ww@mail.gmail.com
[2] https://postgr.es/m/CAH2-WzkjU+NiBskZunBDpz6trSe+aQvuPAe+xgM8ZvoB4wQwhA@mail.gmail.com
--
Peter Geoghegan