Free space mapping (was Re: Multi-Versions and Vacuum)

Andrew Sullivan <andrew@libertyrms.info> writes:
> But if I understand now what you're saying, it's this: the vacuum
> _does_ get everything, but if the free space map settings are too
> small, the free space map doesn't know about that free space;
> therefore, the space never gets re-used for future data, because as
> far as the postmaster is concerened, the space isn't available.  So
> the tables keep growing because they have to append their data at the
> end of the file (because the free space map hasn't reported any free
> space).  Run vacuum a few times like this, and there is a substantial
> amount of unused space in the tables, where the free space map
> doesn't have an entry for it.  Is that right?

Well, the design theory was that once you free enough space on a
particular page, it *will* get into the FSM and then will be filled up.
The fact that small amounts of free space don't get re-used quickly
doesn't bother me a whole lot; the table size should still stabilize
at some proportion of free to used space that depends on your vacuum
frequency.

However, if the FSM is vastly smaller than the number of
actively-changing pages in your database, then that argument breaks down
--- in particular, if the FSM can't even keep track of all the
completely-empty pages in your database then you are going to suffer
progressive leakage.  I'm not sure where the problem gets bad enough to
worry about, and have not had time to try to do a probabilistic
analysis.

If we could determine where trouble sets in, perhaps we could detect the
situation and print warnings during VACUUM?  Another possibility is to
auto-size FSM during postmaster startup, perhaps as a fraction of the
total DB size measured by "du".  (But that probably doesn't work if
you've been playing symlink games.)  In any case we need more knowledge
about appropriate FSM sizes than we have at the moment.

The whole FSM concept is new in 7.2 and I'm sure it still needs
refinement.  Ideas welcome.

            regards, tom lane