From: | Niels Kristian Schjødt <nielskristian(at)autouncle(dot)com> |
---|---|
To: | Armand du Plessis <adp(at)bank(dot)io> |
Cc: | "pgsql-performance(at)postgresql(dot)org list" <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: Best practice when reindexing in production |
Date: | 2013-05-29 12:38:38 |
Message-ID: | 1E599B61-C713-40CA-B8BB-73D7E6C2DCA9@autouncle.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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(at)bank(dot)io>:
>
> On Wed, May 29, 2013 at 2:26 PM, Magnus Hagander <magnus(at)hagander(dot)net> wrote:
> On Wed, May 29, 2013 at 8:24 AM, Niels Kristian Schjødt
> <nielskristian(at)autouncle(dot)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.
>
From | Date | Subject | |
---|---|---|---|
Next Message | Niels Kristian Schjødt | 2013-05-29 12:41:55 | Re: Best practice when reindexing in production |
Previous Message | Armand du Plessis | 2013-05-29 12:30:45 | Re: Best practice when reindexing in production |