| From: | Kirill Reshke <reshkekirill(at)gmail(dot)com> |
|---|---|
| To: | pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
| Subject: | Use log_newpage_range in HASH index build |
| Date: | 2025-10-23 19:21:35 |
| Message-ID: | CALdSSPgu6fnoOYzgiFF4_Etr96zEHvSwvYJDemc3o++EZbUQMA@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
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=on.
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
| Attachment | Content-Type | Size |
|---|---|---|
| v2-0001-Use-log_newpage_range-in-HASH-index-build.patch | application/octet-stream | 16.1 KB |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Sami Imseih | 2025-10-23 19:32:50 | Re: another autovacuum scheduling thread |
| Previous Message | Masahiko Sawada | 2025-10-23 18:52:54 | Re: POC: enable logical decoding when wal_level = 'replica' without a server restart |