Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vcgGX-009Zoy-37 for pgsql-hackers@arkaria.postgresql.org; Mon, 05 Jan 2026 08:48:42 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1vcgGU-000kEt-2p for pgsql-hackers@arkaria.postgresql.org; Mon, 05 Jan 2026 08:48:39 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vcgGU-000kEl-10 for pgsql-hackers@lists.postgresql.org; Mon, 05 Jan 2026 08:48:39 +0000 Received: from mail-pg1-x541.google.com ([2607:f8b0:4864:20::541]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1vcgGT-004IDO-0h for pgsql-hackers@postgresql.org; Mon, 05 Jan 2026 08:48:38 +0000 Received: by mail-pg1-x541.google.com with SMTP id 41be03b00d2f7-c227206e6dcso13378266a12.2 for ; Mon, 05 Jan 2026 00:48:36 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1767602916; x=1768207716; darn=postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=pbMVOFXNeMjSKgThBNCz1dt2mjDqEAjlTw+hHEu1O5E=; b=RNz8h7mUg+sjJANxn6EP2ff7OCL6hlFelFTF57gg4p37bI4OBbGea0wL1n+IVnzGSj QVCArbhId1WvXE4xl93lVl117FopgXVRgWIA6MTeP+N/scRNFgRCwQ7aAhYXaQwQsuHE kA7Ksp7uT+2DAo3kK8GH8jO8HK3pPfVlofoa0jceToTgC+EG/VE04dKKoKwul7wlzVyK apIwHPKDn+0kDdcs5KHlse1H0yEay81n3XR0Rc/bzkFNB9jIN/bRzjdvH+4KSQSu9rx5 RaLHX5TSiaRI1sKcdblve5cnrPvqdp2nli2rXVs7l54SdIQ7PxRdisA6Fyij4qPw8Fsq qTAg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1767602916; x=1768207716; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-gg:x-gm-message-state:from:to:cc:subject:date :message-id:reply-to; bh=pbMVOFXNeMjSKgThBNCz1dt2mjDqEAjlTw+hHEu1O5E=; b=Cm8x0dquntJ8AtrKkA03sRRDY1LzWT3dS8Iv+7XDkfjbpP/I0M7gJOypU+dtEBcg+T TNnhaGllTD+RxfFD7gSUohl0ykXZ7rd85Znlw7X41/U68kV2NsSxLlvnKnBDOO/Aszzg 18f/98CPsKXZcyOlNgdGjRtjsuGrwM1g5x8H+iLjA2thNbMomMZ+lPas4utj7n8zH02j 08WDh4uX/K1ZvenX3xYAeJki6oX1Ca05JNtWIY7KrvreCjD75LzG2Ee1zGwYVhXyVVTV fTXojX5GlgBGoe3Nzvfs3wCfwmT1ViLFODDIOTNN38vr4N+9q0tRg9GZ2X34WQt0Aj/K 3j5Q== X-Gm-Message-State: AOJu0YyKbW3KLFs5p1dcISrbxupY7RIax9FJM770D30A3WGtdsFeKPKF AJdYJZaffcnYRtdd0t9ZGYWKWGDPzuJj49oCYz0jkAck5lYF6f9+8UuyS0dLwbOZP/TtUo2UCEp VKjehl0OYNMvZ/gm2+PDOqWfVtzPpWTk= X-Gm-Gg: AY/fxX4YQ6iPHzLW5p0ZeqLWI0wKFhwpaa3ZGCsuEhW5MacQaXnBEYZ9Wneib7GyzAQ 1EvbM5QohHIxMVqoCP2gYOSVG11EQHVVcm+jT6hdIU7mOHxoqoRWa+Klkz70FfYpFaJz+bz0hLO db4yYk/YsIPwxZwurmXoYBM1M76WKh2p12cOBaqjyMvqUmbfdopr4NHzqg/Z7oFSbV8wyFaEL0Y 00afDW17wQPiEPPxA01VLTrD2U6fzgnklW3gB2T9B/VoSL5iO3UGfSxeK9GdtYSQ8QWFjc= X-Google-Smtp-Source: AGHT+IEVQAoSZ7/800HCyuuWvWQEwPyLxA+KzvWEMYsKRnoYaFEKH+GsJBZQE5alI6ikGpnVOLtNiav1nkFIKPGZ/Yk= X-Received: by 2002:a05:7301:2aaf:b0:2ae:506b:4b05 with SMTP id 5a478bee46e88-2b05ec6f3c4mr28351431eec.27.1767602915882; Mon, 05 Jan 2026 00:48:35 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: lakshmi Date: Mon, 5 Jan 2026 14:20:27 +0530 X-Gm-Features: AQt7F2pnpbz0vIsVZOAFd-iqUg25lfmm-h2vrxQlL0Tz9scbqp6KIeTaKTpO9bo Message-ID: Subject: Re: Use log_newpage_range in HASH index build To: Kirill Reshke Cc: pgsql-hackers Content-Type: multipart/mixed; boundary="000000000000e83f340647a01f01" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000e83f340647a01f01 Content-Type: multipart/alternative; boundary="000000000000e83f330647a01fff" --000000000000e83f330647a01fff Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Hi Kirill, Following up on my earlier note, I implemented the proposed HASH overflow page reuse enhancement.Recently freed overflow pages are recorded in _hash_freeovflpage( ),and _hash_addovflpage( ) now prefers reusing those pages during allocation before falling back to the bitmap scan. The change is backend-local and allocation-only,with no WAL or on-disk format changes.I verified correctness using index build/drop and VACUUM cycles,and confirmed WAL neutrality by comparing WAL generated during HASH index builds with and without this change (no observable difference beyond normal noise). The patch is attached for review.Feedback is welcome. Best regards, Lakshmi On Tue, Dec 23, 2025 at 5:23=E2=80=AFPM lakshmi wr= ote: > 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 fre= ed > 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 > 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 >> > --000000000000e83f330647a01fff Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi Kirill,
Following up on my earlier note, I implement= ed the proposed HASH overflow page reuse enhancement.Recently freed overflo= w pages are recorded in=C2=A0
_hash_freeovflpage( ),and _hash_addovflpa= ge( ) now prefers reusing those pages during allocation before falling back= to the bitmap scan.

The change is backend-local and allocation-only= ,with no WAL or on-disk format changes.I verified correctness using index b= uild/drop and VACUUM cycles,and confirmed WAL neutrality by comparing WAL g= enerated during HASH index builds with and without this change (no=C2=A0 ob= servable difference beyond normal noise).

The patch is attached for = review.Feedback is welcome.

Best regards,
Lakshmi

=
On Tue, Dec 23, 2025 at 5:23=E2=80=AFPM lakshmi <lakshmigcdac@gmail.com> wrote:

Hi Kirill,

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

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

I w= ould 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.com> 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 indexe= s.
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=C2=A0 SPLIT_COMPLETE and VACUUM_ONE_PAGE are never generated
during index build. I'm not sure about=C2=A0 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
--000000000000e83f330647a01fff-- --000000000000e83f340647a01f01 Content-Type: text/x-patch; charset="US-ASCII"; name="0001-hash-reuse-recently-freed-overflow-pages.patch" Content-Disposition: attachment; filename="0001-hash-reuse-recently-freed-overflow-pages.patch" Content-Transfer-Encoding: base64 Content-ID: X-Attachment-Id: f_mk0srxq20 RnJvbSAzNGQ0ZDk5YmFhNTM3N2E0ZGFiYjhjZjljNGJjZDBlOWJiMmM2Mzc2IE1vbiBTZXAgMTcg MDA6MDA6MDAgMjAwMQpGcm9tOiBMYWtzaG1pIDxiaGFyYXRkYnBnQGdtYWlsLmNvbT4KRGF0ZTog TW9uLCA1IEphbiAyMDI2IDEwOjM4OjIzICswNTMwClN1YmplY3Q6IFtQQVRDSF0gaGFzaDogcmV1 c2UgcmVjZW50bHkgZnJlZWQgb3ZlcmZsb3cgcGFnZXMKCi0tLQogc3JjL2JhY2tlbmQvYWNjZXNz L2hhc2gvaGFzaG92ZmwuYyB8IDc3ICsrKysrKysrKysrKysrKysrKysrKysrKysrKysrKwogMSBm aWxlIGNoYW5nZWQsIDc3IGluc2VydGlvbnMoKykKCmRpZmYgLS1naXQgYS9zcmMvYmFja2VuZC9h Y2Nlc3MvaGFzaC9oYXNob3ZmbC5jIGIvc3JjL2JhY2tlbmQvYWNjZXNzL2hhc2gvaGFzaG92Zmwu YwppbmRleCAzMjc3ZGExOTg0MC4uYzY2ZjhhZGMwNTYgMTAwNjQ0Ci0tLSBhL3NyYy9iYWNrZW5k L2FjY2Vzcy9oYXNoL2hhc2hvdmZsLmMKKysrIGIvc3JjL2JhY2tlbmQvYWNjZXNzL2hhc2gvaGFz aG92ZmwuYwpAQCAtMjIsNiArMjIsNjMgQEAKICNpbmNsdWRlICJhY2Nlc3MveGxvZ2luc2VydC5o IgogI2luY2x1ZGUgIm1pc2NhZG1pbi5oIgogI2luY2x1ZGUgInV0aWxzL3JlbC5oIgorLyotLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tCisgKiBCYWNrZW5kLWxvY2FsIHJldXNlIGNhY2hlIGZvciByZWNlbnRseSBm cmVlZCBvdmVyZmxvdyBwYWdlcy4KKyAqIEJlc3QtZWZmb3J0IGhpbnQgb25seTogbm8gY29ycmVj dG5lc3Mgb3IgV0FMIGRlcGVuZGVuY3kuCisgKi0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0KKyAqLworI2RlZmlu ZSBIQVNIX09WRkxfUkVVU0VfQ0FDSEVfU0laRSAzMgorCit0eXBlZGVmIHN0cnVjdCBIYXNoT3Zm bFJldXNlQ2FjaGUKK3sKKwlCbG9ja051bWJlciBibG9ja3NbSEFTSF9PVkZMX1JFVVNFX0NBQ0hF X1NJWkVdOworCWludAkJCWhlYWQ7CisJaW50CQkJY291bnQ7Cit9CQkJSGFzaE92ZmxSZXVzZUNh Y2hlOworCitzdGF0aWMgSGFzaE92ZmxSZXVzZUNhY2hlIG92ZmxfcmV1c2VfY2FjaGU7CisKKy8q IFJlY29yZCBhIGZyZWVkIG92ZXJmbG93IHBhZ2UgKi8KK3N0YXRpYyB2b2lkCitoYXNoX3JlY29y ZF9mcmVlZF9vdmZscGFnZShCbG9ja051bWJlciBibGtubykKK3sKKwlvdmZsX3JldXNlX2NhY2hl LmJsb2Nrc1tvdmZsX3JldXNlX2NhY2hlLmhlYWRdID0gYmxrbm87CisJb3ZmbF9yZXVzZV9jYWNo ZS5oZWFkID0KKwkJKG92ZmxfcmV1c2VfY2FjaGUuaGVhZCArIDEpICUgSEFTSF9PVkZMX1JFVVNF X0NBQ0hFX1NJWkU7CisKKwlpZiAob3ZmbF9yZXVzZV9jYWNoZS5jb3VudCA8IEhBU0hfT1ZGTF9S RVVTRV9DQUNIRV9TSVpFKQorCQlvdmZsX3JldXNlX2NhY2hlLmNvdW50Kys7Cit9CisKKy8qIFRy eSB0byByZXVzZSBhIHJlY2VudGx5IGZyZWVkIG92ZXJmbG93IHBhZ2UgKi8KK3N0YXRpYyBib29s CitoYXNoX3RyeV9yZXVzZV9jYWNoZWRfb3ZmbHBhZ2UoUmVsYXRpb24gcmVsLCBCdWZmZXIgbWV0 YWJ1ZiwKKwkJCQkJCQkgICBCbG9ja051bWJlciAqcmV1c2VfYmxrbm8pCit7CisJSGFzaE1ldGFQ YWdlIG1ldGFwID0gSGFzaFBhZ2VHZXRNZXRhKEJ1ZmZlckdldFBhZ2UobWV0YWJ1ZikpOworCWlu dAkJCWk7CisKKwlmb3IgKGkgPSAwOyBpIDwgb3ZmbF9yZXVzZV9jYWNoZS5jb3VudDsgaSsrKQor CXsKKwkJaW50CQkJaWR4ID0KKwkJCShvdmZsX3JldXNlX2NhY2hlLmhlYWQgLSAxIC0gaSArIEhB U0hfT1ZGTF9SRVVTRV9DQUNIRV9TSVpFKQorCQkJJSBIQVNIX09WRkxfUkVVU0VfQ0FDSEVfU0la RTsKKworCQlCbG9ja051bWJlciBibGtubyA9IG92ZmxfcmV1c2VfY2FjaGUuYmxvY2tzW2lkeF07 CisJCXVpbnQzMgkJYml0bm8gPSBfaGFzaF9vdmZsYmxrbm9fdG9fYml0bm8obWV0YXAsIGJsa25v KTsKKworCQkvKiBCaXRtYXAgaXMgYXV0aG9yaXRhdGl2ZSAqLworCQlpZiAoX2hhc2hfaXNiaXRz ZXQobWV0YXAtPmhhc2htX21hcHAsIGJpdG5vKSkKKwkJeworCQkJX2hhc2hfY2xyYml0KG1ldGFw LT5oYXNobV9tYXBwLCBiaXRubyk7CisJCQkqcmV1c2VfYmxrbm8gPSBibGtubzsKKwkJCXJldHVy biB0cnVlOworCQl9CisJfQorCisJcmV0dXJuIGZhbHNlOworfQorCiAKIAogc3RhdGljIHVpbnQz MiBfaGFzaF9maXJzdGZyZWViaXQodWludDMyIG1hcCk7CkBAIC0xMzIsNiArMTg5LDEwIEBAIF9o YXNoX2FkZG92ZmxwYWdlKFJlbGF0aW9uIHJlbCwgQnVmZmVyIG1ldGFidWYsIEJ1ZmZlciBidWYs IGJvb2wgcmV0YWluX3BpbiwgYm9vCiAJdWludDMyCQlpLAogCQkJCWo7CiAJYm9vbAkJcGFnZV9m b3VuZCA9IGZhbHNlOworCUJsb2NrTnVtYmVyIHJldXNlX2Jsa25vOworCisKKwogCiAJLyoKIAkg KiBXcml0ZS1sb2NrIHRoZSB0YWlsIHBhZ2UuICBIZXJlLCB3ZSBuZWVkIHRvIG1haW50YWluIGxv Y2tpbmcgb3JkZXIgc3VjaApAQCAtMTg2LDYgKzI0NywxNiBAQCBfaGFzaF9hZGRvdmZscGFnZShS ZWxhdGlvbiByZWwsIEJ1ZmZlciBtZXRhYnVmLCBCdWZmZXIgYnVmLCBib29sIHJldGFpbl9waW4s IGJvbwogCV9oYXNoX2NoZWNrcGFnZShyZWwsIG1ldGFidWYsIExIX01FVEFfUEFHRSk7CiAJbWV0 YXAgPSBIYXNoUGFnZUdldE1ldGEoQnVmZmVyR2V0UGFnZShtZXRhYnVmKSk7CiAKKwkvKgorCSAq IE5FVzogVHJ5IHJldXNlIGNhY2hlIGJlZm9yZSBzY2FubmluZyBiaXRtYXAKKwkgKi8KKwlpZiAo aGFzaF90cnlfcmV1c2VfY2FjaGVkX292ZmxwYWdlKHJlbCwgbWV0YWJ1ZiwgJnJldXNlX2Jsa25v KSkKKwl7CisJCW92ZmxidWYgPSBfaGFzaF9nZXRpbml0YnVmKHJlbCwgcmV1c2VfYmxrbm8pOwor CQlwYWdlX2ZvdW5kID0gdHJ1ZTsKKwkJZ290byBmb3VuZDsKKwl9CisKIAkvKiBzdGFydCBzZWFy Y2ggYXQgaGFzaG1fZmlyc3RmcmVlICovCiAJb3JpZ19maXJzdGZyZWUgPSBtZXRhcC0+aGFzaG1f Zmlyc3RmcmVlOwogCWZpcnN0X3BhZ2UgPSBvcmlnX2ZpcnN0ZnJlZSA+PiBCTVBHX1NISUZUKG1l dGFwKTsKQEAgLTYzMyw2ICs3MDQsMTIgQEAgX2hhc2hfZnJlZW92ZmxwYWdlKFJlbGF0aW9uIHJl bCwgQnVmZmVyIGJ1Y2tldGJ1ZiwgQnVmZmVyIG92ZmxidWYsCiAJQ0xSQklUKGZyZWVwLCBiaXRt YXBiaXQpOwogCU1hcmtCdWZmZXJEaXJ0eShtYXBidWYpOwogCisJLyoKKwkgKiBORVc6IFJlbWVt YmVyIHRoaXMgb3ZlcmZsb3cgcGFnZSBmb3IgcmV1c2UKKwkgKi8KKworCWhhc2hfcmVjb3JkX2Zy ZWVkX292ZmxwYWdlKG92ZmxibGtubyk7CisKIAkvKiBpZiB0aGlzIGlzIG5vdyB0aGUgZmlyc3Qg ZnJlZSBwYWdlLCB1cGRhdGUgaGFzaG1fZmlyc3RmcmVlICovCiAJaWYgKG92ZmxiaXRubyA8IG1l dGFwLT5oYXNobV9maXJzdGZyZWUpCiAJewotLSAKMi4zOS41Cgo= --000000000000e83f340647a01f01--