Joshua D. Drake wrote:
> 1. You could put all your indexes into a table space, this would allow
> you to "try" different things with the indexes.
Most of them are, but I still have to back them up in order to have a
valid backup, because the PITR code would choke if any are missing.
> 2. Even though my peer Alvaro doesn't think the idea is silly, I still
> do and here is why. If you can invalidate the indexes you will have to
> reindex (or recreate) to make them valid (as you mentioned). That is an
> exclusive lock.
>
> If your database has any level of concurrency the cost to
> recreate/reindex those indexes right when you are attempting to get your
> standby into production is going to be very high. Basically you are
> trading 25% hard disk space for a longer, possibly excessively longer
> outage. Hard disk space is so darn cheap that it doesn't seem to make
> any sense.
We have yet to recover from a PG disaster. We back up every night, and
never use the back ups for anything. To me, it seems perfectly
reasonable to get a quicker back up every night, with the remote
possibility of ever having to pay the price for it. And IMO, the price
on recovery would be minor, if not negative. The time to rebuild the
indexes would be offset by the shorter time needed to restore the
database cluster and allow the PITR stuff to work its magic. And of
course keep in mind, that while indexes are building, all the tables not
being indexed are accessible. Nothing is accessible during a PITR
operation. I would also be able to rebuild only the most important
indexes and leave the system to operate with a few missing until things
quiet down, and then finish reindexing.
-Glen