Re: Feedback on getting rid of VACUUM FULL

Josh Berkus wrote:
> Hackers,
>
> Here's the feedback on replacing VACUUM FULL with VACUUM REWRITE:
> http://it.toolbox.com/blogs/database-soup/getting-rid-of-vacuum-full-feedback-needed-33959
>
> Of note:
>
> a) To date, I have yet to hear a single person bring up an actual
> real-life use-case where VACUUM FULL was desireable and REWRITE would
> not be. Lots of people have said something hypothetical, but nobody has
> come forward with a "I have this database X and several times Y
> happened, and only FULL would work ...". This makes me think that there
> very likey are no actual use cases where we need to preserve FULL.

Well, Andrew McNamara just posted today:
http://archives.postgresql.org/message-id/20090916063341.0735C5AC0D6@longblack.object-craft.com.au

Had VACUUM FULL not been available, though, I'm pretty sure he would've
come up with something else instead.

> c) Vivek had some points about required implementation:
>
> "However, there still must be a way to compact the tables that is mvcc
> safe. From what I have read and recall, cluster is not. Thus, the vacuum
> rewrite would be a mandatory feature (or cluster could be made mvcc safe)."
>
> Is Vivek correct about this? News to me ...

No, that was fixed in 8.3.

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.

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
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.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com