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.