Tom Lane wrote:
> "Heikki Linnakangas" <heikki@enterprisedb.com> writes:
>> Tom Lane wrote:
>>>> Another real problem with doing pruning only in UPDATE path is that
>>>> we may end up with long HOT chains if the page does not receive a
>>>> UPDATE, after many consecutive HOT updates.
>>> How is that, if the same number of prune attempts would occur?
>
>> It wouldn't. To avoid the long HOT chains, we want to prune more often
>> than what's needed to just make room for updates.
>
> I don't follow. HOT chains can only get longer by updates.
I believe the case pruning-on-select protects against is
where you'd have a period of heavy updating and long running
transaction, followed by a period of (mostly) read only access.
If you didn't manage to prune most chains during the first phase
due to a rather old OldestXmin, the following selects will all
spend cycles on following the long HOT chains.
Still, it sounds like the real reason is more the technical
difficulties of doing it on update, rather than preventing
that scenario.
A rather wild idea: Could we maybe pin individual tuples, instead
of the whole page? Then we'd just have to be careful not to move
those when pruning during the update.
greetings, Florian Pflug