Received: from localhost (maia-1.hub.org [200.46.204.191])
	by postgresql.org (Postfix) with ESMTP id 621E89FB1B9
	for <pgsql-general-postgresql.org@postgresql.org>;
	Fri, 19 Jan 2007 13:20:31 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.191]) (amavisd-new, port 10024)
	with ESMTP id 38991-05 for
	<pgsql-general-postgresql.org@postgresql.org>;
	Fri, 19 Jan 2007 13:20:21 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.4
Received: from alnrmhc14.comcast.net (alnrmhc14.comcast.net [206.18.177.54])
	by postgresql.org (Postfix) with ESMTP id E67949FB449
	for <pgsql-general@postgresql.org>;
	Fri, 19 Jan 2007 13:19:57 -0400 (AST)
Received: from [10.1.2.103]
	(c-75-70-110-235.hsd1.co.comcast.net[75.70.110.235])
	by comcast.net (alnrmhc14) with ESMTP
	id <20070119171956b1400rngj2e>; Fri, 19 Jan 2007 17:19:57 +0000
From: "Ed L." <pgsql@bluepolka.net>
To: Csaba Nagy <nagy@ecircle-ag.com>
Subject: Re: Index bloat of 4x
Date: Fri, 19 Jan 2007 10:19:52 -0700
User-Agent: KMail/1.8.1
Cc: Postgres general mailing list <pgsql-general@postgresql.org>,
	Bill Moran <wmoran@collaborativefusion.com>, Tom Lane <tgl@sss.pgh.pa.us>,
	Alvaro Herrera <alvherre@commandprompt.com>
References: <20070117093223.bcdb302e.wmoran@collaborativefusion.com>
	<200701181842.00696.pgsql@bluepolka.net>
	<1169197877.11526.10.camel@coppola.muc.ecircle.de>
In-Reply-To: <1169197877.11526.10.camel@coppola.muc.ecircle.de>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="utf-8"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200701191019.52768.pgsql@bluepolka.net>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200701/1073
X-Sequence-Number: 107613

On Friday January 19 2007 2:11 am, Csaba Nagy wrote:
>
> > I afraid I don't see how any of the answers I saw discussed
> > fit a 24x7 operation.  Reindex, drop index, vacuum full, ...
> > they all block production queries of one sort or another for
> > significant periods of time (minutes) on large (multi/tens
> > of GB) tables, and thus are infeasible for true 24x7
> > operations.[snip]
>
> This is not completely true, as of 8.2 there is an online
> index build, and if that could be used in a background thread
> to rebuild the index and replace the bloated one once it's
> finished, that would be a non-blocking operation which could
> be done in 24x7 situations.

Online index creation definitely helps us toward 24x7.  But 
wouldn't we still have to drop the old index, thus blocking 
production queries?

Ed