Re: Doubt w.r.t vacuum

Robert Treat <xzilla(at)users(dot)sourceforge(dot)net> writes:
>> This is overoptimistic :-(. VACUUM FULL cannot necessarily compact the
>> table completely, and so it will record free space in FSM (if there is
>> any worth recording). An example situation is that page 1000 may
>> contain a very large tuple, which will not fit on any earlier page.

> Isn't it possible that the reshuffling of tuples before page 1000 could
> open up enough space to move the overly large tuple?

Not in the same vacuum pass. Reshuffling opens *zero* space until you
commit the shuffling transaction, because you can't destroy the old
copies until you commit the moved ones.

You could imagine making multiple passes, but at that point it's almost
certainly faster to forget the VACUUM FULL approach entirely, and do
something more like CLUSTER: copy all the live tuples into a new file.

regards, tom lane