Re: index bloat

On Wed, Jul 13, 2005 at 05:39:33PM -0400, Tom Lane wrote:

> (Memo to hackers: this is a fairly interesting case for autovacuum
> I think. The overall update rate on the table is not high enough to
> trigger frequent vacuums, unless autovacuum is somehow made aware that
> particular index key ranges are getting hit more heavily than others.
> Maybe this says that autovac needs to be tracking btree index page
> splits, or some such statistic, more than just overall updates.)

Interesting. I wonder exactly what metric do we want to track in the
first place. Just the number of page splits does not necessarily mean a
lot -- it could be a table that is under heavy insertion, with no dead
tuples. I guess we could do something with the ratio of dead tuples vs.
new tuples (in this case this seems to be close to 1, rather than 0,
which would be the case I mention above), times number of btree page
splits since last vacuum.

If the number is "high" then we need to vacuum. I guess the threshold
needs to be related to average key length.

--
Alvaro Herrera (<alvherre[a]alvh.no-ip.org>)
"Saca el libro que tu religión considere como el indicado para encontrar la
oración que traiga paz a tu alma. Luego rebootea el computador
y ve si funciona" (Carlos Duclós)