Thread: Index file size keeps on growing.....

Hi all,
    I'm in front of a real problem (from my point of view). I have some tables
with a lot of update insert and delete (24/7). These tables have multiples
indexes. Major part of Insert commands are performed as bulk at 00:00:00. I'm
doing Vaccum and then a vacuum analyze to clean tables (well done) and update
indexes (also performed) BUT index are not "cleaned" in the way their size
keeps on growing in size...
    The real problem is that I have tables that takes about 20MB each and index
around 190MB (for some of them). The only way I found to reduce index file
size is to destroy the incriminated indexes and recreate, praying for select
not to happen between drop and create operation (I cannot afford table
lock)...

    So is this a correct Postgres Behaviour?
    Do I have a neat way to make my index keep a reasonnable size?
    Did I missed something in the config file and/or with the vacuum commands?

Thanks for any clue!!

--
Jean-Christophe ARNU
s/w developer
Paratronic France

Hi Jean,

Have you tried REINDEX?
what version of postgresql is it?

regds
mallah.

On Thursday 23 May 2002 01:10 pm, Jean-Christophe ARNU (JX) wrote:
> Hi all,
>     I'm in front of a real problem (from my point of view). I have some tables
> with a lot of update insert and delete (24/7). These tables have multiples
> indexes. Major part of Insert commands are performed as bulk at 00:00:00.
> I'm doing Vaccum and then a vacuum analyze to clean tables (well done) and
> update indexes (also performed) BUT index are not "cleaned" in the way
> their size keeps on growing in size...
>     The real problem is that I have tables that takes about 20MB each and
> index around 190MB (for some of them). The only way I found to reduce index
> file size is to destroy the incriminated indexes and recreate, praying for
> select not to happen between drop and create operation (I cannot afford
> table lock)...
>
>     So is this a correct Postgres Behaviour?
>     Do I have a neat way to make my index keep a reasonnable size?
>     Did I missed something in the config file and/or with the vacuum commands?
>
> Thanks for any clue!!

--
Rajesh Kumar Mallah,
Project Manager (Development)
Infocom Network Limited, New Delhi
phone: +91(11)6152172 (221) (L) ,9811255597 (M)

Visit http://www.trade-india.com ,
India's Leading B2B eMarketplace.