Peter, Matthias, many thanks for your input!
> On 28 Aug 2025, at 01:06, Peter Geoghegan <pg@bowt.ie> wrote:
>
> Have you benchmarked it?
Kind of. Here are bloat charts from random production clusters:
Upper green line has an axis on right - it's total index bloat per cluster. Other lines are individual indexes with
mostbloat, axis on the left.
Notice the 7 day period on one of lines. It's our friday automatic index repack of an indexes that suffers from bloat.
Mostof the week these indexes are 97%+ percents bloated.
On a yellow line small period is noticeable - that's what "free at empty" vacuum can help with now.
It is just one index, and even not a top bloat contributor. But 2 out of 3 random clusters had such index. (I must
admitthat clusters were not truly random - I just picked browser tabs from recent incidents during my on-call duty
shift)
Both cases are queues with secondary index, which gets bloated quickly after reindexing.
Of course, I'm not proposing to do "merge-at-half", merge-at-95%-free would be good enough for this case. 95% bloated
indexcertainly has some 95% free pages.
I think to establish baseline for locking correctness we are going to start from writing index scan tests, that fail
withproposed merge patch and pass on current HEAD. I want to observe that forward scan is showing duplicates and
backwardscan misses tuples.
From that we will try to design locking that does not affect performance significantly, but allows to merge pages.
Perhaps,we can design a way to switch new index scans to "safe mode" during index vacuum and waiting for existing scans
tocomplete.
Best regards, Andrey Borodin.
