Re: Fast index build vs. PITR

On Tue, Jun 01, 2004 at 12:52:32PM -0400, Greg Stark wrote:
>
> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
>
> > 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.)
>
> Could you just mark the index as unusable? Have the optimizer ignore such
> indexes and PITR recovery can notify the user of these indexes and/or invoke a
> rebuild automatically?

The big problem I see with this kind of approaches is that building an
index from scratch can take a huge amount of time, because you have to
sort the data. Building from WAL does not have this problem, so it can
be much faster. Of course, when you are restoring using a PITR
approach you probably want it to be very fast, and have the DB running
with as little quirks as possible, as soon as possible.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"El realista sabe lo que quiere; el idealista quiere lo que sabe" (Anónimo)