Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtp (Exim 4.72) (envelope-from
	<pgsql-performance-owner+M50001=pgsql-performance-ar@postgresql.org>)
	id 1Uhk7u-0007U1-CZ for pgsql-performance@arkaria.postgresql.org;
	Wed, 29 May 2013 17:25:30 +0000
Received: from localhost ([127.0.0.1] helo=postgresql.org)
	by malur.postgresql.org with smtp (Exim 4.72) (envelope-from
	<pgsql-performance-owner+M50001=pgsql-performance-ar@postgresql.org>)
	id 1Uhk7t-0001fa-R7 for pgsql-performance@arkaria.postgresql.org;
	Wed, 29 May 2013 17:25:29 +0000
Received: from makus.postgresql.org ([2001:4800:7903:4::125])
	by malur.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <daniele.varrazzo@gmail.com>) id 1Uhk7r-0001eA-OV
	for pgsql-performance@postgresql.org; Wed, 29 May 2013 17:25:27 +0000
Received: from mail-wg0-x22d.google.com ([2a00:1450:400c:c00::22d])
	by makus.postgresql.org with esmtp (Exim 4.80)
	(envelope-from <daniele.varrazzo@gmail.com>) id 1Uhk7o-0005lB-Vo
	for pgsql-performance@postgresql.org; Wed, 29 May 2013 17:25:27 +0000
Received: by mail-wg0-f45.google.com with SMTP id n12so6745800wgh.12
	for <pgsql-performance@postgresql.org>;
	Wed, 29 May 2013 10:25:22 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113;
	h=message-id:subject:from:to:cc:date:in-reply-to:references
	:content-type:x-mailer:content-transfer-encoding:mime-version;
	bh=L6xT1DXYyqi3uupO0meM/FflhA2FOCFJf6Z6ZBwxbvY=;
	b=jGYrryGAb3Vn0EkoEvQ0R9m4V4SDAeJVWwtB2TReqBBI+q9Y3lqtTxnx8u0sCrJq+O
	Vz/Pp//0ZwBcqYTn9ilwOikWWF6YBDO6lroT5gqDCCBNhQ+4qSIW9bA7BBL1Yx1VoZi4
	vjeXWKyH2fqr/u4m9XPOKnrhd1GkcP2JI6qzkapdXedvgQU0alz/aKJ7zoUrLNaj405K
	8d+/EIfGfJRuTz1Nl63vB0NQxbBkXYacNMpYBJ6cHS19IZDinpbUtpqSW1Wm7q8wa43S
	xZAd+H/kr90FfWL4xzV3VwHqAMJoWL/VnEWSFtP/5rDqtOwxbXTyS2U3QRkDo0b5VdOC
	2Jaw==
X-Received: by 10.180.76.194 with SMTP id m2mr2083404wiw.4.1369848322809;
	Wed, 29 May 2013 10:25:22 -0700 (PDT)
Received: from [10.122.65.106] (colet177.gambitresearch.com. [79.123.98.177])
	by mx.google.com with ESMTPSA id
	k10sm32616026wia.4.2013.05.29.10.25.21 for <multiple recipients>
	(version=SSLv3 cipher=RC4-SHA bits=128/128);
	Wed, 29 May 2013 10:25:22 -0700 (PDT)
Message-ID: <1369848321.28819.31.camel@risotto.smithersbet.com>
Subject: Re: Best practice when reindexing in production
From: Daniele Varrazzo <daniele.varrazzo@gmail.com>
To: Jesper Krogh <jesper@krogh.cc>
Cc: pgsql-performance@postgresql.org
Date: Wed, 29 May 2013 18:25:21 +0100
In-Reply-To: <51A63717.7070301@krogh.cc>
References: <5359686B-FAE8-4056-AF13-F9FD6919FE82@autouncle.com>
	<51A63717.7070301@krogh.cc>
Content-Type: text/plain; charset="UTF-8"
X-Mailer: Evolution 3.2.3-0ubuntu6 
Content-Transfer-Encoding: 7bit
Mime-Version: 1.0
X-Pg-Spam-Score: 0.7 (/)
List-Archive: <http://archives.postgresql.org/pgsql-performance>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-performance.postgresql.org>
List-Owner: <mailto:pgsql-performance-owner@postgresql.org>
List-Post: <mailto:pgsql-performance@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-performance>
List-Unsubscribe: 
 <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
X-Mailing-List: pgsql-performance
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

On Wed, 2013-05-29 at 19:12 +0200, Jesper Krogh wrote:

> 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.

In my experience vacuum/autovacuum just don't reclaim any space from the
indexes, which accumulate bloat indefinitely. I've tried to work around
that in so many ways: the show-stopper has been the impossibility to
drop FK indexes in a concurrent way, coupled with VALIDATE CONSTRAINT
not doing what advertised and taking an exclusive lock.

My solution has been to become pg_repack maintainer. YMMV. Just don't
expect vacuum to reduce the indexes size: it doesn't.


-- 
Daniele



-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance