Re: Fast index build vs. PITR

Alvaro Herrera <alvherre(at)dcc(dot)uchile(dot)cl> writes:

> 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.

I'm not clear that building from WAL is really going to be that much faster.
A) algorithmically it's only the factor of log(n) that you're talking about.
and B) the WAL will have records for every write, not just the final product,
so it might potentially have a lot more writes to do.

I thought part of the original problem was specifically that going through WAL
slowed down the index rebuild much more than a factor of 2, which would tend
to imply that in fact rebuilding from WAL isn't going to be as fast as you
might expect.

Another possibility is doing the complete index build without going through
WAL and then inserting a complete copy of the index into the WAL without
syncing or activating the rebuilt index until the copy do WAL is done. That
kind of sucks since it's equivalent to just taking another backup of the data
files immediately after the rebuild, but might be a more direct solution using
the existing tools.

> 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.

This is certainly true.

--
greg