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. 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 thought about having those commands do the same work themselves, but it seems better to have them simply trigger autovacuum than quadruplicate the work. I do this by having them fill in the "inserts_since_vacuum" field added in Patch One with the number of rows rewritten. This assumes that autovacuum_vacuum_scale_factor is < 1.0 which hopefully is a safe assumption. While doing this, I noticed that ALTER TABLE should also re-analyze the table for obvious reasons, so I have that one set "changes_since_analyze" to the number of rows rewritten as well. I have not included any kind of test suite here because I don't really have any ideas how to go about it in a sane way. Suggestions welcome. Attention reviewer: Please note that some of the documentation in the first patch gets removed by the second patch, in case they both don't get committed. I have added this to the imminent commitfest. These patches are rebased as of 26fa446. -- Vik Fearing +33 6 46 75 15 36 http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
autovac_01_v01.patch
Description: invalid/octet-stream
autovac_02_v01.patch
Description: invalid/octet-stream
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
