Re: VACUUM's ancillary tasks

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%3D1zGu5OshfzxKBqDmxxKcoDJu4pJux8UAo5h7k%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. IIRC there was an attempt late in the 9.5 cycle, but Bruce
(IIRC) ran out of steam. And nobody picked it up again ... :(

Greetings,

Andres Freund