Message-ID: <51A63717.7070301@krogh.cc>
Date: Wed, 29 May 2013 19:12:55 +0200
From: Jesper Krogh <jesper@krogh.cc>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:17.0) Gecko/17.0 Thunderbird/17.0
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: Best practice when reindexing in production
References: <5359686B-FAE8-4056-AF13-F9FD6919FE82@autouncle.com>
In-Reply-To: <5359686B-FAE8-4056-AF13-F9FD6919FE82@autouncle.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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