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 | timestamp with 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 <br...@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers