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

On Thu, Apr 4, 2019 at 10:07 PM Julien Rouhaud <rjuju123(at)gmail(dot)com> wrote:
>
> On Thu, Apr 4, 2019 at 1:23 PM Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> wrote:
> >
> > On Thu, Apr 4, 2019 at 1:26 PM Tsunakawa, Takayuki
> > <tsunakawa(dot)takay(at)jp(dot)fujitsu(dot)com> wrote:
> > >
> > > From: Fujii Masao [mailto:masao(dot)fujii(at)gmail(dot)com]
> > > > reloption for TOAST is also required?
> > >
> > > # I've come back to the office earlier than planned...
> > >
> > > Hm, there's no reason to not provide toast.vacuum_shrink_enabled. Done with the attached patch.
> > >
> >
> > Thank you for updating the patch!
>
> +1!
>
> > + <term><literal>vacuum_shrink_enabled</literal>,
> > <literal>toast.vacuum_shrink_enabled</literal>
> > (<type>boolean</type>)</term>
> > + <listitem>
> > + <para>
> > + Enables or disables shrinking the table when it's vacuumed.
> > + This also applies to autovacuum.
> > + The default is true. If true, VACUUM frees empty pages at the
> > end of the table.
> >
> > "VACUUM" needs <command> or "vacuum" is more appropriate here?
>
> also, the documentation should point out that freeing is not
> guaranteed. Something like
>
> + The default is true. If true, VACUUM will try to free empty
> pages at the end of the table.

+1

>
> > I'm not sure the consensus we got here but we don't make the vacuum
> > command option for this?
>
> I don't think here's a clear consensus, but my personal vote is to add
> it, with SHRINK_TABLE = [ force_on | force_off | default ] (unless a
> better proposal has been made already)

As INDEX_CLEANUP option has been added by commit a96c41f, the new
option for this feature could also accept zero or one boolean
argument, that is SHRINK_TABLE [true|false] and true by default.
Explicit options on VACUUM command overwrite options set by
reloptions. And if the boolean argument is omitted the option depends
on the reloptions.

FWIW, I also would like to defer to committer on the naming new
option but an another possible comment on that could be that the term
'truncate' might be more suitable rather than 'shrink' in the context
of lazy vacuum. As Tsunakawa-san mentioned the term 'shrink' is used
in PostgreSQL documentation but we use it mostly in the context of
VACUUM FULL. I found two paragraphs that use the term 'shrink'.

vacuum.sgml:
<para>
The <option>FULL</option> option is not recommended for routine use,
but might be useful in special cases. An example is when you have deleted
or updated most of the rows in a table and would like the table to
physically shrink to occupy less disk space and allow faster table
scans. <command>VACUUM FULL</command> will usually shrink the table
more than a plain <command>VACUUM</command> would.
</para>

maintenance.sgml
Although <command>VACUUM FULL</command> can be used to shrink a table back
to its minimum size and return the disk space to the operating system,
there is not much point in this if the table will just grow again in the
future. Thus, moderately-frequent standard
<command>VACUUM</command> runs are a
better approach than infrequent <command>VACUUM FULL</command> runs for
maintaining heavily-updated tables.

On the other hand, we use the term 'truncate' in the progress
reporting of lazy vacuum (see documentation of
pg_stat_progress_vacuum). So I'm concerned that if we use the term
'shrink' users will think that this option prevents VACUUM FULL from
working.

Regards,

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