Re: Question on REINDEX

Josh Berkus <josh@agliodbs.com> writes:
>> Not at all.  What it says is that you expect 100% of the pages to have
>> useful amounts of free space, which is a *much* weaker criterion.

> Hmmmm .... actually, it seems like, if you are vacuuming regularly, you only
> *do* need to track pages that have been touched by DELETE or UPDATE.   Other
> pages would have already been vacuumed and not have any useful free space
> left.   Yes?

Well, the space has to be remembered until it's reused.  On the other
hand, there's nothing that says FSM has to be aware of all the free
space available at all times --- the real criterion to avoid bloat
is that after a VACUUM, enough space is logged in FSM to satisfy all
the insertions that will happen before the next VACUUM.  So you could
have situations where free space is temporarily forgotten (for lack
of slots in FSM), but other free space gets used instead, and eventually
a later VACUUM re-finds that free space and puts it into FSM.

I think it's true that the more often you vacuum, the less FSM you need,
but this doesn't have much to do with how much free space is actually
out there on disk.  It's because you only need enough FSM to record the
free space you'll need until the next vacuum.

            regards, tom lane