Re: Berserk Autovacuum (let's save next Mandrill)

Hi,

> > Why not select a table that has inserts, updates and deletes for
autovacuum just like we do for autoanalyze, not only deletes and updates
like we do now?

> >
> > Sounds like a good idea, although I do agree with Alvaro when he
> > mentions that it would be good to only invoke a worker that was only
> > going to freeze tuples and not look at the indexes.
>
> The invoking autovacuum on table based on inserts, not only deletes
> and updates, seems good idea to me. But in this case, I think that we
> can not only freeze tuples but also update visibility map even when
> setting all-visible. Roughly speaking I think vacuum does the
> following operations.
>
> 1. heap vacuum

2. HOT pruning
>
Is it worth skipping it if we're writing a page anyway for the sake of hint
bits and new xids? This will all be no-op anyway on append-only tables and
happen only when we actually need something?

> 3. freezing tuples
> 4. updating visibility map (all-visible and all-frozen)
>
These two are needed, and current autovacuum launch process does not take
into account that this is also needed for non-dead tuples.

> 5. index vacuum/cleanup
>
There is a separate patch for that. But, since
https://commitfest.postgresql.org/16/952/ for almost a year already
Postgres skips index cleanup on tables without new dead tuples, so this
case is taken care of already?

> 6. truncation
>
This shouldn't be a heavy operation?

>
> With the proposed patch[1] we can control to do 5 or not. In addition
> to that, another proposed patch[2] allows us to control 6.
>
> For append-only tables (and similar tables), what we periodically want
> to do would be 3 and 4 (possibly we can do 2 as well). So maybe we
> need to have both an option of (auto)vacuum to control whether to do 1
> and something like a new autovacuum threshold (or an option) to invoke
> the vacuum that disables 1, 5 and 6. The vacuum that does only 2, 3
> and 4 would be much cheaper than today's vacuum and anti-wraparound
> vacuum would be able to skip almost pages.
>

Why will we want to get rid of 1? It's a noop from write perspective and
saves a scan to do it if it's not noop.

Why make it faster in emergency situations when situation can be made
non-emergency from the very beginning instead?

>
> [1] https://commitfest.postgresql.org/22/1817/
> [2] https://commitfest.postgresql.org/22/1981/
>
> Regards,
>
> --
> Masahiko Sawada
> NIPPON TELEGRAPH AND TELEPHONE CORPORATION
> NTT Open Source Software Center
>

--
Darafei Praliaskouski
Support me: http://patreon.com/komzpa