On Monday, 29 August 2016, Andres Freund <and...@anarazel.de <javascript:_e(%7B%7D,'cvml','and...@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%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
