public inbox for [email protected]
help / color / mirror / Atom feedFrom: Darafei "Komяpa" Praliaskouski <[email protected]>
To: Andrey Borodin <[email protected]>
Cc: Peter Geoghegan <[email protected]>
Cc: [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: Mon, 10 Nov 2025 23:16:52 +0400
Message-ID: <CAC8Q8tJt6LFNaMjKAB0-SBm8q8p2ABQ47beEAgeFDFHKrUXQZg@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
<CAH2-Wz=e2LG=a9X9bsHVCdCsouH=6EE94NSBErwF5pY=1K2efA@mail.gmail.com>
<[email protected]>
<[email protected]>
Hello,
On Sun, Aug 31, 2025 at 4:16 PM Andrey Borodin <[email protected]> wrote:
>
>
> > On 29 Aug 2025, at 13:39, Andrey Borodin <[email protected]> wrote:
> >
>
> What if we just abort a scan, that stepped on the page where tuples were
> moved out?
>
...
> What do you think?
>
We have a database on which we have bulk insertions and deletions of
significant parts of the table.
btree- and gist-bloat becomes a significant issue there so much that we
have to resort to making ad-hoc cron-like solutions[1]. REINDEX
CONCURRENTLY also sometimes crashes due to memory pressure leaving
half-dead indexes behind which we have to clean up and keep reindexing
until success. [2]
Anything that improves the situation and makes Postgres handle this
automatically would improve the experience significantly.
Regarding locks: I think that baseline to compare to here is "what would
happen if I had to REINDEX instead" and that is EXCLUSIVE LOCK at some
point. I'd set that as a baseline for the endeavour. I think it may
dramatically simplify correctness checks for the first iterations and
relieve the pain for most of the cases.
A similar mechanic for GiST will also be helpful.
1.
https://github.com/konturio/insights-db/blob/main/scripts/reindex-bloated-btrees.sh
2.
https://github.com/konturio/insights-db/blob/main/scripts/drop_invalid_indexes.sql
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], [email protected]
Subject: Re: [WiP] B-tree page merge during vacuum to reduce index bloat
In-Reply-To: <CAC8Q8tJt6LFNaMjKAB0-SBm8q8p2ABQ47beEAgeFDFHKrUXQZg@mail.gmail.com>
* 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