Skip site navigation (1) Skip section navigation (2)

Re: Vacuum Full - stops responding(?)

From: Greg Stark <gsstark(at)mit(dot)edu>
To: Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Krowa Krowax <krowa333(at)gmail(dot)com>, pgsql-admin(at)postgresql(dot)org
Subject: Re: Vacuum Full - stops responding(?)
Date: 2009-10-20 03:46:10
Message-ID: 407d949e0910192046r7793e2e1sb53b61cfa60f40db@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-admin
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

In response to

Responses

pgsql-admin by date

Next:From: Tom LaneDate: 2009-10-20 03:56:45
Subject: Re: Vacuum Full - stops responding(?)
Previous:From: Scott MarloweDate: 2009-10-20 02:37:40
Subject: Re: Vacuum Full - stops responding(?)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group