MIME-Version: 1.0
References: 
 <CALdSSPgu6fnoOYzgiFF4_Etr96zEHvSwvYJDemc3o++EZbUQMA@mail.gmail.com>
In-Reply-To: 
 <CALdSSPgu6fnoOYzgiFF4_Etr96zEHvSwvYJDemc3o++EZbUQMA@mail.gmail.com>
From: lakshmi <lakshmigcdac@gmail.com>
Date: Tue, 23 Dec 2025 17:23:07 +0530
Message-ID: 
 <CAEvyyTjUTLXvrme3CNMjJYwA9j4usrq53PyBPv47Apk58Qz+GA@mail.gmail.com>
Subject: Re: Use log_newpage_range in HASH index build
To: Kirill Reshke <reshkekirill@gmail.com>
Cc: pgsql-hackers <pgsql-hackers@postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000f5f67506469d29a0"
Archived-At: 
 <https://www.postgresql.org/message-id/CAEvyyTjUTLXvrme3CNMjJYwA9j4usrq53PyBPv47Apk58Qz%2BGA%40mail.gmail.com>
Precedence: bulk

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

Hi Kirill,

I tested your patch on the current master and confirmed the WAL reduction
during HASH index build.

While testing, I noticed a possible small follow-up improvement in HASH
overflow handling. Currently, any free overflow page may be reused, which
can scatter overflow chains and hurt cache locality. Reusing recently freed
overflow pages first could help, without changing WAL behavior or on-disk
format.

I would like to work on this as a follow-up enhancement and would welcome
any suggestions.

Best regards,
Lakshmi

On Tue, Dec 23, 2025 at 2:31=E2=80=AFPM Kirill Reshke <reshkekirill@gmail.c=
om>
wrote:

> There exists an optimization to index creation process, when we omit
> to write any WAL
> for index build. It is currently supported in B Tree, GIN, GiST, spg
> indexes.
> It works because we do not need to recover anything if index creation
> fails, because if was not used by any query. So, the index can be
> built on-disk, and then, just before making the index alive, we can
> simply log all pages to WAL.
>
> Hash index currently lacks this optimization.
> PFA implementation.
>
> During my testing, I checked the amount of WAL generated by index
> build before and after patch applied. My script was something like:
>
> select pg_current_wal_insert_lsn();
>
> create index on t using hash (i);
>
> select pg_current_wal_insert_lsn();
>
> select pg_lsn_wal_diff(lsn1, lsn2);
>
> Resulting numbers depend on index size, but I got 2.5-3.5 times less
> WAL with this patch and 8 times less WAL with this patch +
> wal_compression=3Don.
> Index creation time, however, did not change much...
>
> About implementation:
> These are many types of record that can be generated during index build.
> I know for sure these are possible (double-checked using pg_waldump):
>
> SPLIT_COMPLETE
> INSERT
> SPLIT_ALLOCATE_PAGE
> SPLIT_PAGE
> ADD_OVFL_PAGE
> SQUEEZE_PAGE
> INIT_META_PAGE
> INIT_BITMAP_PAGE
>
>
> Looks like  SPLIT_COMPLETE and VACUUM_ONE_PAGE are never generated
> during index build. I'm not sure about  MOVE_PAGE_CONTENTS.
>
> So, implementation is simply pass isbuild flag everywhere something is
> wal-logged. Looks like it is less invasive than alternatives.
>
> --
> Best regards,
> Kirill Reshke
>

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

<div dir=3D"ltr"><div dir=3D"ltr"><p class=3D"gmail-isSelectedEnd">Hi Kiril=
l,</p><p class=3D"gmail-isSelectedEnd">I tested your patch on the current m=
aster and confirmed the WAL reduction during HASH index build.</p><p class=
=3D"gmail-isSelectedEnd">While testing, I noticed a possible small follow-u=
p improvement in HASH overflow handling. Currently, any free overflow page =
may be reused, which can scatter overflow chains and hurt cache locality. R=
eusing recently freed overflow pages first could help, without changing WAL=
 behavior or on-disk format.</p><p class=3D"gmail-isSelectedEnd">I would li=
ke to work on this as a follow-up enhancement and would welcome any suggest=
ions.</p><p>Best regards,<br>Lakshmi</p></div><br><div class=3D"gmail_quote=
 gmail_quote_container"><div dir=3D"ltr" class=3D"gmail_attr">On Tue, Dec 2=
3, 2025 at 2:31=E2=80=AFPM Kirill Reshke &lt;<a href=3D"mailto:reshkekirill=
@gmail.com">reshkekirill@gmail.com</a>&gt; wrote:<br></div><blockquote clas=
s=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid r=
gb(204,204,204);padding-left:1ex">There exists an optimization to index cre=
ation process, when we omit<br>
to write any WAL<br>
for index build. It is currently supported in B Tree, GIN, GiST, spg indexe=
s.<br>
It works because we do not need to recover anything if index creation<br>
fails, because if was not used by any query. So, the index can be<br>
built on-disk, and then, just before making the index alive, we can<br>
simply log all pages to WAL.<br>
<br>
Hash index currently lacks this optimization.<br>
PFA implementation.<br>
<br>
During my testing, I checked the amount of WAL generated by index<br>
build before and after patch applied. My script was something like:<br>
<br>
select pg_current_wal_insert_lsn();<br>
<br>
create index on t using hash (i);<br>
<br>
select pg_current_wal_insert_lsn();<br>
<br>
select pg_lsn_wal_diff(lsn1, lsn2);<br>
<br>
Resulting numbers depend on index size, but I got 2.5-3.5 times less<br>
WAL with this patch and 8 times less WAL with this patch +<br>
wal_compression=3Don.<br>
Index creation time, however, did not change much...<br>
<br>
About implementation:<br>
These are many types of record that can be generated during index build.<br=
>
I know for sure these are possible (double-checked using pg_waldump):<br>
<br>
SPLIT_COMPLETE<br>
INSERT<br>
SPLIT_ALLOCATE_PAGE<br>
SPLIT_PAGE<br>
ADD_OVFL_PAGE<br>
SQUEEZE_PAGE<br>
INIT_META_PAGE<br>
INIT_BITMAP_PAGE<br>
<br>
<br>
Looks like=C2=A0 SPLIT_COMPLETE and VACUUM_ONE_PAGE are never generated<br>
during index build. I&#39;m not sure about=C2=A0 MOVE_PAGE_CONTENTS.<br>
<br>
So, implementation is simply pass isbuild flag everywhere something is<br>
wal-logged. Looks like it is less invasive than alternatives.<br>
<br>
-- <br>
Best regards,<br>
Kirill Reshke<br>
</blockquote></div></div>

--000000000000f5f67506469d29a0--