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

On 2019-Feb-28, Tsunakawa, Takayuki wrote:

> From: Michael Paquier [mailto:michael(at)paquier(dot)xyz]
> > So we could you consider adding an option for the VACUUM command as well
> > as vacuumdb? The interactions with the current patch is that you need to
> > define the behavior at the beginning of vacuum for a given heap, instead
> > of reading the parameter at the time the truncation happens, and give
>
> I'm not confident whether this is the same as the above, I imagined this:
>
> * Add a new USERSET GUC vacuum_shrink_table = {on | off}, on by default.
> This follows the naming style "verb_object" like log_connections and enable_xxx. We may want to use enable_vacuum_shrink or something like that, but enable_xxx seems to be used solely for planner control. Plus, vacuum-related parameters seem to start with vacuum_.

Robert used the phrase "attractive nuisance", which maybe sounds like a
good thing to have to a non native speaker, but it actually isn't -- he
was saying we should avoid a GUC at all, and I can see the reason for
that. I think we should have a VACUUM option and a reloption, but no
GUC. The default should always be to shrink, unless either the VACUUM
option or the reloption turn that off. (So it doesn't make sense to set
either the VACUUM option or the reloption to "on").

Disclaimer: I did write roughly the same patch using both a GUC and a
VACUUM option, though I named my GUC truncate_on_vacuum and the VACUUM
option "truncate_anyway" (so you can turn truncation off globally, then
enable it selectively at manual vacuum execution time, but not
autovacuum). However, the reason for doing this were concerns about
robustness caused by data corruption induced by failing to truncate
pages containing removed tuples ... not performance improvement, as your
patch. So they wanted to turn off truncation for *all* tables, not just
a select few.

Hopefully we'll get Tom's patch that addresses the failure-to-truncate
issues in pg12.

--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services