Re: Index bloat problem?

From: Josh Berkus <josh(at)agliodbs(dot)com>
To: Bill Chandler <billybobc1210(at)yahoo(dot)com>
Cc: pgsql-perform <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Index bloat problem?
Date: 2005-04-21 17:44:48
Message-ID: 200504211044.48794.josh@agliodbs.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Chris Browne 2005-04-21 17:47:24 Re: Index bloat problem?
Previous Message Josh Berkus 2005-04-21 17:42:38 Re: Index bloat problem?