Re: New vacuum option to do only freezing

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.

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.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company