Re: Deleting older versions in unique indexes to avoid page splits

пн, 18 янв. 2021 г. в 21:43, Peter Geoghegan <pg(at)bowt(dot)ie>:

> In the end, I couldn't justify imposing this cost on anything other
> than a non-HOT updater, which is what the flag proposal would require
> me to do -- then it's not 100% clear that the relative cost of each
> "bet" placed in heapam.c really is extremely low (we can no longer say
> for sure that we have very little to lose, given the certain
> alternative that is a version churn page split).

I must admit, that it's a bit difficult to understand you here (at least
for me).

I assume that by "bet" you mean flagged tuple, that we marked as such
(should we implement the suggested case).
As heapam will give up early in case there are no deletable tuples, why do
say,
that "bet" is no longer low? At the end, we still decide between page split
(and
index bloat) vs a beneficial space cleanup.

The fact is that
> "version chains in indexes" still cannot get very long. Plus there are
> other subtle ways in which it's unlikely to be a problem (e.g. the
> LP_DEAD deletion stuff also got much better, deduplication can combine
> with bottom-up deletion so that we'll trigger a useful bottom-up
> deletion pass sooner or later without much downside, and possibly
> other things I haven't even thought of).
>

I agree with this, except for "version chains" not being long. It all
really depends
on the data distribution. It's perfectly common to find indexes supporting
FK constraints
on highly skewed sets, with 80% of the index belonging to a single value
(say, huge business
customer vs several thousands of one-time buyers).

--
Victor Yegorov