Re: Best practice when reindexing in production

[Niels Kristian Schjødt <nielskristian@xxxxxxxxxxxxx>]

I looked at pg_repack - however - is it "safe" for production? 

It seems very intrusive and black-box-like to me...

Den 29/05/2013 kl. 14.30 skrev Armand du Plessis <adp@xxxxxxx>:

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ødt
<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?

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.