Re: table size growing out of control

Andrew Sullivan <andrew(at)libertyrms(dot)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).

regards, tom lane