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

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

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.