From: Brian Helm <brian.helm@securityconfidence.com>
To: pgsql-sql@postgresql.org
Subject: How To Calculate Table Size Minus Deleted Rows
Date: Tue, 06 Jul 2010 15:42:45 -0400
Message-ID: <4C338735.2080809@securityconfidence.com> (raw)
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
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-sql@postgresql.org
Cc: brian.helm@securityconfidence.com
Subject: Re: How To Calculate Table Size Minus Deleted Rows
In-Reply-To: <4C338735.2080809@securityconfidence.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