Message-ID: <4C338735.2080809@securityconfidence.com>
Date: Tue, 06 Jul 2010 15:42:45 -0400
From: Brian Helm <brian.helm@securityconfidence.com>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US;
	rv:1.9.1.9) Gecko/20100423 Lightning/1.0b1 Thunderbird/3.0.4
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: How To Calculate Table Size Minus Deleted Rows
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

I'm in the need of a way to determine the disk size of a table that
excludes dead tuples.  Here is my situation.  Our company would like to
provide a "rolling storage" solution on a per schema basis.  So
basically we set a "quota" of the amount of disk space that a schema can
occupy.  Every night a cron job is run that calculates the overage of
each schema's quota and deletes as many rows as necessary from each
table to bring the schema back within quota limits.

I was trying to use pg_total_relation_size to calculate the amount of
space consumed by each table, but I quickly learned that even after
deleting/vacuuming, the reported size of the table does not drop.  I
assume this is due to the nature of how a lazy vacuum works.  Is there a
way to get the size of the table that excludes the freed (but not
released) space from a delete/vacuum run?

-- 
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Brian Helm
Security Confidence Corporation
Brian.Helm@SecurityConfidence.com
513.388-4500/866.732.2661 Ext 106
www.SecurityConfidence.com