Re: database size grows (even after vacuum (full and analyze))....

On Wed, May 03, 2006 at 18:15:37 +0100,
Joao Miguel Ferreira <jmf(at)estg(dot)ipvc(dot)pt> wrote:
> On Wed, 2006-05-03 at 15:45, Bruno Wolff III wrote:
> > On Wed, May 03, 2006 at 12:02:18 +0100,
> > Joao Miguel Ferreira <jmf(at)estg(dot)ipvc(dot)pt> wrote:
> > > certain life time for each record).
> > >
> > > Dispite this, the file system size still grows (at about 500KB in about
> > > 1000 minutes).
> >
> > How often are you vacuuming the table?
>
> I wrote that on my original e-mail: I do a "VACUUM ANALYZE" every 30
> minutes and a "VACUUM FULL" every 2 hours.

Sorry about that, I obviously missed that.

> Should I do anything else ? Thanks.

There are a couple of possibilities worth checking. One is that there aren't
idle transactions staying open for a long time. These would prevent vacuum
from removing deleted rows as these transactions could still see them.
Another possibility is that the FSM is too low and there isn't enough space to
track all of rows that can be recovered. (Vacuuming more often will also
reduce the needed FSM setting.) A third possible issue is index bloat, which
can happen on older versions (7.4ish) of Postgres where key values increase (or
decrease) montonicly.