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.
