On Wed, May 29, 2013 at 2:26 PM, Magnus Hagander <magnus@xxxxxxxxxxxx> wrote:
On Wed, May 29, 2013 at 8:24 AM, Niels Kristian SchjødtIf you have the diskspaec, it's generally a good idea to do a CREATE
<nielskristian@xxxxxxxxxxxxx> 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?
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.
