Re: VACUUM FULL memory requirements

On Mon, Dec 14, 2009 at 1:55 PM, Kevin Grittner
<Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
> David Schnur <dnschnur(at)gmail(dot)com> wrote:
>
>> For a bit of extra context, the database is embedded within a
>> piece of software, which is the source of the uncertainty.  If it
>> was something I could manage myself, I would just run the VACUUM
>> FULL by hand at those times when I knew it was necessary.  As it
>> stands, it's not predictable, and users also tend to be less
>> accepting of having that space locked up.
>>
>> That's why I'm interested in more information on the memory & disk
>> requirements of the various maintenance functions.  The main
>> reason I've stayed away from CLUSTER is because of the disk
>> requirement.  But I had no idea it was possible for VACUUM FULL to
>> run out of memory under normal conditions, so I may need to
>> reconsider the options.
>
> If you had a utility which could be run when desired, which would
> incrementally move data from the end of a table to free space within
> the table, would that do?  It could be run while the table was in
> use for other purposes, but could cause occasional serialization
> failures for updates -- so you might want to schedule it for
> maintenance windows when possible.  Unlike the current VACUUM
> ANALYZE, stopping it would not lose the work done up to that point.
>
> (The above is being considered as a replacement for the current
> VACUUM FULL techniques where you don't have room for a second copy
> of the table.  If there's some reason that *wouldn't* work for you,
> now would be a good time to weigh in.)

My usage is dictated by using slony, which means I can't do the things
I'd like, like just copying the data out of the table, truncating it,
and copying it back in / renaming the newly created table as the old
one etc. So for me, and other slony users who can't do the wild
westish stuff we'd otherwise resort to, vacuum full is very useful in
some circumstances. So, an improved vacuum full would be much
appreciated. The proposed replacement, would it still be able to
recover lost space like the old one?