RE: pg_attribute growing and growing and growing

> -----Original Message-----
> From: Brian Hirt
>
> Hi,
>
> I'm having a bit of trouble with the pg_attribute table growing larger
> and larger and larger. Actually that's now the real problem, it's
> the indexes that are the real problem. I run a site that get's a fair
> amount of traffic and we use temporary table extensively for some more
> complex queries (because by breaking down the queries into steps,
> we can get
> better performance than by letting postgres plan the query poorly) I
> assume that creating a temporary table and then dropping it will cause
> the pg_attribute table to grow because our pg_attribute grows by
> about 15MB
> per day and if it isn't vacuumed nightly the system slows down very
> quickly. After "vacuum analyze pg_attribute", the pg_attribute table is
> back to it's normal small size. However, the two indexes on
> pg_attribute do
> not shrink at all. The only way I've found to get around this is to
> dump, drop, create, reload the database. I don't really want to trust
> that to a script and I don't really like having the system down that much.
>

If you could stop postmaster,you could reacreate indexes
of pg_attribute as follows.

1) shutdown postmaster(using pg_ctl stop etc).
2) backup the index files of pg_attributes somewhere for safety.
3) invoke standalone postgres
postgres -P -O your_database_name
4) recreate indexes of pg_attribute
reindex table pg_attribute force;
5) exit standalone postgres
6) restart postmaster

Regards.

Hiroshi Inoue
Inoue(at)tpf(dot)co(dot)jp