DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:date:message-id:subject:from:to:content-type;
	b=cQxkV8h5v1YPrW0pE60GlUeFoSZGs5bAryN0nQkolhlyLzcYjLrolTE/5ylu5tsSv2
	ksDKo6JetkZNPhixU5UhVceTkobsfQ77NsmqOyLdfOJNw+Pc0paHpykGDNyXZ/Vphy1V
	e7AgrrhZFG0sykasHAFPBBHF3FEEIQcFAsZ1I=
MIME-Version: 1.0
Date: Wed, 4 Nov 2009 15:18:46 -0600
Message-ID: <ca24673e0911041318p1c05c009l95c13b74dcb93b3@mail.gmail.com>
Subject: vacuum'ing toast crumbs, detecting dangling transactions
From: Dave Crooke <dcrooke@gmail.com>
To: pgsql-performance@postgresql.org
Content-Type: multipart/alternative; boundary=0016e6d99b4df8bc5404779227d2

--0016e6d99b4df8bc5404779227d2
Content-Type: text/plain; charset=ISO-8859-1

Hi folks

I had a couple of semi-newbie questions about this, which I couldn't find
obvious answers to in the archives ... we are using Postgres 8.3, and the
behaviour is the same across Windows and Linux.

I am working with an app which, among other things stores XML files (average
about 50KB in size) in blobs in Postgres (column type "text") which Postgres
puts in a pg_toast_nnnnn table. The pattern of access is that a group of a
few hundred new rows is written to the main table once every few hours, but
then the XML documents in that recent batch of rows will be updated about
once every 5 minutes each, until the next batch of new rows is created - in
that way, the contents of the table are the most recent version of each
document, plus a historical trail of one version every few hours.

I'm not defending the decision to store blobs in a database (it was taken a
while ago, before the need for frequent updates of the XML) and it isn't
something that can be readily changed at short notice, so please no advice
about "don't do that" :-)

Obviously, the app causes high turnover of rows in both the parent table and
the toast table, so it relies heavily on vacuum to keep the size down. There
is no DBA here and no Postgres tuning has been done yet (I plan to have a
poke, but my DB tuning experience is Oracle with a side of MySQL, I am a
Postgres newbie).

Questions:

1. When I run vacuum manually on the parent table with the application
running, it has no effect on either the parent or toast table (as reported
by the "pgstattuple" add-on), even when the table is showing 40-50% dead
tuples. However, if I disconnect the app, all the dead tuples clean up and
moved to the "free space" category.

Is this a normal amount of dead space, and if not, what does this mean? My
best guess is that (a) it's not normal, and (b) somewhere the app is holding
open an old transaction, so Postgres thinks it has to retain all that data.

2. If there is a hanging transaction, what's the best way to trace it from
the PG end? Client is classic Java (Spring / Hibernate / Apache DBCP) if
that matters.

Cheers
Dave

--0016e6d99b4df8bc5404779227d2
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Hi folks<br><br>I had a couple of semi-newbie questions about this, which I=
 couldn&#39;t find obvious answers to in the archives ... we are using Post=
gres 8.3, and the behaviour is the same across Windows and Linux.<br><br>
I am working with an app which, among other things stores XML files (averag=
e about 50KB in size) in blobs in Postgres (column type &quot;text&quot;) w=
hich Postgres puts in a pg_toast_nnnnn table. The pattern of access is that=
 a group of a few hundred new rows is written to the main table once every =
few hours, but then the XML documents in that recent batch of rows will be =
updated about once every 5 minutes each, until the next batch of new rows i=
s created - in that way, the contents of the table are the most recent vers=
ion of each document, plus a historical trail of one version every few hour=
s.<br>
<br>I&#39;m not defending the decision to store blobs in a database (it was=
 taken a while ago, before the need for frequent updates of the XML) and it=
 isn&#39;t something that can be readily changed at short notice, so please=
 no advice about &quot;don&#39;t do that&quot; :-)<br>
<br>Obviously, the app causes high turnover of rows in both the parent tabl=
e and the toast table, so it relies heavily on vacuum to keep the size down=
. There is no DBA here and no Postgres tuning has been done yet (I plan to =
have a poke, but my DB tuning experience is Oracle with a side of MySQL, I =
am a Postgres newbie).<br>
<br>Questions:<br><br>1. When I run vacuum manually on the parent table wit=
h the application running, it has no effect on either the parent or toast t=
able (as reported by the &quot;pgstattuple&quot; add-on), even when the tab=
le is showing 40-50% dead tuples. However, if I disconnect the app, all the=
 dead tuples clean up and moved to the &quot;free space&quot; category.<br>
<br>Is this a normal amount of dead space, and if not, what does this mean?=
 My best guess is that (a) it&#39;s not normal, and (b) somewhere the app i=
s holding open an old transaction, so Postgres thinks it has to retain all =
that data.<br>
<br>2. If there is a hanging transaction, what&#39;s the best way to trace =
it from the PG end? Client is classic Java (Spring / Hibernate / Apache DBC=
P) if that matters.<br><br>Cheers<br>Dave<br><br>

--0016e6d99b4df8bc5404779227d2--