Re: Minimum tuple threshold to decide last pass of VACUUM

On 2 August 2015 at 13:13, Michael Paquier <michael(dot)paquier(at)gmail(dot)com>
wrote:

> Hi all,
>
> Commit 4046e58c (dated of 2001) has introduced the following comment
> in vacuumlazy.c:
> + /* If any tuples need to be deleted, perform final vacuum cycle */
> + /* XXX put a threshold on min nuber of tuples here? */
> + if (vacrelstats->num_dead_tuples > 0)
> In short, we may want to have a reloption to decide if we do or not
> the last pass of VACUUM or not depending on a given number of
> remaining tuples. Is this still something we would like to have?
>

I don't think we want a new user parameter, but we should have an internal
limit with a heuristic, similar to how we decide whether to truncate.

I would suggest this internal logic...

* If its a VACUUM FREEZE then index_scan_threshold = 0, i.e. always scan if
needed, since the user is requesting maximum vacuum

* For emergency anti-wraparound VACUUMs we shouldn't scan indexes at all,
since they aren't critical path activities at that point

* For normal VACUUMs we should scan indexes only if (num_dead_tuples * 20)
> (blocks to be scanned in any one index), which allows some index bloat
but not much

--
Simon Riggs http://www.2ndQuadrant.com/
<http://www.2ndquadrant.com/>
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services