From: Bruce Momjian <bruce@momjian.us>
Message-Id: <200701310318.l0V3IWU08970@momjian.us>
Subject: Re: Index bloat of 4x
In-Reply-To: <1169457850.2735.20.camel@coppola.muc.ecircle.de>
To: Csaba Nagy <nagy@ecircle-ag.com>
Date: Tue, 30 Jan 2007 22:18:32 -0500 (EST)
CC: Jeremy Haile <jhaile@fastmail.fm>, 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>
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Content-Type: text/plain; charset="US-ASCII"


Added to TODO:

	* Add REINDEX CONCURRENTLY, like CREATE INDEX CONCURRENTLY
	
	  This is difficult because you must upgrade to an exclusive table lock
	  to replace the existing index file.  CREATE INDEX CONCURRENTLY does not
	  have this complication.  This would allow index compaction without
	  downtime.

I understand the problems, but the need for this seems pretty clear.

---------------------------------------------------------------------------

Csaba Nagy wrote:
> 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.
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>        choose an index scan if your joining column's datatypes do not
>        match

-- 
  Bruce Momjian   bruce@momjian.us
  EnterpriseDB    http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +