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