pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Greg Smith <greg@2ndquadrant.com>
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
Date: Sun, 22 Aug 2010 14:00:49 -0400
Message-ID: <4C7165D1.1040402@2ndquadrant.com> (raw)
In-Reply-To: <AANLkTi=wP4pEnuFCx529qWCwPuSMpbKc1RUEh3Lhugwr@mail.gmail.com>
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>

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




view thread (15+ messages)

Message-ID: <4C7165D1.1040402@2ndquadrant.com>
Permalink:  ../4C7165D1.1040402@2ndquadrant.com/
Also on:    postgresql.org/message-id/4C7165D1.1040402@2ndquadrant.com

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: greg@2ndquadrant.com, adaldeia@gmail.com
  Subject: Re: Fwd: Vacuum Full + Cluster + Vacuum full = non removable dead rows
  In-Reply-To: <4C7165D1.1040402@2ndquadrant.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox