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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
