VACUUM's ancillary tasks

On Monday, 29 August 2016, Andres Freund <andres(at)anarazel(dot)de
<javascript:_e(%7B%7D,'cvml','andres(at)anarazel(dot)de');>> wrote:

> Hi,
>
> On 2016-08-29 03:26:06 +0200, Vik Fearing wrote:
> > The attached two patches scratch two itches I've been having for a
> > while. I'm attaching them together because the second depends on the
> first.
> >
> > Both deal with the fact that [auto]vacuum has taken on more roles than
> > its original purpose.
> >
> >
> > Patch One: autovacuum insert-heavy tables
> >
> > If you have a table that mostly receives INSERTs, it will never get
> > vacuumed because there are no (or few) dead rows. I have added an
> > "inserts_since_vacuum" field to PgStat_StatTabEntry which works exactly
> > the same way as "changes_since_analyze" does.
> >
> > The reason such a table needs to be vacuumed is currently twofold: the
> > visibility map is not updated, slowing down index-only scans; and BRIN
> > indexes are not maintained, rendering them basically useless.
>
> It might be worthwhile to look at
> http://archives.postgresql.org/message-id/CAMkU%3D1zGu5Oshfz
> xKBqDmxxKcoDJu4pJux8UAo5h7k%2BGA_jS3Q%40mail.gmail.com
> there's definitely some overlap.
>
>
> > Patch Two: autovacuum after table rewrites
> >
> > This patch addresses the absurdity that a standard VACUUM is required
> > after a VACUUM FULL because the visibility map gets blown away. This is
> > also the case for CLUSTER and some versions of ALTER TABLE that rewrite
> > the table.
>
> I think this should rather fixed by maintaining the VM during
> cluster.

>
+1

>
> IIRC there was an attempt late in the 9.5 cycle, but Bruce
>
(IIRC) ran out of steam. And nobody picked it up again ... :(
>
>
It may be worth to look at
https://www.postgresql.org/message-id/CAMkU%3D1w3osJJ2FneELhhNRLxfZitDgp9FPHee08NT2FQFmz_pQ%40mail.gmail.com

I've updated this patch to apply to current HEAD, can propose it to pg10.

Regards,

--
Masahiko Sawada

--
Regards,

--
Masahiko Sawada