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.
