From: Dave Chapeskie <pgsql@ddm.wox.org>
To: pgsql-perform <pgsql-performance@postgresql.org>
Subject: Re: Index bloat problem?
Date: Thu, 21 Apr 2005 15:33:05 -0400
Message-ID: <20050421193305.GA84813@ddm.wox.org> (raw)
In-Reply-To: <200504211128.43937.josh@agliodbs.com>
References: <20050421173855.76286.qmail@web51410.mail.yahoo.com>
<200504211044.48794.josh@agliodbs.com>
<4267EFF9.2000908@rentec.com>
<200504211128.43937.josh@agliodbs.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: pgsql@ddm.wox.org
Subject: Re: Index bloat problem?
In-Reply-To: <20050421193305.GA84813@ddm.wox.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox