Re: New vacuum option to do only freezing

On Fri, Mar 29, 2019 at 4:39 AM Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>
> On Thu, Mar 28, 2019 at 2:00 AM Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> wrote:
> > The patch adds the basic functionality to disable index cleanup but
> > one possible argument could be whether we should always disable it
> > when anti-wraparound vacuum. As discussed on another thread[1]
> > anti-wraparound vacuum still could lead the I/O burst problem and take
> > a long time, especially for append-only large table. Originally the
> > purpose of this feature is to resolve the problem that vacuum takes a
> > long time even if the table has just a few dead tuples, which is a
> > quite common situation of anti-wraparound vacuum. It might be too late
> > to discuss but if we always disable it when anti-wraparound vacuum
> > then users don't need to do "VACUUM (INDEX_CLEANUP false)" manually on
> > PostgreSQL 12. Dose anyone have opinions?
>
> I think we can respect the configured value of the option even for
> aggressive vacuums, but I don't think we should change aggressive
> vacuums to work that way by default. You are correct that the table
> might have only a few dead tuples, but it might also have a lot of
> dead tuples; I have heard rumors of a PostgreSQL installation that had
> autovacuum = off and non-stop wraparound autovacuums desperately
> trying to forestall shutdown. That's probably a lot less likely now
> that we have the freeze map and such a system would almost surely have
> a nasty bloat problem, but disabling index cleanup by default would
> make it worse.

Understood and agreed. Always setting it to false would affect much
and there are users who expect anti-wraparound vacuums to reclaim
garbage.

>
> I think the solution in the long run here is to (1) allow the
> index_cleanup option (or the corresponding reloption) to override the
> default behavior and (2) eventually change the default behavior from
> 'always yes' to 'depends on how many dead tuples we found'. But I
> think that the second of those things is not appropriate to consider
> changing in PG 12 at this point.

The current patch already takes (1) and I agreed with you that (2)
would be for PG 13 or later. So the patch would be helpful for such
users as well.

Attached updated patches. These patches are applied on top of 0001
patch on parallel vacuum thread[1].

[1] https://www.postgresql.org/message-id/CAD21AoBaFcKBAeL5_%2B%2Bj%2BVzir2vBBcF4juW7qH8b3HsQY%3DQ6%2Bw%40mail.gmail.com

Regards,

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