Postgres Pro
Downloads
Home   >   mailing lists

Thread: Indexes growing continuously

Indexes growing continuously

From
Daniele Orlandi
Date:
Hi,

I'm running PostgreSQL 6.5.3 on Linux-2.2.12 on a 2-way SMP machine.
I noticed that even after VACUUMing there are some indexes that still
remain very big.

For example, here's what I found for pg_attribute:

-rw-------   1 postgres postgres   122880 Apr  4 15:24 pg_attribute
-rw-------   1 postgres postgres 17055744 Apr  4 15:24
pg_attribute_attrelid_index
-rw-------   1 postgres postgres 50176000 Apr  4 15:24
pg_attribute_relid_attnam_index
-rw-------   1 postgres postgres 20758528 Apr  4 15:24
pg_attribute_relid_attnum_index

I know I can fix this by recreating the indexes, but I don't know how to
do it for a system table and if it's safe.

Any hints ?

TIA.

Regards.

Re: Indexes growing continuously

From
Mike Mascari
Date:
Daniele Orlandi wrote:
> 
> Hi,
> 
> I'm running PostgreSQL 6.5.3 on Linux-2.2.12 on a 2-way SMP machine.
> I noticed that even after VACUUMing there are some indexes that still
> remain very big.
> 
> For example, here's what I found for pg_attribute:
> 
> -rw-------   1 postgres postgres   122880 Apr  4 15:24 pg_attribute
> -rw-------   1 postgres postgres 17055744 Apr  4 15:24
> pg_attribute_attrelid_index
> -rw-------   1 postgres postgres 50176000 Apr  4 15:24
> pg_attribute_relid_attnam_index
> -rw-------   1 postgres postgres 20758528 Apr  4 15:24
> pg_attribute_relid_attnum_index
> 
> I know I can fix this by recreating the indexes, but I don't know how to
> do it for a system table and if it's safe.
> 
> Any hints ?

Unfortunately, this is a bug in PostgreSQL with respect to system
indexes. You can safely drop/create user indexes, but not system
ones. The only way to reclaim the space used is to dump/reload
your database. Under 7.0, I *believe* Hiroshi's REINDEX command
(please correct me someone if I'm wrong) will allow you to
reconstruct system indexes, but the root problem still exists...

Hope that helps, 

Mike Mascari

Re: Indexes growing continuously

From
Bruce Momjian
Date:
> > I know I can fix this by recreating the indexes, but I don't know how to
> > do it for a system table and if it's safe.
> > 
> > Any hints ?
> 
> Unfortunately, this is a bug in PostgreSQL with respect to system
> indexes. You can safely drop/create user indexes, but not system
> ones. The only way to reclaim the space used is to dump/reload
> your database. Under 7.0, I *believe* Hiroshi's REINDEX command
> (please correct me someone if I'm wrong) will allow you to
> reconstruct system indexes, but the root problem still exists...

pg_upgrade will allow this, without dump/reload of data.

--  Bruce Momjian                        |  http://www.op.net/~candle pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026