How To Calculate Table Size Minus Deleted Rows

From: Brian Helm <brian(dot)helm(at)securityconfidence(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: How To Calculate Table Size Minus Deleted Rows
Date: 2010-07-06 19:42:45
Message-ID: 4C338735.2080809@securityconfidence.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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(dot)Helm(at)SecurityConfidence(dot)com
513.388-4500/866.732.2661 Ext 106
www.SecurityConfidence.com

Browse pgsql-sql by date

  From Date Subject
Next Message silly sad 2010-07-07 05:00:48 Re:
Previous Message Justin Graf 2010-07-06 17:52:51 Re: