Re: How often do I need to reindex tables?

Bill Moran <wmoran(at)collaborativefusion(dot)com> writes:
> In response to Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
>> Can you describe the usage pattern of that index? I'm curious why it
>> doesn't maintain reasonably static size. How often is the underlying
>> table vacuumed?
> ...
> There are 21 jobs, each ranging in size from 2000 - 5000 files. Each job
> runs twice a day. So you're looking at about 60,000 new rows at midnight
> and 60,000 new rows at noon each day. With the purge cycle, about the
> same number of rows are being deleted as are being added, so the table
> size stays pretty constant.
> ...
> Note that the index under discussion is the only one in this database that
> shows significant bloat.

Yeah, and there's no obvious reason in what you say why this one should
bloat either. Can you say anything about the distribution of the index
columns --- are you working with a fairly static set of filenameids, or
does that change over time? How about the pathids? How does the
combination of filenameid x pathid behave?

A bit of quick arithmetic says that the minimum possible size of that
index (at 100% fill factor) would be about 20K pages. What you showed
us was that it had expanded to 40-some K pages, or a bit under 50% fill
factor. This is low but not totally out of line; the traditional rule
of thumb is that the steady state fill factor will be about 2/3rds for a
heavily updated btree. If you leave it go, does it continue to get
larger, or stay around 40K?

regards, tom lane