Horton
Menu

Re: New IndexAM API controlling index vacuum strategies

Victor Yegorov <vyegorov@gmail.com>

View thread
From: Victor Yegorov <vyegorov@gmail.com>
To: Peter Geoghegan <pg@bowt.ie>
Cc: Masahiko Sawada <sawada.mshk@gmail.com>, PostgreSQL-development <pgsql-hackers@postgresql.org>
Date: 2021-02-02T14:27:56Z
Lists: pgsql-hackers 
вт, 2 февр. 2021 г. в 05:27, Peter Geoghegan <pg@bowt.ie>:

> And now here is the second thing I thought of, which is much better:
>
> Sometimes 1% of the dead tuples in a heap relation will be spread
> across 90%+ of the pages. With other workloads 1% of dead tuples might
> be highly concentrated, and appear in no more than 1% of all heap
> pages. Obviously the distinction between these two cases/workloads
> matters a lot. And so the triggering criteria must be quantitative
> *and* qualitative. It should not be based on counting dead tuples,
> since that alone won't differentiate these two extreme cases - both of
> which are probably quite common (in the real world extremes are
> actually the normal and common case IME).
>
> I like the idea of basing it on counting *heap blocks*, not dead
> tuples. We can count heap blocks that have *at least* one dead tuple
> (of course it doesn't matter how they're dead, whether it was this
> VACUUM operation or some earlier opportunistic pruning). Note in
> particular that it should not matter if it's a heap block that has
> only one LP_DEAD line pointer or a heap page that is near the
> MaxHeapTuplesPerPage limit for the page -- we count either type of
> page towards the heap-page based limit used to decide if index
> vacuuming goes ahead for all indexes during VACUUM.
>

I really like this idea!

It resembles the approach used in bottom-up index deletion, block-based
accounting provides a better estimate for the usefulness of the operation.

I suppose that 1% threshold should be configurable as a cluster-wide GUC
and also as a table storage parameter?


-- 
Victor Yegorov

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