Re: [HACKERS] 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 Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Index bloat problem in 7.4
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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Index bloat problem in 7.4
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 signature.asc Description: OpenPGP digital signature
Re: [HACKERS] Index bloat problem in 7.4
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 Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Index bloat problem in 7.4
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 signature.asc Description: OpenPGP digital signature
