The attached documentation patch overhauls the "Routine Reindexing"
documentation to introduce production index rebuilding techniques.
REBUILD is useless for a lot of environments, replaced with CREATE INDEX
CONCURRENTLY and various naming shuffles if you want the new index to
have the same name. I tried to link to all of the commands needed to
pull that off usefully.
I also noticed that it's easy to get a wrong idea from the CREATE INDEX
CONCURRENTLY documentation: that invalid indexes are only possible when
building certain types of indexes, like unique ones. I mention
deadlocks there to make it more obvious that any index built
concurrently can fail. I have now seen several REBUILD simulation
scripts written that assume simple concurrent index building will always
work. Those can really crash and burn on a deadlock, leaving no index
at all left behind in some bad sequences. (DROP the original, ALTER the
new name to the old one, but it's invalid)
I think all of this would make a good backport candidate going back to
9.1, when several of the ALTER TABLE methods were added for replacing
indexes backing constraints. Before then this technique had so many
limitations it was barely worth mentioning. I didn't want to push this
approach so heavily in the manual at the time 9.1 was released, for fear
there might have some unexpected real-world issues here. Indexes
referred to by foreign keys have turned out to be the only thing I see
regularly that are hard to rebuild in 9.1 and 9.2. The CREATE/ALTER
shuffle works great for everything else.
--
Greg Smith 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.com