Re: Vacuum-full very slow

Tom Lane wrote:
> Alvaro Herrera <alvherre(at)commandprompt(dot)com> 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