Improving index maintenance suggestions
От | Greg Smith |
---|---|
Тема | Improving index maintenance suggestions |
Дата | |
Msg-id | 51E74E2C.8020505@2ndQuadrant.com обсуждение исходный текст |
Ответы |
Re: Improving index maintenance suggestions
|
Список | pgsql-docs |
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
Вложения
В списке pgsql-docs по дате отправления: