Re: table size growing out of control

On Tue, 2002-07-16 at 19:18, Tom Lane wrote:
> Andrew Sullivan <andrew@libertyrms.info> writes:
> > But as I understand it, the standard, non-blocking vacuum marks
> > unused pages for reuse by the backend.  That approach can only
> > "remember" so many recovered pages.  Adjusting the free space map
> > setting improves that, so if you have a lot of turnover in your
> > tables, you can increase the FSM and vacuum more frequently.  You
> > still need up to double the size of the table, however, to
> > accommodate the turnover.
>
> Only if your vacuum schedule is to vacuum once per 100% turnover of
> the table contents.  If you vacuum as often as, say, 10% of the table
> rows are updated or deleted, then you should see the table size
> remaining at about 10% over the minimum possible size.  So it's a
> straight tradeoff of CPU expenditure versus disk space.  Tables that
> get a lot of update activity need to be vacuumed often to keep them
> from bloating.
>
> Or at least that's the theory.  It can fall down if your FSM size
> is too small to let all the free space be tracked.  We've also seen
> some reports since 7.2 release of tables growing when it didn't appear
> that they should, but I'm unconvinced yet whether those cases were
> PG bugs or application problems (eg, old open transactions preventing
> VACUUM from reclaiming space).
>

Is there a way to verify the existence of the old open transactions?

Robert Treat