Re: Feedback on getting rid of VACUUM FULL

On Tue, Apr 28, 2015 at 11:32 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> On Fri, Apr 24, 2015 at 3:04 PM, Alvaro Herrera
> <alvherre(at)2ndquadrant(dot)com> wrote:
>
> > I think what we need here is something that does heap_update to tuples
> > at the end of the table, moving them to earlier pages; then wait for old
> > snapshots to die (the infrastructure for which we have now, thanks to
> > CREATE INDEX CONCURRENTLY); then truncate the empty pages. Of course,
> > there are lots of details to resolve. It doesn't really matter that
> > this runs for long: a process doing this for hours might be better than
> > AccessExclusiveLock on the table for a much shorter period.
>
> Why do you need to do anything other than update the tuples and let
> autovacuum clean up the mess?
>

It could take a long time before autovacuum kicked in and did so. I think
a lot of time when people need this, the lack of space in the file system
is blocking some other action they want to do, so they want a definitive
answer as to when the deed is done rather than manually polling the file
system with "df". You could invoke vacuum manually rather than waiting for
autovacuum, but it would kind of suck to do that only to find out you
didn't wait long enough for all the snapshots to go away and so no space
was actually released--and I don't think we have good ways of finding out
how long is long enough. Ways of squeezing tables in the background would
be nice, but so would a way of doing it in the foreground and getting a
message when it is complete.

Cheers,

Jeff