Re: Index bloat in 7.2

Clinging to sanity, julian(dot)scarfe(at)ntlworld(dot)com ("Julian Scarfe") mumbled into her beard:
> I've got a box running 7.2.1 (yes, I know :-() in which an index for
> a rapidly turning over (and regularly vacuumed) table is growing
> steadily in size. The index in question is on a timestamp field
> that is just set to now() on the entry of the row, to enable the
> query that clears out old data to an archive to run efficiently.
> Reindexing shrinks it back to a reasonable size. Other indexes reach
> an equilibrium size and stay there. The behaviour is fine on a
> system running 7.4.x: the index stays at a sensible number of pages.
>
> Is this likely to be related to a known issue with 7.2 that got fixed, or
> have I got potentially more serious problems?

The "empty pages not reclaimed" problem is something that did indeed
get fixed in the post-7.2 days. I _think_ it was 7.4, but it might
have been 7.3.

When we were running 7.2, we used to fairly regularly (e.g. - about
every other month) need to schedule maintenance windows in order to
reindex tables in order to resolve this issue. Some indices on
heavily-update tables would get pretty big "dead zones" that only
reindexing would fix.

The last it was discussed, there still seemed to be a _theoretical_
possibility of there still being a pathological case even in 7.4, but
nobody has reported it in practice. That case would result if you
dropped down to 1 index entry remaining "live" per page. That would
be a very "sparse" handling of things, leaving >98% of the page empty,
and there's no obvious mechanism to merge such pages back together.

But as you're deleting _all_ old entries, that would clear out the
relevant index pages entirely, so that they could be reclaimed.

In short, 7.4.x is indeed a good resolution to your issue.
--
let name="cbbrowne" and tld="gmail.com" in name ^ "@" ^ tld;;
http://www3.sympatico.ca/cbbrowne/sgml.html
"I would guess that he really believes whatever is politically
advantageous for him to believe." -- Alison Brooks, referring to
Michael Portillo, on soc.history.what-if