Re: I don't want to back up index files

On Thu, 2009-03-12 at 12:57 -0700, Glen Parker wrote:
> Tom Lane wrote:
> > Glen Parker <glenebob(at)nwlink(dot)com> writes:
> >> Tom Lane wrote:
> >>> ... AFAICS what
> >>> Glen is proposing is to not WAL-log index changes, and with that any
> >>> crash no matter how minor would have to invalidate indexes.
> >
> >> Nooo...! This has nothing to do with WAL logging index changes.
> >
> > How so? In any PITR-based situation it seems to me you need to worry
> > about the WAL bulk a lot more than the bulk of the base backup.
>
>
> It isn't the bulk so much as the amount of time, and the impact to the
> running system during that time, that it takes to execute the base backup.
>
> I haven't noticed any real impact related to compressing and exporting
> WAL files.
>
> Anyway, more to the point, I'm not knowingly proposing anything that
> should cause reduced system reliability in the event of a crash.

Glen,

Thanks for bringing your ideas to the list.

The idea of auto rebuilding indexes following recovery has already been
proposed, so is under consideration. It hasn't been proposed in relation
to the use case you mention, so that is new.

If we did as you suggest then it would speed up the base backup but
would also add index rebuild time onto the end of any recovery. If the
database is so large than base backup time is significant then rebuild
time for *all* indexes will be very significant. It seems an attractive
proposition but one that could lead to significant regret if disaster
did strike.

So I think it's a good idea, but on balance not one that enough people
would use to make it worth implementing of itself. Auto rebuilding
damaged indexes is on the radar however, so if we're close enough we may
be able to do something useful along the lines you suggest.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support