Re: Vacuum-full very slow

[Alvaro Herrera <alvherre(at)commandprompt(dot)com>]

Steve Crawford wrote:

> So my mental-model is utterly and completely wrong. My assumption was
> that since a full vacuum requires an access exclusive lock, it would do
> the intelligent and efficient thing which would be to first compact the
> table and then recreate the indexes.

Right, it doesn't do the intelligent and efficient thing.  There are
differences though: VACUUM FULL does not need an extra copy of the table
and indexes, while CLUSTER does.

OTOH, VACUUM FULL also needs to WAL log every action, which makes it
slower; CLUSTER only calls fsync when it's done, but since it keeps the
original files around it doesn't need to involve WAL.

> Am I reading that what it actually does is to thrash around keeping
> indexes unnecessarily updated, bloating them in the process?

Yes.

> Will cluster reduce the on-disk size like vacuum does?

Yes.  And a bit more because indexes don't suffer.

> And am I the only one who thinks the cluster command is backwards -
> after all it is the table that is being reordered based on an index so:

No, you're not, which is why a new syntax has been introduced for 8.3.

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.