On Wed, May 29, 2013 at 2:26 PM, Magnus Hagander <magnus@hagander.net> wrote:
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 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?
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).
If you wish to recluster it online you can also look into pg_repack - https://github.com/reorg/pg_repack Great tool allows you to repack and reindex your database without going offline.