MIME-Version: 1.0
References: <CAEze2Wj9SgwOpe_1CWnS_D-txQaQyXArR=dm4DTnha93=yua4g@mail.gmail.com>
 <CANtu0ohFr7OzNSbxqBhUpR0mXDYyt0Xt6+=Tbq0EC7as7kr+Lg@mail.gmail.com>
 <CANtu0oh4PwBn_h+4p_MxFigRAyJvF-0nA9Tm5NFRwfsWWjZQiA@mail.gmail.com>
 <CANtu0ojHEVU9U_bxgViRmtqNTJ92LnF+76-yzn4axYjGsK2kqQ@mail.gmail.com>
 <CANtu0ogS871NkdUnZW9P_LVpLzhSJ1+cETK0b55cYjs=v2qbPA@mail.gmail.com>
 <CANtu0ohRVBDf4x7Ge3oVzgf4NzMb_DhmTM1ae0u1WUA+CD0UqA@mail.gmail.com>
 <CANtu0ogTfyng-H4yWr3Pm_+PXX+XvDx1AM1sXTy1V7DM6jJ+Bw@mail.gmail.com>
 <CANtu0oi+nbipJUsMZcoUfodCyuTN_DAXD22UstjMTYWG=tJ4jw@mail.gmail.com>
 <CANtu0oiuUF7L0wTGxOHfumyoVge3n7C4rAjdmFo=efeEwobXbg@mail.gmail.com>
 <CANtu0oiD-AvXdygYqYP-WkFq=7vSL78Wj8UU-PUX+3huPNqroQ@mail.gmail.com> <Z2uV2TIpYisYPipp@paquier.xyz>
In-Reply-To: <Z2uV2TIpYisYPipp@paquier.xyz>
From: Michail Nikolaev <michail.nikolaev@gmail.com>
Date: Wed, 25 Dec 2024 16:14:00 +0100
Message-ID: <CANtu0og-4pvn4+TCWH6U9ghyd7x7NBAZSgi4ZWyBZdBWH6OpWA@mail.gmail.com>
Subject: Re: Revisiting {CREATE INDEX, REINDEX} CONCURRENTLY improvements
To: Michael Paquier <michael@paquier.xyz>
Cc: Matthias van de Meent <boekewurm+postgres@gmail.com>, 
	PostgreSQL Hackers <pgsql-hackers@postgresql.org>, Andrey Borodin <amborodin86@gmail.com>, 
	Melanie Plageman <melanieplageman@gmail.com>
Content-Type: multipart/alternative; boundary="000000000000ea615b062a19ae8e"
Archived-At: <https://www.postgresql.org/message-id/CANtu0og-4pvn4%2BTCWH6U9ghyd7x7NBAZSgi4ZWyBZdBWH6OpWA%40mail.gmail.com>
Precedence: bulk

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

Hello, Michael!

Thank you for your comments and feedback!

Yes, this patch set contains a significant amount of code, which makes it
challenging to review. Some details are explained in the commit messages,
but I=E2=80=99m doing my best to structure the patch set in a way that is a=
s
committable as possible. Once all the parts are ready, I plan to write a
detailed letter explaining everything, including benchmark results and
other relevant information.

Meanwhile, here=E2=80=99s a quick overview of the patch structure. If you h=
ave
suggestions for an alternative decomposition approach, I=E2=80=99d be happy=
 to hear.
The primary goals of the patch set are to:
    * Enable the xmin horizon to propagate freely during concurrent index
builds
    * Build concurrent indexes with a single heap scan

The patch set is split into the following parts. Technically, each part
could be committed separately, but all of them are required to achieve the
goals.

Part 1: Stress tests
- 0001: Yes, this patch is from another thread and not directly required,
it=E2=80=99s included here as a single commit because it=E2=80=99s necessar=
y for stress
testing this patch set. Without it, issues with concurrent reindexing and
upserts cause failures.
- 0002: Yes, I agree these tests need to be refactored or moved into a
separate task. I=E2=80=99ll address this later.

Part 2: During the first phase of concurrently building a  index, reset the
snapshot used for heap scans between pages, allowing xmin to go forward.
- 0003: Implement such snapshot resetting for non-parallel and non-unique
cases
- 0004: Extends snapshot resetting to parallel builds
- 0005: Extends snapshot resetting to unique indexes

Part 3: Build concurrent indexes in a single heap scan
- 0006: Introduces the STIR (Short-Term Index Replacement) access method, a
specialized method for auxiliary indexes during concurrent builds
- 0007: Implements the auxiliary index approach, enabling concurrent index
builds to use a single heap scan.
            In a few words, it works like this: create an empty auxiliary
STIR index to track new tuples, scan heap and build new index, merge STIR
tuples into new index, drop auxiliary index.
- 0008: Enhances the auxiliary index approach by resetting snapshots during
the merge phase, allowing xmin to propagate

Part 4: This part depends on all three previous parts being committed to
make sense (other parts are possible to apply separately).
- 0009:  Remove PROC_IN_SAFE_IC logic, as it is no more required

I have a plan to add a few additional small things (optimizations) and then
do some scaled stress-testing and benchmarking. I think that without it, no
one is going to spend his time for such an amount of code :)

Merry Christmas,
Mikhail.

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

<div dir=3D"auto"><div dir=3D"ltr">Hello, Michael!<div><br></div><div><p>Th=
ank you for your comments and feedback!</p><p>Yes, this patch set contains =
a significant amount of code, which makes it challenging to review. Some de=
tails are explained in the commit messages, but I=E2=80=99m doing my best t=
o structure the patch set in a way that is as committable as possible. Once=
 all the parts are ready, I plan to write a detailed letter explaining ever=
ything, including benchmark results and other relevant information.</p><p>M=
eanwhile, here=E2=80=99s a quick overview of the patch structure. If you ha=
ve suggestions for an alternative decomposition approach, I=E2=80=99d be ha=
ppy to hear.</p></div><div>The primary goals of the patch set are to:<br>=
=C2=A0 =C2=A0 * Enable the xmin horizon to propagate freely during concurre=
nt index builds<br>=C2=A0 =C2=A0 * Build concurrent indexes with a single h=
eap scan</div><div><br></div><div>

The patch set is split into the following parts. Technically, each part cou=
ld be committed separately, but all of them are required to achieve the goa=
ls.

</div><div><br></div><div>Part 1: Stress tests</div><div>- 0001: Yes,=20

this patch is from another thread and not directly required, it=E2=80=99s i=
ncluded here as a single commit because it=E2=80=99s necessary for stress t=
esting this patch set. Without it, issues with concurrent reindexing and up=
serts cause failures.</div><div>- 0002: Yes, I agree these tests need to be=
 refactored or moved into a separate task. I=E2=80=99ll address this later.=
</div><div><br>Part 2: During the first phase of concurrently building a=C2=
=A0 index, reset the snapshot used for heap scans between pages, allowing x=
min to go forward.</div><div>- 0003: Implement such snapshot resetting for =
non-parallel and non-unique cases</div><div>- 0004:=20

 Extends snapshot resetting to parallel builds</div><div>- 0005:=20

Extends snapshot resetting to unique indexes</div><div><br></div><div>Part =
3: Build concurrent indexes in a single heap scan</div><div>- 0006: Introdu=
ces the STIR (Short-Term Index Replacement) access method, a specialized me=
thod for auxiliary indexes during concurrent builds</div><div>- 0007:=20

Implements the auxiliary index approach, enabling concurrent index builds t=
o use a single heap scan.</div><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 In a few=C2=A0words, it works like this: create an empty auxiliary STIR=
 index to track new tuples, scan heap and build new index, merge STIR tuple=
s into new index, drop auxiliary index.</div><div>- 0008: Enhances the auxi=
liary index approach by resetting snapshots during the merge phase, allowin=
g xmin to propagate</div><div><br></div><div>Part 4:=20

This part depends on all three previous parts being committed to make sense

 (other parts are possible to apply separately).</div><div>- 0009:=C2=A0 Re=
move PROC_IN_SAFE_IC logic, as it is no more required</div><div><br></div><=
div>I have a plan to add a few additional small things (optimizations) and =
then do some scaled stress-testing and benchmarking. I think that without i=
t, no one is going to spend his time for such an amount=C2=A0of code :)=C2=
=A0</div><div><br></div><div>Merry Christmas,</div><div>Mikhail.</div></div=
></div>

--000000000000ea615b062a19ae8e--