Horton
Menu

Re: New IndexAM API controlling index vacuum strategies

Masahiko Sawada <sawada.mshk@gmail.com>

View thread
From: Masahiko Sawada <sawada.mshk@gmail.com>
To: Peter Geoghegan <pg@bowt.ie>
Cc: Robert Haas <robertmhaas@gmail.com>, PostgreSQL-development <pgsql-hackers@postgresql.org>, Noah Misch <noah@leadboat.com>
Date: 2021-03-23T01:40:33Z
Lists: pgsql-hackers 
On Fri, Mar 19, 2021 at 3:36 AM Peter Geoghegan <pg@bowt.ie> wrote:
>
> On Thu, Mar 18, 2021 at 3:32 AM Masahiko Sawada <sawada.mshk@gmail.com> wrote:
> > If we have the constant threshold of 1 billion transactions, a vacuum
> > operation might not be an anti-wraparound vacuum and even not be an
> > aggressive vacuum, depending on autovacuum_freeze_max_age value. Given
> > the purpose of skipping index vacuuming in this case, I think it
> > doesn't make sense to have non-aggressive vacuum skip index vacuuming
> > since it might not be able to advance relfrozenxid. If we have a
> > constant threshold, 2 billion transactions, maximum value of
> > autovacuum_freeze_max_age, seems to work.
>
> I like the idea of not making the behavior a special thing that only
> happens with a certain variety of VACUUM operation (non-aggressive or
> anti-wraparound VACUUMs). Just having a very high threshold should be
> enough.
>
> Even if we're not going to be able to advance relfrozenxid, we'll
> still finish much earlier and let a new anti-wraparound vacuum take
> place that will do that -- and will be able to reuse much of the work
> of the original VACUUM. Of course this anti-wraparound vacuum will
> also skip index vacuuming from the start (whereas the first VACUUM may
> well have done some index vacuuming before deciding to end index
> vacuuming to hurry with finishing).

But we're not sure when the next anti-wraparound vacuum will take
place. Since the table is already vacuumed by a non-aggressive vacuum
with disabling index cleanup, an autovacuum will process the table
when the table gets modified enough or the table's relfrozenxid gets
older than autovacuum_vacuum_max_age. If the new threshold, probably a
new GUC, is much lower than autovacuum_vacuum_max_age and
vacuum_freeze_table_age, the table is continuously vacuumed without
advancing relfrozenxid, leading to unnecessarily index bloat. Given
the new threshold is for emergency purposes (i.g., advancing
relfrozenxid faster), I think it might be better to use
vacuum_freeze_table_age as the lower bound of the new threshold. What
do you think?

Regards,

-- 
Masahiko Sawada
EDB:  https://www.enterprisedb.com/

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Don't truncate heap when VACUUM's failsafe is in effect.

  2. Teach VACUUM to bypass unnecessary index vacuuming.

  3. Add wraparound failsafe to VACUUM.

  4. Truncate line pointer array during VACUUM.

  5. Remove tupgone special case from vacuumlazy.c.

  6. Refactor lazy_scan_heap() loop.

  7. Propagate parallel VACUUM's buffer access strategy.

  8. Simplify state managed by VACUUM.

  9. Notice that heap page has dead items during VACUUM.

  10. Adjust lazy_scan_heap() accounting comments.

  11. Use full 64-bit XID for checking if a deleted GiST page is old enough.

  12. Fix some problems with VACUUM (INDEX_CLEANUP FALSE).