Re: Subject: bool / vacuum full bug followup

On Fri, May 03, 2002 at 12:20:44AM -0400, Tom Lane wrote:
> Jeffrey Baker <jwbaker(at)acm(dot)org> writes:
> > Well, if the table has an associated TOAST table, it is possible
> > that it will suddenly start growing out of control, and nothing you
> > can do with VACUUM will help. The TOAST system has a free space map
> > (FSM) with some default number of pages it can track. If you exceed
> > this number, it will become completely unable to reclaim space.
>
> That's a gross misstatement.
>
> If you have more pages with free space than the FSM can track, it will
> forget about the ones with the least free space (at least until the next
> vacuum tries to reload the info). This will very possibly lead to table
> growth over time, but it's hardly the same as "completely unable to
> reclaim space".

I expected someone to come out with this reply, but my own
monitoring in productions shows otherwise. I'll forward my data
along once I have completed my study.

The short of my observations is that once you have more free pages
than the FSM can track, the table grows linearly over time. This is
for a table with heavy insert and delete activity. This is perhaps
not the design intent but it is what we are seeing.

As I said I intend to send along unbearably detailed info once I
think I have mastered the problem.

> BTW, TOAST has nothing to do with this. Space in main tables and
> toast tables is tracked alike.

In my observation only TOAST tables have this behavior.

Regards
jwb