Peter, Matthias, many thanks for your input! > On 28 Aug 2025, at 01:06, Peter Geoghegan <[email protected]> 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 most bloat, 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. Most of 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 admit that 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 index certainly 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 with proposed merge patch and pass on current HEAD. I want to observe that forward scan is showing duplicates and backward scan 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 to complete. Best regards, Andrey Borodin.
