DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:message-id:date:from:user-agent:mime-version:to:cc:subject:references:in-reply-to:content-type:content-transfer-encoding:sender;
	b=ch9YyKpSjwJ2DFa40MUp/dox7gXuaRihPxSHG3QQy/ynEavmWqMeeALBFnzF4A/eXbKRKoQavNZ+LKPSwyJBla4KcuCOZ4WAmAw6KvwdsTfgm+T3vZa0u0Q/6MzrWg51C8xqW9tnYASqXAEMuPA6sBY6ng/iX1NAvtRBDtYS6EY=
Message-ID: <45EC2CCF.8020906@enterprisedb.com>
Date: Mon, 05 Mar 2007 14:44:31 +0000
From: Heikki Linnakangas <heikki@enterprisedb.com>
User-Agent: Icedove 1.5.0.8 (X11/20061116)
MIME-Version: 1.0
To: Bruce McAlister <bruce.mcalister@blueface.ie>
CC: pgsql-performance@postgresql.org
Subject: Re: PostgreSQL 8.2.3 VACUUM Timings/Performance
References: <esh2mg$16vu$1@news.hub.org> <45EC1931.3020701@enterprisedb.com>
	<esh858$2iiv$1@news.hub.org>
In-Reply-To: <esh858$2iiv$1@news.hub.org>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Bruce McAlister wrote:
> Hi Heikki,
> 
> Thanks for the reply.
> 
> The RAID array was implemented due to a projected growth pattern which 
> incorporate all 18 of our databases. The sizings I mentioned only refer to 1 
> of those databases, which, is also the most heavily used database :)
> 
> If I understand you correctly, we could in essence change our maintenance 
> routine to the follwing:
> 
> [1] Cluster on most used index
> [2] Perform a vacuum analyze on the table
> 
> If I read your post correctly, this will regenerate the index that the 
> cluster is performed on (1 of 3) and also re-generate the table in the 
> sequence of that index?

That's right. In fact, even cluster probably doesn't make much 
difference in your case. Since the table fits in memory anyway, the 
physical order of it doesn't matter much.

I believe you would be fine just turning autovacuum on, and not doing 
any manual maintenance.

> If that is the case, why would anyone use the vacuum full approach if they 
> could use the cluster command on a table/database that will regen these 
> files for you. It almost seems like the vacuum full approach would, or 
> could, be obsoleted by the cluster command, especially if the timings in 
> their respective runs are that different (in our case the vacuum full took 
> 15 minutes in our worst case, and the cluster command took under 1 second 
> for the same table and scenario).

In fact, getting rid of vacuum full, or changing it to work like 
cluster, has been proposed in the past. The use case really is pretty 
narrow; cluster is a lot faster if there's a lot of unused space in the 
table, and if there's not, vacuum full isn't going to do much so there's 
not much point running it in the first place. The reason it exists is 
largely historical, there hasn't been a pressing reason to remove it either.

-- 
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com