Re: PG 7.2 on Linux: where's the space?

Jeff Boes wrote:
> We upgraded from 7.1 to 7.2 recently, and saw the used space on our
> database partition drop from something like 80% to under 20% (as
> measured by 'du'). Hurrah! However, it's about four weeks later and
> we're back to over 80% again. Is the solution:

This has been discussed a fair bit recently. I'll try attempt to help,
though I'm sure others are much more qualified.

>
> 1) More frequent VACUUM FULL operations?

Possibly. However, it's been noted that if you do a lot of deletes
and/or updates so that you have a lot of "dead" tuples, you probably
need to increase your "fsm_*" parameters. Search the newsgroup archive
for their names for advice on size.

>
> 2) REINDEX our biggest tables?

It has also been noted that indexes can do this too, again a high churn
rate is the cause. Off the top of my head I don't remember how to find
this out, but it's in the newsgroup archives too. But I think one of
the things you can do was:

select * from pg_class order by relpages desc;

to try to find out where the space is going. Search for something like
that.

>
> 3) Periodicly dump and reload our biggest tables?

I hope not! :-) Increase your fsm_* values and see what that does for
you, before you take more drastic measures.

HTH,
Kevin

>
> I'm really hoping this last one is not the answer, as we'd have to
> take the system offline for hours every week to accomplish this.