Re: decoupling table and index vacuum

On Fri, Feb 4, 2022 at 1:15 PM Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> My second thought was that perhaps we can create a test scenario
> where, in one index, the deduplication and bottom-up index deletion
> and kill_prior_tuple mechanisms are very effective, and in another
> index, it's not effective at all. For example, maybe index A is an
> index on the primary key, and index B is a non-unique index on some
> column that we're updating with ever-increasing values (so that we
> never put new tuples into a page that could be productively cleaned
> up). I think what should happen in this case is that A should not grow
> in size even if it's never vacuumed, while B will require vacuuming to
> keep the size down.

That should work. All you need is a table with several indexes, and a
workload consisting of updates that modify a column that is the key
column for only one of the indexes. I would expect bottom-up index
deletion to be 100% effective for the not-logically-modified indexes,
in the sense that there will be no page splits -- provided there are
no long held snapshots, and provided that the index isn't very small.
If it is small (think of something like the pgbench_branches pkey),
then even the occasional ANALYZE will act as a "long held snapshot"
relative to the size of the index. And so then you might get one page
split per original leaf page, but probably not a second, and very very
probably not a third.

The constantly modified index will be entirely dependent on index
vacuuming here, and so an improved VACUUM design that allows that
particular index to be vacuumed more frequently could really improve
performance.

BTW, it's a good idea to avoid unique indexes in test cases where
there is an index that you don't want to set LP_DEAD bits for, since
_bt_check_unique() tends to do a good job of setting LP_DEAD bits,
independent of the kill_prior_tuple thing. You can avoid using
kill_prior_tuple by forcing bitmap scans, of course.

--
Peter Geoghegan