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

On Fri, Jan 5, 2024 at 3:05 PM Andres Freund <andres(at)anarazel(dot)de> wrote:
> OTOH, the pruning logic, including its WAL record, already supports marking
> items unused, all we need to do is to tell it to do so in a few more cases. If
> we didn't already need to have support for this, I'd a much harder time
> arguing for doing this.
>
> One important part of the larger project is to combine the WAL records for
> pruning, freezing and setting the all-visible/all-frozen bit into one WAL
> record. We can't set all-frozen before we have removed the dead items. So
> either we need to combine pruning and setting items unused for no-index tables
> or we end up considerably less efficient in the no-indexes case.

Those are fair arguments.

> An aside:
>
> As I think we chatted about before, I eventually would like the option to
> remove index entries for a tuple during on-access pruning, for OLTP
> workloads. I.e. before removing the tuple, construct the corresponding index
> tuple, use it to look up index entries pointing to the tuple. If all the index
> entries were found (they might not be, if they already were marked dead during
> a lookup, or if an expression wasn't actually immutable), we can prune without
> the full index scan. Obviously this would only be suitable for some
> workloads, but it could be quite beneficial when you have huge indexes. The
> reason I mention this is that then we'd have another source of marking items
> unused during pruning.

I will be astonished if you can make this work well enough to avoid
huge regressions in plausible cases. There are plenty of cases where
we do a very thorough job opportunistically removing index tuples.

--
Robert Haas
EDB: http://www.enterprisedb.com