On Tue, Sep 17, 2013 at 7:10 PM, Andres Freund <and...@2ndquadrant.com> wrote: > I generally think the current logic for triggering VACUUMs via > autovacuum doesn't really make all that much sense in the days where we > have the visibility map.
Right now, whether or not to autovacuum is the rest of a two-pronged test. The first prong is based on number of updates and deletes relative to table size; that triggers a regular autovacuum. The second prong is based on age(relfrozenxid) and triggers a non-page-skipping vacuum (colloquially, an anti-wraparound vacuum). The typical case in which this doesn't work out well is when the table has a lot of inserts but few or no updates and deletes. So I propose that we change the first prong to count inserts as well as updates and deletes when deciding whether it needs to vacuum the table. We already use that calculation to decide whether to auto-analyze, so it wouldn't be very novel. We know that the work of marking pages all-visible will need to be done at some point, and doing it sooner will result in doing it in smaller batches, which seems generally good. However, I do have one concern: it might lead to excessive index-vacuuming. Right now, we skip the index vac step only if there ZERO dead tuples are found during the heap scan. Even one dead tuple (or line pointer) will cause an index vac cycle, which may easily be excessive. So I further propose that we introduce a threshold for index-vac; so that we only do index vac cycle if the number of dead tuples exceeds, say 0.1% of the table size. Thoughts? Let the hurling of rotten tomatoes begin. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers