On Fri, Sep 6, 2013 at 01:01:59PM -0400, Bruce Momjian wrote:
> This December 2012 thread by Andrew Dunstan shows he wasn't aware that a
> manual VACUUM was required for index-only scans. That thread ended with
> us realizing that pg_upgrade's ANALYZE runs will populate
> pg_class.relallvisible.
>
> What I didn't see in that thread is an analysis of what cases are going
> to require manual vacuum, and I have seen no work in 9.3 to improve
> that. I don't even see it on the TODO list.
OK, let's start the discussion then. I have added a TODO list:
Improve setting of visibility map bits for read-only and insert-only workloads
So, what should trigger an auto-vacuum vacuum for these workloads?
Rather than activity, which is what normally drives autovacuum, it is
lack of activity that should drive it, combined with a high VM cleared
bit percentage.
It seems we can use these statistics values:
n_tup_ins | bigint n_tup_upd | bigint n_tup_del |
bigint n_tup_hot_upd | bigint n_live_tup | bigint
n_dead_tup | bigint n_mod_since_analyze | bigint last_vacuum |
timestampwith time zone last_autovacuum | timestamp with time zone
Particilarly last_vacuum and last_autovacuum can tell us the last time
of vacuum. If the n_tup_upd/n_tup_del counts are low, and the VM set
bit count is low, it might need vacuuming, though inserts into existing
pages would complicate that.
-- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB
http://enterprisedb.com
+ It's impossible for everything to be true. +