Message-ID: <4C7165D1.1040402@2ndquadrant.com>
Date: Sun, 22 Aug 2010 14:00:49 -0400
From: Greg Smith <greg@2ndquadrant.com>
User-Agent: Thunderbird 2.0.0.24 (X11/20100317)
MIME-Version: 1.0
To: Alexandre de Arruda Paes <adaldeia@gmail.com>
CC: pgsql-performance@postgresql.org
Subject: Re: Fwd: Vacuum Full + Cluster + Vacuum full = non removable dead
 rows
References: <AANLkTi=3XOw+6_gpR5c9c-OZaVa3AoDeVVOkcR4zA8V7@mail.gmail.com>
	<AANLkTikZb-9XaC5=-fKNPYEK7J_q97Kjq9nsM=3kwgV2@mail.gmail.com>
	<AANLkTi=865eVqoej58M8W1afxHZXVzx1uqnvxb--LmgG@mail.gmail.com>
	<AANLkTikSM75f-GksqBt-CwZJxiDF0mFEH3T7UDUiZ9=3@mail.gmail.com>
	<AANLkTimS9cjYedVmC0UrwYU42ivd_TAw_=nFZEggDXP6@mail.gmail.com>
	<AANLkTi=kTvCFWX4Zgkw=s-JRrv_+caEu57KUJEeyufgy@mail.gmail.com>
	<AANLkTimwDa+kJZaLuoOa=6ve_O9Ka5sHiwFVXud9rOku@mail.gmail.com>
	<AANLkTikz1VQFLw0dm54cUtQ2_eWmg=R6HVKu2nUUA5+b@mail.gmail.com>
	<14512.1282137722@sss.pgh.pa.us>
	<AANLkTik=uHxUbUEzfZJWDRyxhdLemytsAdtzUpyn6kF-@mail.gmail.com>
	<4C6CEE4202000025000348D6@gw.wicourts.gov>
	<AANLkTinpa+932QM7c8SxhkAY4Gr7RHFb_F_HHX+T3J64@mail.gmail.com>
	<AANLkTikvRrKxehYBZ6vD5MbWGPGANX4mPwe1-vcFQz_b@mail.gmail.com>
	<AANLkTikDbe+j_JUNgH61aREdwm01+eChCy89eVyUaeR4@mail.gmail.com>
	<AANLkTi=wP4pEnuFCx529qWCwPuSMpbKc1RUEh3Lhugwr@mail.gmail.com>
In-Reply-To: <AANLkTi=wP4pEnuFCx529qWCwPuSMpbKc1RUEh3Lhugwr@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Alexandre de Arruda Paes wrote:
> Unfortunately, the customer can't wait for the solution and the 
> programmer eliminated the
> use of this table by using a in-memory array.

Well that will be fun.  Now they've traded their old problem for a new 
one--cache inconsistency between the data in memory and what sitting in 
the database.  The fun apart about that is that the cache mismatch bugs 
you'll run into are even more subtle, frustrating, and difficult to 
replicate on demand than the VACUUM ones.

> Only for discussion: the CLUSTER command, in my little knowledge, is a 
> intrusive command that's cannot recover the dead tuples too.
> Only TRUNCATE can do this job, but obviously is not applicable all the 
> time.

Yes, CLUSTER takes a full lock on the table and rewrites a new one with 
all the inactive data removed.  The table is unavailable to anyone else 
while that's happening.

Some designs separate their data into partitions in a way that it's 
possible to TRUNCATE/DROP the ones that are no longer relevant (and are 
possibly filled with lots of dead rows) in order to clean them up 
without using VACUUM.  This won't necessarily help with long-running 
transactions though.  If those are still referring to do data in those 
old partitions, removing them will be blocked for the same reason VACUUM 
can't clean up inside of them--they data is still being used by an 
active transaction.

-- 
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com   www.2ndQuadrant.us