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-18T10:31:55Z
Lists: pgsql-hackers 
On Thu, Mar 18, 2021 at 3:41 PM Peter Geoghegan <pg@bowt.ie> wrote:
>
> On Wed, Mar 17, 2021 at 11:23 PM Masahiko Sawada <sawada.mshk@gmail.com> wrote:
> > Attached the updated patch that can be applied on top of your v3 patches.
>
> Some feedback on this:
>
> * I think that we can afford to be very aggressive here, because we're
> checking dynamically. And we're concerned about extremes only. So an
> age of as high as 1 billion transactions seems like a better approach.
> What do you think?

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 think that you need to remember that we have decided not to do any
> more index vacuuming, rather than calling
> check_index_cleanup_xid_limit() each time -- maybe store that
> information in a state variable.
>
> This seems like a good idea because we should try to avoid changing
> back to index vacuuming having decided to skip it once.

Once decided to skip index vacuuming due to too old relfrozenxid
stuff, the decision never be changed within the same vacuum operation,
right? Because the relfrozenxid is advanced at the end of vacuum.

> Also, we need
> to refer to this in lazy_scan_heap(), so that we avoid index cleanup
> having also avoided index vacuuming. This is like the INDEX_CLEANUP =
> off case, which is also only for emergencies. It is not like the
> SKIP_VACUUM_PAGES_RATIO case, which is just an optimization.

Agreed with this point. I'll fix it in the next version patch.

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).