Re: Feedback on getting rid of VACUUM FULL

On Wed, 2009-09-16 at 21:23 +0300, Heikki Linnakangas wrote:

> I was just going to post that we should make a decision about this,
> because ISTM there's some code in Simon's hot standby patch that is only
> required to support VACUUM FULL. If we make the decision that we drop
> VACUUM FULL in 8.5, we can take that part out of the patch now. It's not
> a huge amount of code, but still.

All it saves is a few hacks, which realistically don't cause anything
more than an eyesore. VF has been ugly for years so we don't need to
react quickly and I don't want to delay HS. Please let's not focus on
side problems.

> 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

I think that can be called VACUUM FULL also. We are just changing the
internal implementation after all. There are too many scripts that
already invoke VF to ask people to rewrite.

> 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. But it would be trivial
> to write one, so I think we should offer that as a contrib module.

Hmmm, I think such a utility could easily cause more complaints than
VACUUM FULL unless we had a few other things as well. It doesn't move
update chains so it will mean that the table will not be able to shrink
immediately, nor even for a long time afterwards if there are long
queries. If a table were concurrently updated then this would not help
at all, unless the FSM channelled *all* backends carefully to parts of
the table that would help the process rather than hinder it. It will
also bloat indexes just as VF does now. REINDEX CONCURRENTLY would help
with that and we need it anyway for other reasons - and it needs to be
invoked by autovacuum.

A better way would be to have the FSM sense that packing was needed and
then alter the path transactions take so that they naturally begin to
repack the table over time. That way we wouldn't need to run a utility
at all in most cases.

--
Simon Riggs www.2ndQuadrant.com