Re: Fast index build vs. PITR - Mailing list pgsql-hackers

From Zeugswetter Andreas SB SD
Subject Re: Fast index build vs. PITR
Date
Msg-id 46C15C39FEB2C44BA555E356FBCD6FA40184D0EE@m0114.s-mxs.net
Whole thread Raw
In response to Fast index build vs. PITR  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-hackers
> I think an actually implementable version of this would be:
>
> 1. Don't log any index operations at all in WAL.
>
> 2. When recovering from WAL, restore all the table contents by WAL
> replay.  (This would of course include the system catalog contents that
> describe the indexes.)  Then sit there and do a global REINDEX to
> rebuild all the indexes.
>
> This would gain a reduction of some percentage in WAL traffic, at the
> cost of a hugely expensive recovery cycle any time you actually needed
> to use the WAL.  I guess this could be attractive to some installations,
> but I'm not sure very many people would want it ...

I think only the "global" part of it is not really acceptable. If we had a flag
for each index that marks it "inconsistent" reindexing only those that are
marked would be great.

Could we log a WAL record that basically only marks an index for deferred reindex
after WAL recovery ? During WAL replay all records for this index could be
ignored (this is not a must because of the post update page images in WAL,
the index would still stay inconsistent until reindex of course).

I think such a reindex step could also be responsible for those non-btree
indexes that don't fully support WAL (gist?).

Andreas


pgsql-hackers by date:

Previous
From: Alvaro Herrera
Date:
Subject: Re: SELECT * FROM LIMIT 1; is really slow
Next
From: Bob.Henkel@hartfordlife.com
Date:
Subject: Re: Nested xacts: looking for testers and review