Re: Multi-Versions and Vacuum

On Fri, Jul 19, 2002 at 12:35:23PM -0400, Tom Lane wrote:

> After VACUUM does its thing (and yes, it removes everything it legally
> can) it reports the amount of free space it found on each page to the
> FSM, and the FSM remembers as much of that info as it has room for ---
> preferring pages with more free space, if it has to be selective.

I think this is where the confusion lies.  The ever-growing tables
that people are experiencing is somehow related to the new
non-blocking vacuum and the value of the free space map settings.
Previous posts on the matter have left some (well, me, at least) with
the idea that the non-blocking vacuum doesn't get everything, just in
case the free space map values are too small.

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?

If so, it should be possible to calculate what one needs for the
max_fsm_pages setting by calculating how much data is replaced or
deleted in any given period, vacuum-to-vacuum, I guess.  What one
would need to do is calculate how much space a row is taking up on
disk, and then set the max_fsm_pages to comprise (almost?) enough
space to keep a map big enough to include all the rows likely to be
recovered by vacuum.  Or am I completely mad?  (I know, I know, "Yes"
is a possible answer there.)

A

--
----
Andrew Sullivan                               87 Mowat Avenue
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M6K 3E3
                                         +1 416 646 3304 x110