Horton

Re: Emit fewer vacuum records by reaping removable tuples during pruning

Melanie Plageman <melanieplageman@gmail.com>

View thread View on postgr.es
From: Melanie Plageman <melanieplageman@gmail.com>
To: Peter Geoghegan <pg@bowt.ie>
Cc: Robert Haas <robertmhaas@gmail.com>, Michael Paquier <michael@paquier.xyz>, Pg Hackers <pgsql-hackers@postgresql.org>, Andres Freund <andres@anarazel.de>
Date: 2024-01-17T22:38:52Z
Lists: pgsql-hackers 
On Wed, Jan 17, 2024 at 4:25 PM Peter Geoghegan <pg@bowt.ie> wrote:
>
> On Wed, Jan 17, 2024 at 3:58 PM Robert Haas <robertmhaas@gmail.com> wrote:
> > > Ah, I realize I was not clear. I am now talking about inconsistencies
> > > in vacuuming the FSM itself. FreeSpaceMapVacuumRange(). Not updating
> > > the freespace map during the course of vacuuming the heap relation.
> >
> > Fair enough, but I'm still not quite sure exactly what the question
> > is. It looks to me like the current code, when there are indexes,
> > vacuums the FSM after each round of index vacuuming. When there are no
> > indexes, doing it after each round of index vacuuming would mean never
> > doing it, so instead we vacuum the FSM every ~8GB. I assume what
> > happened here is that somebody decided doing it after each round of
> > index vacuuming was the "right thing," and then realized that was not
> > going to work if no index vacuuming was happening, and so inserted the
> > 8GB threshold to cover that case.
>
> Note that VACUUM_FSM_EVERY_PAGES is applied against the number of
> rel_pages "processed" so far -- *including* any pages that were
> skipped using the visibility map. It would make a bit more sense if it
> was applied against scanned_pages instead (just like
> FAILSAFE_EVERY_PAGES has been since commit 07eef53955). In other
> words, VACUUM_FSM_EVERY_PAGES is applied against a thing that has only
> a very loose relationship with physical work performed/time elapsed.

This is a good point. Seems like a very reasonable change to make, as
I would think that was the original intent.

- Melanie

Commits

  1. Combine FSM updates for prune and no-prune cases.

  2. Remove LVPagePruneState.

  3. Move VM update code from lazy_scan_heap() to lazy_scan_prune().

  4. Optimize vacuuming of relations with no indexes.

  5. Be more consistent about whether to update the FSM while vacuuming.

  6. Remove hastup from LVPagePruneState.

  7. Use scanned_pages to decide when to failsafe check.

  8. Simplify lazy_scan_heap's handling of scanned pages.

  9. While vacuuming a large table, update upper-level FSM data every so often.