Date: Thu, 21 Apr 2005 15:33:05 -0400
From: Dave Chapeskie <pgsql@ddm.wox.org>
To: pgsql-perform <pgsql-performance@postgresql.org>
Subject: Re: Index bloat problem?
Message-ID: <20050421193305.GA84813@ddm.wox.org>
Mail-Followup-To: Dave Chapeskie <pgsql@ddm.wox.org>,
	pgsql-perform <pgsql-performance@postgresql.org>
References: <20050421173855.76286.qmail@web51410.mail.yahoo.com>
	<200504211044.48794.josh@agliodbs.com>
	<4267EFF9.2000908@rentec.com>
	<200504211128.43937.josh@agliodbs.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <200504211128.43937.josh@agliodbs.com>
User-Agent: Mutt/1.5.4i

On Thu, Apr 21, 2005 at 11:28:43AM -0700, Josh Berkus wrote:
> Michael,
> 
> > ....Every five minutes, DBCC INDEXDEFRAG will report to the user an
> > estimated percentage completed. DBCC INDEXDEFRAG can be terminated at
> > any point in the process, and *any completed work is retained.*"
> 
> Keen.  Sounds like something for our TODO list.
> 
> -- 
> Josh Berkus
> Aglio Database Solutions
> San Francisco

See http://archives.postgresql.org/pgsql-general/2005-03/msg01465.php
for my thoughts on a non-blocking alternative to REINDEX.  I got no
replies to that message. :-(


I've almost got a working solution integrated in the backend that does
correct WAL logging and everything.  (Writing the code to write and
replay WAL logs for complicated operations can be very annoying!)

For now I've gone with a syntax of:

  REINDEX INDEX btree_index_name INCREMENTAL;

(For now it's not a proper index AM (accessor method), instead the
generic index code knows this is only supported for btrees and directly
calls the btree_compress function.)

It's not actually a REINDEX per-se in that it doesn't rebuild the whole
index.  It holds brief exclusive locks on the index while it shuffles
items around to pack the leaf pages fuller.  There were issues with the
code I attached to the above message that have been resolved with the
new code.  With respect to the numbers provided in that e-mail the new
code also recycles more pages than before.

Once I've finished it up I'll prepare and post a patch.

-- 
Dave Chapeskie
OpenPGP Key ID: 0x3D2B6B34