On Thu, Jul 18, 2024 at 7:08 AM David Rowley <dgrowle...@gmail.com> wrote: > On Wed, 10 Jul 2024 at 22:58, Tomas Vondra > <tomas.von...@enterprisedb.com> wrote: > > IIRC long time ago VACUUM FULL actually worked in a similar way, i.e. by > > moving rows around. I'm not sure if it did the lock-free thing as > > proposed here (probably not), but I guess at least some of the reasons > > why it was replaced by CLUSTER would still apply to this new thing. > > Yeah, that changed in 9.0. The old version still obtained an AEL on the > table. > > I think the primary issue with the old way was index bloat wasn't > fixed. The release notes for 9.0 do claim the CLUSTER method "is > substantially faster in most cases", however, I imagine there are > plenty of cases where it wouldn't be. e.g, it's hard to imagine > rewriting the entire 1TB table and indexes is cheaper than moving 1 > row out of place row.
The other thing I remember besides index bloat is that it was crushingly slow. My memory is pretty fuzzy after this long, but I feel like it was on the order of minutes to do VACUUM FULL when you could have done CLUSTER in seconds -- and then on top of the long wait you often ended up using more disk space at the end than you had at the beginning due to the index bloat. I remember being surprised by the decision to remove it entirely, but it sure was painful to use. -- Robert Haas EDB: http://www.enterprisedb.com
