Thread: Index bloat problem in 7.4
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. Did they ? Dave
Hi, Dave, Dave Cramer 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. You may try backporting it yourself, or use a cronjob or such issuing regular REINDEX commands during the night (or whenever you have idle hours). HTH; Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org
Markus Schaber wrote: > Hi, Dave, > > Dave Cramer 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) -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
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 :-) Thanks for your correction, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org
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. Dave > > Thanks for your correction, > Markus > -- > Markus Schaber | Logical Tracking&Tracing International AG > Dipl. Inf. | Software Development GIS > > Fight against software patents in Europe! www.ffii.org > www.nosoftwarepatents.org >
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.