Re: index bloat

> -----Original Message-----
> From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
> Sent: Thursday, July 07, 2005 11:53 PM
>
> "David Esposito" <pgsql-general(at)esposito(dot)newnetco(dot)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