Re: index bloat - Mailing list pgsql-general

From David Esposito
Subject Re: index bloat
Date
Msg-id 200507081201.j68C1sWN006896@relay2.nnco.com
Whole thread Raw
In response to Re: index bloat  (Tom Lane <tgl@sss.pgh.pa.us>)
Responses Re: index bloat
List pgsql-general

> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: Thursday, July 07, 2005 11:53 PM
>
> "David Esposito" <pgsql-general@esposito.newnetco.com> writes:
> > Size of "problem" table: 6 million rows
> > Ballpark guess on INSERT/UPDATE/DELETE queries on this table: over 1
> > million/day
> > ...
> > I do a nightly VACUUM (not VACUUM FULL)
>
> Given those parameters, you should expect a "slack"
> proportion of about
> 1/6th of the table.  Many of the indexes you show seem to be in that
> ballpark --- do you have any idea what's different about the two that
> are not?
>

Index size is in MB      'Clean DB' 'Live DB'  'Slack'
------------------------------------------------------
campaign_patron_unq        215.5      498.1     282.6
campaign_email_pkey        143.1      295.3     152.1
email_patron_idx           143.1      290.8     147.7
referral_idx                95.2      223.7     128.5
email_campaign_idx         143.1      221.5      78.4
email_detail_last_mod_idx  126.1      161.6      35.5

Hmm, how are you getting 1/6? The ballpark seems to be about 50% or more for
those first 4 ... The thing is, they are all indexes on fields that don't
change over the life of the record so it would seem that the slack would
only build for DELETEs, not UPDATEs ... and the volume of DELETEs is
slightly less than 1 million/week (deleting the oldest records) ... the
campaign_email_pkey is a integer generated from a sequence so it would seem
like that index should be well-behaved since we always add to the right side
of the be of the btree and always delete from the left side ...

The numbers I'm showing above reflect about 4.5 weeks since the last full
REINDEX of the DB ... And they seem to be on a steady charge upward ... I've
been hoping that at some point they 'level off' and just have a constant
amount of slack in them but it seems like they're just growing without bound
..

Thanks again,
Dave


pgsql-general by date:

Previous
From: Peter Eisentraut
Date:
Subject: Re: Full outer join question.
Next
From: Tom Lane
Date:
Subject: Re: index bloat