On Thu, Jun 4, 2009 at 7:31 AM, Erik Aronesty<erik@q32.com> wrote:
> is there some way to view the level of "bloat that needs full" in each
> table, so i could write a script that alerts me to the need of a
> "vacuum full" without waiting for random queries to "get slow"?
>
> looking at the results of the "bloat query", i still can't see how to
> know whether bloat is getting bad in an objective manner.
One other thought on this... I think the main thing to consider is
bloat as a percentage of table size. When you go to sequential scan
the table, a table with as much bloat as data will take twice as long
to scan, one with twice as much bloat as data will take three times as
long to scan, and so on.
If you're only ever doing index scans, the effect will be less
noticeable, but in round figures comparing the amount of bloat to the
amount of data is a good place to start. I usually find 3x is about
where the pain starts to hit. Also, small tables can sometimes
tolerate a higher percentage of bloat than large ones, because those
table scans tend to be fast anyway.
A lot of times bloat happens at one particular time and just never
goes away. Leaving an open transaction around for an hour or two can
bloat all of your tables, and they'll never get de-bloated on their
own without help. It would be nice if VACUUM had even a little bit of
capability for incrementally improving this situation, but currently
it doesn't. So when you mention running for a year, it's not unlikely
that you had one bad day (or several days in a row) when you collected
all of that bloat, rather than accumulating it gradually over time.
...Robert