Tom Lane wrote: > Alvaro Herrera <alvherre@xxxxxxxxxxxxxxxxx> writes: >> Steve Crawford wrote: >>> Am I reading that what it actually does is to thrash around keeping >>> indexes unnecessarily updated, bloating them in the process? > >> Yes. > > Just for the record, it's not "unnecessary". The point of that is to > not leave a corrupted table behind if VACUUM FULL fails midway through. > The algorithm is:... Yes, dig far enough under the covers and it all makes sense. Just curious would it be wise or even possible to create the functionality of an (oxymoronic) "vacuum full partial"? In other words, provide the ability to set a max-tuples or max-time parameter. Since you are looking for active tuples at the physical end of the file and moving them to unused space within the file, then lopping off the end it might be possible to do this in chunks to give control over how long a table is locked at any one time. Of course this doesn't improve the index-bloat issue. > CLUSTER avoids all this thrashing by recopying the whole table, but > of course that has peak space requirements approximately twice the > table size (and is probably not a win anyway unless most of the table > rows need to be moved). You pays your money, you takes your choice. That's certainly our case as we are archiving and purging 10s of millions of tuples from the prior year and have plenty of reserve disk-space. I killed the vacuum full after it passed the 5-hour mark. Cluster took 20 minutes with nice-compact indexes included. Thanks for the advice, everyone. Cheers, Steve
