From: Jesper Krogh <jesper@krogh.cc>
To: pgsql-performance@postgresql.org
Subject: Re: Best practice when reindexing in production
Date: Wed, 29 May 2013 19:12:55 +0200
Message-ID: <51A63717.7070301@krogh.cc> (raw)
In-Reply-To: <5359686B-FAE8-4056-AF13-F9FD6919FE82@autouncle.com>
References: <5359686B-FAE8-4056-AF13-F9FD6919FE82@autouncle.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
On 29/05/13 14:24, Niels Kristian Schjødt wrote:On 29/05/13 14:24, Niels
Kristian Schjødt 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?
Hi.
Since you still dont know wether it is worth it or not, I would strongly
suggest that you test this out before. Simply just creating an index
next to the old one with the same options (but different name) and
compare sizes would be simple.
Second, if the new index is significantly smaller than the old on, I
suggest that you try to crank up the autovacuum daemon instead of
blindly dropping and creating indexes, this will help to mitigate the
bloat you're seeing accumulating in above test.
Cranking up autovacuum is going to have significan less impact on the
concurrent queries while doing it and can help to maintain the database
in a shape where regular re-indexings shouldnt be nessesary. Autovacuum
has build in logic to sleep inbetween operations in order to reduce the
IO-load of you system for the benefit of concurrent users. The approach
of duplicate indices will pull all the resources it can get and
concurrent users may suffer while you do it..
Jesper
--
Jesper
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: jesper@krogh.cc
Subject: Re: Best practice when reindexing in production
In-Reply-To: <51A63717.7070301@krogh.cc>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox