From: Greg Smith <greg@2ndquadrant.com>
To: Dave Crooke <dcrooke@gmail.com>
Cc: pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: Followup: vacuum'ing toast
Date: Wed, 04 Nov 2009 20:27:17 -0500
Message-ID: <4AF229F5.201@2ndquadrant.com> (raw)
In-Reply-To: <ca24673e0911041552k70b4af34k6f689ab116fc8752@mail.gmail.com>
References: <ca24673e0911041552k70b4af34k6f689ab116fc8752@mail.gmail.com>
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
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, dcrooke@gmail.com
Subject: Re: Followup: vacuum'ing toast
In-Reply-To: <4AF229F5.201@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