From: Josh Berkus <josh@agliodbs.com>
To: Bill Chandler <billybobc1210@yahoo.com>
Cc: pgsql-perform <pgsql-performance@postgresql.org>
Subject: Re: Index bloat problem?
Date: Thu, 21 Apr 2005 10:42:38 -0700
Message-ID: <200504211042.38633.josh@agliodbs.com> (raw)
In-Reply-To: <20050421173855.76286.qmail@web51410.mail.yahoo.com>
References: <20050421173855.76286.qmail@web51410.mail.yahoo.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.
> 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, those dead nodes would be replaced with new index
nodes. However, a mass-delete-in-one-go messes that system up.
> 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
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: josh@agliodbs.com, billybobc1210@yahoo.com
Subject: Re: Index bloat problem?
In-Reply-To: <200504211042.38633.josh@agliodbs.com>
* 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