Message-ID: <4AF229F5.201@2ndquadrant.com>
Date: Wed, 04 Nov 2009 20:27:17 -0500
From: Greg Smith <greg@2ndquadrant.com>
User-Agent: Thunderbird 2.0.0.23 (X11/20090817)
MIME-Version: 1.0
To: Dave Crooke <dcrooke@gmail.com>
CC: pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: Followup: vacuum'ing toast
References: <ca24673e0911041552k70b4af34k6f689ab116fc8752@mail.gmail.com>
In-Reply-To: <ca24673e0911041552k70b4af34k6f689ab116fc8752@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Dave Crooke wrote:
> Since the Jackrabbit tables are in the same namespace / user / schema
> as ours, am I right in thinking that this is effectively blocking the
> entire auto-vaccum system from doing anything at all?
>   
Yes, but the problem is actually broader than that:  it wouldn't matter 
if it was a different user or namespace, the impact would still be the 
same.  PostgreSQL gets rid of needing to hold a bunch of table/row locks 
by using an approach called MVCC:  
http://www.postgresql.org/docs/8.4/static/mvcc-intro.html

The biggest downside of that approach is that if you have an old client 
lingering around, things that happened in the database after it started 
can't be cleaned up.  That client might still be referring to the old 
copy of that data, so that anything it looks at will be a consistent 
snapshot that includes the earlier version of the rows, the database is 
paranoid about letting VACUUM clean the things you've deleted up.

In 8.4 this situation is improved for some common use cases.  In the 8.3 
you're using, an old transaction will block any VACUUM attempt from 
moving past that point in time forever.  You have to figure out how to 
get Hibernate to close the transaction it's leaving open for VACUUM to work.

--
Greg Smith    greg@2ndquadrant.com    Baltimore, MD