Hello All,
Can anybody from the forum (developer) confirm whether external
reindexing is not required with Postgres 7.4.
I added external reindexing after searching forum when my App which uses
Postgres (7.4.1) started giving error msgs like:
1)message type 0x44 arrived from server while idle.
http://archives.postgresql.org/pgsql-general/2002-06/msg00813.php
2)WARNING: specified item offset is too large
PANIC: failed to add item to the page for "cdr_transid_key"
server closed the connection unexpectedly This probably means the server
terminated abnormally before or while processing the request.
After working for a week with external reindexing, Things are working
well.
But I am not sure whether its really the reindexing factor as some of
you have suggested that : "indexes in PostgreSQL do not need maintenance
and tuning".
Also Can anybody tell me what are the overheads involved with DROPing
and CREATing indexes and how often (frequency) this activity should be
perform (? If really required ?) on heavily loaded tables.
My tables have 2M transaction per 6 hours.
I am performing "VACCUM ANALYSE" every 10 mins.
I really require some help as I am doing all these changes with my
production build App.
Thanks,
Rajan.
-----Original Message-----
From: Bruno Wolff III [mailto:bruno@wolff.to]
Sent: Monday, May 17, 2004 11:04 PM
To: Oliver Fromme
Cc: Rajan Bhide; pgsql-novice@postgresql.org
Subject: Re: [NOVICE] !!URGENT!! Should I keep INDEX on the table?
On Mon, May 17, 2004 at 10:09:32 +0200,
Oliver Fromme <olli@lurza.secnetix.de> wrote:
>
> I can't believe that's true. That would be a serious show- stopper
> bug in PostgreSQL, in my opinion. The documentation does not mention
> anywhere that periodically dropping and re- creating indices might be
> necessary. In fact, in chapter 11.8 it specifically says "indexes in
> PostgreSQL do not need maintenance and tuning".
There was a problem with reclaming dead space in btrees before 7.4. If
old data was deleted and new data added with the key for the new date
always increasing (or always decreasing), then the freed up pages would
never get reused. Changes were mage in 7.4 to alleviate this problem.
The developers weren't sure if there were still some usage patterns that
could result in a lot of wasted space. My memory is that there was an
O(n) upper bound on the space used now, but I might be wrong about that.