Re: Fast index build vs. PITR

Alvaro Herrera <alvherre(at)dcc(dot)uchile(dot)cl> writes:
> 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.

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

regards, tom lane