Re: Feedback on getting rid of VACUUM FULL

Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com> writes:
> I'm in favor of removing VACUUM FULL in 8.5. To replace it, we should offer:

> 1) VACUUM REWRITE, which is like CLUSTER but doesn't use an index, and

Check, although I'm not eager to make REWRITE a fully reserved word,
which is what this would entail. I would propose that we call this
VACUUM FULL.

> 2) Another utility that does something like UPDATE ... WHERE ctid > ? to
> move tuples to lower pages. It will be different from current VACUUM
> FULL in some ways. It won't require a table lock, for example, but it
> won't be able to move update chains as nicely.

I think it does require a table lock; you are ignoring the impact on
concurrent transactions of changing existing tuples' CTIDs (and XMINs).
In particular this could absolutely NOT be a standard part of plain
vacuum, despite all the wishful thinking going on downthread. But if
we get rid of old-style VACUUM FULL then we do need something to cover
those few-and-far-between situations where you really do desperately
need to compact a table in place; and a utility like this seems like a
reasonable solution. I'm thinking in particular that it should be
possible to have it move just a bounded number of tuples at a time,
so that you could do a VACUUM to clean out the indexes in between
move passes. Otherwise you run the risk of running out of disk space
anyway, due to index bloat.

regards, tom lane