Re: Reduce amount of WAL generated by CREATE INDEX for gist, gin and sp-gist

With the consent of Anastasia I will improving this patch further.
Attachment contains next version of the patch set.

11.07.2018 00:03, Heikki Linnakangas пишет:
> On 28/02/18 18:03, Anastasia Lubennikova wrote:

>> Implementation is based on generic_xlog.
>
> Why? I think we should just add a log_relation() function in
> xloginsert.c directly, alongside log_newpage_buffer().
>
I have some arguments to stay this functionality at generic_xlog module:
1. xloginsert.c functions work on low level of abstraction, use buffers
and pages.
2. Code size using generic_xlog service functions looks more compact and
safe.
> This makes the assumption that all the pages in these indexes used the
> standard page layout. I think that's a valid assumption, but needs at
> least a comment on that. And perhaps an Assert, to check that
> pd_lower/upper look sane.
Done
>
> As a further optimization, would it be a win to WAL-log multiple pages
> in each record?
In this version of the patch we use simple optimization: pack
XLR_NORMAL_MAX_BLOCK_ID blocks pieces into each WAL-record.
>
> This leaves the XLOG_*_CREATE_INDEX WAL record types unused, BTW.
>
Done
> - Heikki
>

Benchmarks:
-----------

Test: pgbench -f gin-WAL-test.sql -t 5:
---------------------------------------
master:
Latency average: 27696.299 ms
WAL size: 2.66 GB

patched
Latency average: 22812.103 ms
WAL size: 1.23 GB

Test: pgbench -f gist-WAL-test.sql -t 5:
----------------------------------------
master:
Latency average: 19928.284 ms
WAL size: 1.25 GB

patched
Latency average: 18175.064 ms
WAL size: 0.63 GB

Test: pgbench -f spgist-WAL-test.sql -t 5:
------------------------------------------
master:
Latency average: 11529.384 ms
WAL size: 1.07 GB

patched
Latency average: 9330.828 ms
WAL size: 0.6 GB

--
Andrey Lepikhov
Postgres Professional
https://postgrespro.com
The Russian Postgres Company