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

Re: Fwd: Vacuum Full + Cluster + Vacuum full = non removable dead rows

From: Greg Smith <greg(at)2ndquadrant(dot)com>
To: Alexandre de Arruda Paes <adaldeia(at)gmail(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Fwd: Vacuum Full + Cluster + Vacuum full = non removable dead rows
Date: 2010-08-22 18:00:49
Message-ID: 4C7165D1.1040402@2ndquadrant.com (view raw or flat)
Thread:
Lists: pgsql-performance
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(at)2ndQuadrant(dot)com   www.2ndQuadrant.us


In response to

pgsql-performance by date

Next:From: Jann RöderDate: 2010-08-23 04:23:38
Subject: Inefficient query plan
Previous:From: Greg SmithDate: 2010-08-22 17:53:04
Subject: Re: Fwd: Vacuum Full + Cluster + Vacuum full = non removable dead rows

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