From: Josh Berkus <josh@agliodbs.com>
Organization: Aglio Database Solutions
To: Bill Chandler <billybobc1210@yahoo.com>
Subject: Re: Index bloat problem?
Date: Thu, 21 Apr 2005 10:44:48 -0700
User-Agent: KMail/1.6.2
Cc: pgsql-perform <pgsql-performance@postgresql.org>
References: <20050421173855.76286.qmail@web51410.mail.yahoo.com>
In-Reply-To: <20050421173855.76286.qmail@web51410.mail.yahoo.com>
MIME-Version: 1.0
Content-Disposition: inline
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Message-Id: <200504211044.48794.josh@agliodbs.com>

Bill,

> Honestly, this seems like an inordinate amount of
> babysitting for a production application.  I'm not
> sure if the client will be willing to accept it.

Well, then, tell them not to delete 75% of the rows in a table at once.  I 
imagine that operation brought processing to a halt, too.

If the client isn't willing to accept the consequences of their own bad data 
management, I'm not really sure what you expect us to do about it.

> Admittedly my knowledge of the inner workings of an
> RDBMS is limited, but could somebody explain to me why
> this would be so?  If you delete a bunch of rows why
> doesn't the index get updated at the same time?  

It does get updated.  What doesn't happen is the space getting reclaimed.  In 
a *normal* data situation, the dead nodes are recycled for new rows.   But 
doing a massive delete operation upsets that, and generally needs to be 
followed by a REINDEX.

> Is 
> this a common issue among all RDBMSs or is it
> something that is PostgreSQL specific?  

Speaking from experience, this sort of thing affects MSSQL as well, although 
the maintenance routines are different.

-- 
Josh Berkus
Aglio Database Solutions
San Francisco