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

On Tue, Jun 01, 2004 at 11:34:15AM +1000, Gavin Sherry wrote:
> On Mon, 31 May 2004, Tom Lane wrote:
> 
> > I thought for a little bit about a magic "reconstruct the index" WAL
> > entry that would invoke the index build procedure in toto, but that
> > doesn't look like it will fly either.  (Two problems: during crash
> > recovery, you couldn't be sure that what's on disk for the underlying
> > table exactly matches the index you need to build --- it could be a
> > later state of the table; and besides, the environment of the WAL replay
> > process isn't capable of running user-defined functions, so it couldn't
> > work for functional indexes.)
> >
> > So AFAICS, we've got to dump the index contents into WAL to support
> > PITR.  This is a tad annoying.
> 
> Is it possible in this case to dump the index block by block into the log
> after it has been generated? It seems to me this should reduce the
> amount of data we write to WAL and (therefore) speed up the rebuild.

Is this less log traffic?  You save a lot of per-record overhead, but
you have to save internal pages (which are not saved with the standard
code).  Plus it would be a lot different from standard btree WAL
traffic, so it'd be more code.

Maybe there's more to be saved by logging only leaf pages.  But then
there would be even more code to be able to reconstruct the index from
only leaf pages, and there are not that many internal pages either.

A completely different idea would be to log a "logical index creation",
so that during normal recovery those entries are saved somewhere; after
the rest of WAL recovery is done, the system is taken into a more normal
post-recovery pre-usable state, on which those indexes are recreated
from user data.  This would be cheapest in WAL traffic, but probably
it'll also require more code and new hooks in the startup mechanism.
Also, it'd require examining later WAL entries that refer to the index
and act accordingly (e.g. ignore the entry if it modifies the index, and
forget the creation if it's a DROP INDEX command.)

Not that I like neither of those ideas really ... issuing normal WAL
index creation traffic if PITR is active is certainly the easiest way.

-- 
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"In Europe they call me Niklaus Wirth; in the US they call me Nickel's worth.
That's because in Europe they call me by name, and in the US by value!"