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

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