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

On Tue, Jul 10, 2018 at 10:03 PM Heikki Linnakangas <hlinnaka(at)iki(dot)fi> wrote:
>
> On 28/02/18 18:03, Anastasia Lubennikova wrote:
> > I want to propose a bunch of patches which allow to reduce WAL traffic
> > generated by CREATE INDEX for GiST, GIN and SP-GiST. Similarly to b-tree
> > and RUM, we can now log index pages of other access methods only once
> > in the end of indexbuild process.
>
> Makes sense. Is there any scenario where the current method is better?
> In theory, doing another pass through the whole index, to create the WAL
> records, requires more I/O. But in practice, it seems that the reduction
> in WAL is almost certainly a win.

It's frequently advised to move WAL to the separate storage device.
Then, it's not hard to imagine how current method can be faster, when
storage device containing index is much more loaded than storage
device containing WAL. But despite being faster it would still
consume way more overall I/O resources. I'm not sure if we should
provide some option to switch between WAL methods (or heuristics)...

------
Alexander Korotkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company