Re: Best practice when reindexing in production

On 29/05/13 14:24, Niels Kristian Schjødt wrote:On 29/05/13 14:24, Niels
Kristian Schjødt wrote:
> Hi,
>
> I have a database with quite some data (millions of rows), that is heavily updated all the time. Once a day I would like to reindex my database (and maybe re cluster it - don't know if that's worth it yet?). I need the database to be usable while doing this (both read and write). I see that there is no way to REINDEX CONCURRENTLY - So what approach would you suggest that I take on this?

Hi.

Since you still dont know wether it is worth it or not, I would strongly
suggest that you test this out before. Simply just creating an index
next to the old one with the same options (but different name) and
compare sizes would be simple.

Second, if the new index is significantly smaller than the old on, I
suggest that you try to crank up the autovacuum daemon instead of
blindly dropping and creating indexes, this will help to mitigate the
bloat you're seeing accumulating in above test.

Cranking up autovacuum is going to have significan less impact on the
concurrent queries while doing it and can help to maintain the database
in a shape where regular re-indexings shouldnt be nessesary. Autovacuum
has build in logic to sleep inbetween operations in order to reduce the
IO-load of you system for the benefit of concurrent users. The approach
of duplicate indices will pull all the resources it can get and
concurrent users may suffer while you do it..

Jesper

--
Jesper