To: Kurt Overberg <kurt@hotdogrecords.com>
cc: Chris Browne <cbbrowne@acm.org>, pgsql-performance@postgresql.org
Subject: Re: Maintenance question / DB size anomaly... 
In-reply-to: <1FF54A1D-9085-4C9B-81F3-D860FB132D44@hotdogrecords.com> 
References: <A6D35557-74A3-47CF-B426-143C8CE01A13@hotdogrecords.com>
	<607iq0dlns.fsf@dba2.int.libertyrms.com>
	<1FF54A1D-9085-4C9B-81F3-D860FB132D44@hotdogrecords.com>
Comments: In-reply-to Kurt Overberg <kurt@hotdogrecords.com>
	message dated "Tue, 19 Jun 2007 13:13:23 -0400"
Date: Tue, 19 Jun 2007 17:33:39 -0400
Message-ID: <16441.1182288819@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Kurt Overberg <kurt@hotdogrecords.com> writes:
> mydb # vacuum verbose _my_cluster.sl_log_1 ;
> INFO:  "sl_log_1": found 455001 removable, 309318 nonremovable row  
> versions in 13764 pages
> DETAIL:  0 dead row versions cannot be removed yet.

Hmm.  So you don't have a long-running-transactions problem (else that
DETAIL number would have been large).  What you do have is a failure
to vacuum sl_log_1 on a regular basis (because there are so many
dead/removable rows).  I suspect also some sort of Slony problem,
because AFAIK a properly operating Slony system shouldn't have that
many live rows in sl_log_1 either --- don't they all represent
as-yet-unpropagated events?  I'm no Slony expert though.  You probably
should ask about that on the Slony lists.

> ...I then checked the disk and those pages are still there.

Yes, regular VACUUM doesn't try very hard to shorten the disk file.

> Would a VACUUM FULL take care of this?

It would, but it will take an unpleasantly long time with so many live
rows to reshuffle.  I'd advise first working to see if you can get the
table down to a few live rows.  Then a VACUUM FULL will be a snap.
Also, you might want to do REINDEX after VACUUM FULL to compress the
indexes --- VACUUM FULL isn't good at that.

			regards, tom lane