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

From: Andrey Lepikhov <a(dot)lepikhov(at)postgrespro(dot)ru>
To: Heikki Linnakangas <hlinnaka(at)iki(dot)fi>
Cc: David Steele <david(at)pgmasters(dot)net>, a(dot)lubennikova(at)postgrespro(dot)ru, Michael Paquier <michael(at)paquier(dot)xyz>, Dmitry Dolgov <9erthalion6(at)gmail(dot)com>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Reduce amount of WAL generated by CREATE INDEX for gist, gin and sp-gist
Date: 2019-04-01 10:18:18
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

On 26/03/2019 15:59, Heikki Linnakangas wrote:
> On 26/03/2019 11:29, Andrey Lepikhov wrote:
>> On 25/03/2019 15:21, Heikki Linnakangas wrote:
>>> Hmm. When do we create all-zero pages during index build? That seems
>>> pretty surprising.
>> GIST uses buffered pages. During GIST build it is possible (very rarely)
>> what no one index tuple was written to the block page before new block
>> was allocated. And the page has become an all-zero page.
>> You can't have problems in the current GIST code, because it writes into
>> the WAL only changed pages.
> Looking at the code, I don't see how that could happen. The only place
> where the GiST index file is extended is in gistNewBuffer(), and all
> callers of that initialize the page immediately after the call. What am
> I missing?
Sorry, This issue was found in SP-GiST AM. You can show it:
1. Apply v2 version of the patch set (see attachment).
2. In the generic_log_relation() routine set logging on PageIsNew(buf)
3. Run script t1.sql (in attachment).

This problem can be resolved by calling MarkBufferDirty() after
SpGistInitBuffer() in the allocNewBuffer() routine. But in this case we
will write to the WAL more pages than necessary.
To avoid it in the patch '0001-Relation-into-WAL-function' I do not
write new pages to the WAL.

Attached patch set is not final version. It is needed for demonstration
of 'all-zero pages' issue only. The sentence for the direct use of
XLOG_FPI records will be considered in v3.

Andrey Lepikhov
Postgres Professional
The Russian Postgres Company

Attachment Content-Type Size
t1.sql application/sql 239 bytes
v2_0001-Relation-into-WAL-function.patch text/x-patch 2.3 KB
v2_0002-GIN-Optimal-WAL-Usage.patch text/x-patch 10.3 KB
v2_0003-GiST-Optimal-WAL-Usage.patch text/x-patch 11.1 KB
v2_0004-SP-GiST-Optimal-WAL-Usage.patch text/x-patch 6.3 KB

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Filip Rembiałkowski 2019-04-01 11:26:06 Re: dropdb --force
Previous Message Amit Langote 2019-04-01 10:02:44 Re: speeding up planning with partitions