Subject: Re: Index bloat of 4x
From: Csaba Nagy <nagy@ecircle-ag.com>
To: Jeremy Haile <jhaile@fastmail.fm>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, "Ed L." <pgsql@bluepolka.net>,
	Postgres general mailing list <pgsql-general@postgresql.org>,
	Bill Moran <wmoran@collaborativefusion.com>,
	Alvaro Herrera <alvherre@commandprompt.com>
In-Reply-To: <1169233408.21922.1170154897@webmail.messagingengine.com>
References: <20070117093223.bcdb302e.wmoran@collaborativefusion.com>
	<200701181842.00696.pgsql@bluepolka.net>
	<1169197877.11526.10.camel@coppola.muc.ecircle.de>
	<200701191019.52768.pgsql@bluepolka.net>
	<17158.1169228703@sss.pgh.pa.us>
	<1169233408.21922.1170154897@webmail.messagingengine.com>
Content-Type: text/plain
Message-Id: <1169457850.2735.20.camel@coppola.muc.ecircle.de>
Mime-Version: 1.0
Date: Mon, 22 Jan 2007 10:24:10 +0100
Content-Transfer-Encoding: 7bit

On Fri, 2007-01-19 at 20:03, Jeremy Haile wrote:
> Is it feasible to add a "reindex concurrently" that doesn't lock the
> table for the rebuild, then locks the table when doing a second pass to
> pickup rows that were changed after the first pass?  Or something like
> that....

IIRC, the objection was the deadlock potential of any lock upgrade, and
the problems of impossible cleanup on failure if something changed the
permissions of the executing user in the meantime. That's why I think it
would make sense if it could be done by a privileged background thread
like the autovacuum ones, so the lock upgrade can be tried without
blocking, as it can take quite some time till it succeeds, and the
cleanup is possible due to the privileged nature of the executor. 

If there would be such a facility it would also need some policies to
control time windows and priorities just as for autovacuum, that's why I
connect it in my usage-focused mind to autovacuum.

Cheers,
Csaba.