On 3/4/19, 1:44 PM, "Andres Freund" <and...@anarazel.de> wrote: > On 2019-03-04 21:40:53 +0000, Bossart, Nathan wrote: >> On 3/4/19, 12:11 PM, "Andres Freund" <and...@anarazel.de> wrote: >> > I'm not quite convinced this is right. There's plenty sites that >> > practically can't use autovacuum because it might decide to vacuum the >> > 5TB index because of 300 dead tuples in the middle of busy periods. And >> > without an reloption that's not controllable. >> >> Wouldn't it be better to adjust the cost and threshold parameters or >> to manually vacuum during quieter periods? > > No. (auto)vacuum is useful to reclaim space etc. It's just the > unnecessary index cleanup that's the problem... Most of the space can > be reclaimed after all, the item pointer ain't that big...
I see what you mean. >> I suppose setting DISABLE_INDEX_CLEANUP on a relation during busy >> periods could be useful if you really need to continue reclaiming >> transaction IDs, but that seems like an easy way to accidentally never >> vacuum indexes. > > Yea, I do think that's a danger. But we allow disabling autovacuum, so > I'm not sure it matters that much... And for indexes you'd still have > the index page-level vacuum that'd continue to work. I think the difference here is that there isn't something like autovacuum_freeze_max_age to force index cleanup at some point. Granted, you can set autovacuum_freeze_max_age to 2B if you want, but at least there's a fallback available. Nathan
