MIME-Version: 1.0
References: <11A59C0C-A8C8-4642-8493-292D5DF8311D@yandex-team.ru>
 <CAH2-Wz=e2LG=a9X9bsHVCdCsouH=6EE94NSBErwF5pY=1K2efA@mail.gmail.com>
 <3F0FC1DC-3653-4F61-A46F-46FF81C14915@yandex-team.ru>
 <CCD000DB-67CB-4D64-A912-B7514D546058@yandex-team.ru>
In-Reply-To: <CCD000DB-67CB-4D64-A912-B7514D546058@yandex-team.ru>
From: =?UTF-8?Q?Darafei_=22Kom=D1=8Fpa=22_Praliaskouski?= <me@komzpa.net>
Date: Mon, 10 Nov 2025 23:16:52 +0400
Message-ID: 
 <CAC8Q8tJt6LFNaMjKAB0-SBm8q8p2ABQ47beEAgeFDFHKrUXQZg@mail.gmail.com>
Subject: Re: [WiP] B-tree page merge during vacuum to reduce index bloat
To: Andrey Borodin <x4mmm@yandex-team.ru>
Cc: Peter Geoghegan <pg@bowt.ie>, boekewurm+postgres@gmail.com,
	pgsql-hackers <pgsql-hackers@postgresql.org>, Kirk Wolak <wolakk@gmail.com>,
	Nikolay Samokhvalov <nik@postgres.ai>
Content-Type: multipart/alternative; boundary="00000000000070334f064342605f"
Archived-At: 
 <https://www.postgresql.org/message-id/CAC8Q8tJt6LFNaMjKAB0-SBm8q8p2ABQ47beEAgeFDFHKrUXQZg%40mail.gmail.com>
Precedence: bulk

--00000000000070334f064342605f
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Hello,

On Sun, Aug 31, 2025 at 4:16=E2=80=AFPM Andrey Borodin <x4mmm@yandex-team.r=
u> wrote:

>
>
> > On 29 Aug 2025, at 13:39, Andrey Borodin <x4mmm@yandex-team.ru> 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-b=
trees.sh
2.
https://github.com/konturio/insights-db/blob/main/scripts/drop_invalid_inde=
xes.sql

--00000000000070334f064342605f
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">Hello,</div><br><div class=3D"gmail_quote=
 gmail_quote_container"><div dir=3D"ltr" class=3D"gmail_attr">On Sun, Aug 3=
1, 2025 at 4:16=E2=80=AFPM Andrey Borodin &lt;<a href=3D"mailto:x4mmm@yande=
x-team.ru">x4mmm@yandex-team.ru</a>&gt; wrote:<br></div><blockquote class=
=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rg=
b(204,204,204);padding-left:1ex"><br>
<br>
&gt; On 29 Aug 2025, at 13:39, Andrey Borodin &lt;<a href=3D"mailto:x4mmm@y=
andex-team.ru" target=3D"_blank">x4mmm@yandex-team.ru</a>&gt; wrote:<br>
&gt;<br>
<br>
What if we just abort a scan, that stepped on the page where tuples were mo=
ved out?<br></blockquote><div>...=C2=A0</div><blockquote class=3D"gmail_quo=
te" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204=
);padding-left:1ex">What do you think?=C2=A0<br></blockquote><div><br>We ha=
ve a database on which we have bulk insertions and deletions of significant=
 parts of the table.<br><br>btree- and gist-bloat becomes a significant iss=
ue there so much that we have to resort to making ad-hoc cron-like solution=
s[1]. REINDEX CONCURRENTLY also sometimes crashes due to memory pressure le=
aving half-dead indexes behind which we have to clean up and keep reindexin=
g until success. [2]</div><div><br>Anything that improves the situation and=
 makes Postgres handle this automatically would improve the experience sign=
ificantly.<br><br>Regarding locks: I think that baseline to compare to here=
 is &quot;what would happen if I had to REINDEX instead&quot; and that is E=
XCLUSIVE LOCK at some point. I&#39;d set that as a baseline for the endeavo=
ur. I think it may dramatically simplify correctness checks for the first i=
terations and relieve the pain for most of the cases.=C2=A0</div><div><br><=
/div><div>A similar mechanic for GiST will also be helpful.<br><br>1.=C2=A0=
<a href=3D"https://github.com/konturio/insights-db/blob/main/scripts/reinde=
x-bloated-btrees.sh">https://github.com/konturio/insights-db/blob/main/scri=
pts/reindex-bloated-btrees.sh</a><br>2.=C2=A0<a href=3D"https://github.com/=
konturio/insights-db/blob/main/scripts/drop_invalid_indexes.sql">https://gi=
thub.com/konturio/insights-db/blob/main/scripts/drop_invalid_indexes.sql</a=
></div><div><br><br>=C2=A0</div></div></div>

--00000000000070334f064342605f--