VACUUM's ancillary tasks

From: Vik Fearing <vik(at)2ndquadrant(dot)fr>
To: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: VACUUM's ancillary tasks
Date: 2016-08-29 01:26:06
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

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 PostgreSQL : Expertise, Formation et Support

Attachment Content-Type Size
autovac_01_v01.patch invalid/octet-stream 12.3 KB
autovac_02_v01.patch invalid/octet-stream 8.5 KB


Browse pgsql-hackers by date

  From Date Subject
Next Message Vik Fearing 2016-08-29 01:34:36 Sample configuration files
Previous Message Jim Nasby 2016-08-29 00:49:33 Re: Why is a newly created index contains the invalid LSN?