Re: Followup: vacuum'ing toast

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(at)2ndquadrant(dot)com Baltimore, MD