Re: Fast index build vs. PITR

On Tue, Jun 01, 2004 at 01:55:38PM -0400, Greg Stark wrote:
> 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.

Maybe it is log(n) algorithmically, but the constants are big because
there's a lot of non-sequential I/O involved. With the WAL approach you
only read the pages from the log and copy them somewhere else. It's not
nearly the same amount of I/O, and it's mostly sequential. And if I
understood correctly what Tom said, after index construction the whole
index pages are dropped, so there's no need to redo each node split
operation.

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

I think there was more than one problem in the code. I expect at least
those not related (such as some locking issue apparently) are solved.
And I'd expect WAL construction to be heavier than recovery from WAL,
because some WAL writes have to be fsync()ed, and this is a heavy
burden, while recovery does not need fsync on the new files AFAIK (but I
could be wrong on this).

One of the things that bothered me was that for some reason you couldn't
get the whole performance benefit you would expect from simultaneous
index builds when using a multiprocessor machine.

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

Apparently this is the current state of affairs, though I'm not sure.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"La persona que no quería pecar / estaba obligada a sentarse
en duras y empinadas sillas / desprovistas, por cierto
de blandos atenuantes" (Patricio Vogel)