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.