Horton
Menu

Re: New IndexAM API controlling index vacuum strategies

Peter Geoghegan <pg@bowt.ie>

View thread
From: Peter Geoghegan <pg@bowt.ie>
To: Robert Haas <robertmhaas@gmail.com>
Cc: Masahiko Sawada <sawada.mshk@gmail.com>, PostgreSQL-development <pgsql-hackers@postgresql.org>
Date: 2021-03-09T20:35:01Z
Lists: pgsql-hackers 
On Mon, Mar 8, 2021 at 7:34 PM Peter Geoghegan <pg@bowt.ie> wrote:
> > One possible
> > consequence that I'm concerned about is sequential scan performance.
> > For an index scan, you just jump to the line pointer you want and then
> > go get the tuple, but a sequential scan has to loop over all the line
> > pointers on the page, and skipping a lot of dead ones can't be
> > completely free. A small increase in MaxHeapTuplesPerPage probably
> > wouldn't matter, but the proposed increase of almost 10x (291 -> 2042)
> > is a bit scary.
>
> I agree. Maybe the real problem here is that MaxHeapTuplesPerPage is a
> generic constant. Perhaps it should be something that can vary by
> table, according to practical table-level considerations such as
> projected tuple width given the "shape" of tuples for that table, etc.

Speaking of line pointer bloat (and "irreversible" bloat), I came
across something relevant today. I believe that this recent patch from
Matthias van de Meent is a relatively easy way to improve the
situation:

https://www.postgresql.org/message-id/flat/CAEze2WjgaQc55Y5f5CQd3L%3DeS5CZcff2Obxp%3DO6pto8-f0hC4w%40mail.gmail.com

-- 
Peter Geoghegan

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