Re: Index bloat problem in 7.4

Dave Cramer wrote:
>
> On 22-Sep-06, at 3:58 AM, Markus Schaber wrote:
>
> >Hi, Alvaro,
> >
> >Alvaro Herrera wrote:
> >
> >>>>I am aware that more recent versions > 8.x have fixed this
> >>>>problem, I
> >>>>checked the 7.4 release notes but can't see if any of the fixes
> >>>>made it
> >>>>into 7.4.
> >>>Usually, only critical data loss and security fixes are put into the
> >>>minor updates (e. G. 7.4.0 to 7.4.13). So, if it's not in the
> >>>release
> >>>notes, it is unlikely that it dit make it into the 7.4 releases.
> >>Except it was solved in 7.4:
> >>
> >>Allow B-tree index compaction and empty page reuse (Tom)
> >
> >Yes, you're right, the change is listed on
> >http://www.postgresql.org/docs/7.4/interactive/release-7-4.html
> >
> >So, as it _is_ in the release notes, this does not render my statement
> >wrong, but pointless :-)
>
> My understanding is that further work was done to address this in
> later versions as well.
>
> My client is experiencing index bloat in 7.4.x.

My guess is that they will still experience it even with 8.2, because
the situations on which the problem keeps happening have not, to my
knowledge, been addressed in later versions.

The main problem remaining is that partially filled paged are not
merged, so if you delete multiple old tuples (say, indexed by a
timestamp or a monotonously increasing key) and replace it with a single
"summary" tuple keyed with a value in the same range, the index page
where that tuple is stored will likely not contain a lot of other index
entries.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.