On Monday, 29 August 2016, Andres Freund <
andres@anarazel.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%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.
+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
I've updated this patch to apply to current HEAD, can propose it to pg10.
Regards,
--
Masahiko Sawada
--
Regards,
--
Masahiko Sawada