Re: reloption to prevent VACUUM from truncating empty pages at the end of relation

On Sat, Mar 2, 2019 at 4:34 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> Andrew Dunstan <andrew(dot)dunstan(at)2ndquadrant(dot)com> writes:
> > On 3/1/19 2:14 PM, Tom Lane wrote:
> >> Indeed, but I'm not sure that the use-cases are the same. In particular,
> >> unless somebody has done some rather impossible magic, it would be
> >> disastrous to apply DISABLE_INDEX_CLEANUP as a reloption, because then
> >> it would be persistent and you'd never get a real vacuum operation and
> >> soon your disk would be full. Permanently applying truncation disabling
> >> seems less insane.
>
> > You could allow an explicitly set command option to override the reloption.
> > It's important for us to be able to control the vacuum phases more. In
> > particular, the index cleanup phase can have significant system impact
> > but often doesn't need to be done immediately.
>
> I'm not objecting to having a manual command option to skip index cleanup
> (which basically reduces to "do nothing but tuple freezing", right?

DISABLE_INDEX_CLEANUP option does freezing tuples, HOT-pruning and
mark tuples as dead but skips removing tuples, index vacuuming and
index cleanup.

> maybe it should be named/documented that way). Applying it as a reloption
> seems like a foot-gun, though.

FWIW, I agree that we have options for vacuum as vacuum
command options. But for reloptions, I think if the persistence the
setting could be problematic we should not. According to the
discussions so far, I think VACUUM_SHRINK_ENABLED is the one option
that can be available as both vacuum command option and reloptions.
But I'm not sure there is good use case even if we can set
DISABLE_INDEX_CLEANUP as reloptions.

Regards,

--
Masahiko Sawada
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center