Re: Vacuum Full - stops responding(?)

On Mon, Oct 19, 2009 at 7:37 PM, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> wrote:
>> It's trying to repack your table in place.  On the whole, I'd suggest
>> you cancel the vacuum and try a CLUSTER or some such instead.
>
> Unless that table's already in (mostly) index order, even cluster is
> gonna be pretty painful.
> I'd suggest a dump and reload myself.

The run-time of CLUSTER doesn't vary very much based on whether the
data is already in index order or not. The number of passes only grows
like log(n) of the size of your data and if you set
maintenance_work_mem large enough (somewhere around 100MB-1GB) the
constants are small enough that you're unlikely to even outgrow a
single pass (plus a final merge though)

That said one sort is still more than zero. So a dump/reload is always
going to be competitive depending on how slow your data is to compare
versus how slow it is to convert to text and back (and how much larger
the text is than the native format)

--
greg