public inbox for [email protected]
help / color / mirror / Atom feedFrom: Andrey Borodin <[email protected]>
To: Peter Geoghegan <[email protected]>
To: [email protected]
Cc: pgsql-hackers <[email protected]>
Cc: Kirk Wolak <[email protected]>
Cc: Nikolay Samokhvalov <[email protected]>
Subject: Re: [WiP] B-tree page merge during vacuum to reduce index bloat
Date: Fri, 29 Aug 2025 12:39:48 +0400
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAH2-Wz=e2LG=a9X9bsHVCdCsouH=6EE94NSBErwF5pY=1K2efA@mail.gmail.com>
References: <[email protected]>
<CAH2-Wz=e2LG=a9X9bsHVCdCsouH=6EE94NSBErwF5pY=1K2efA@mail.gmail.com>
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.
Attachments:
[image/jpeg] telegram-cloud-photo-size-2-5298707140715868464-x.jpg (35.2K, 2-telegram-cloud-photo-size-2-5298707140715868464-x.jpg)
download | view image
[image/jpeg] telegram-cloud-photo-size-2-5298707140715868461-x.jpg (37.8K, 4-telegram-cloud-photo-size-2-5298707140715868461-x.jpg)
download | view image
view thread (13+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: [WiP] B-tree page merge during vacuum to reduce index bloat
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox