Re: Best practice when reindexing in production

On Wed, May 29, 2013 at 8:24 AM, Niels Kristian Schjødt
<nielskristian@autouncle.com> 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
liketo reindex my database (and maybe re cluster it - don't know if that's worth it yet?). I need the database to be
usablewhile doing this (both read and write). I see that there is no way to REINDEX CONCURRENTLY - So what approach
wouldyou suggest that I take on this? 

If you have the diskspaec, it's generally a good idea to do a CREATE
INDEX CONCURRENTLY, and then rename the new one into place (typically
in a transaction). (If your app, documentation or dba doesn't mind the
index changing names, you don't need to rename of course, you can just
drop the old one).


--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/