After a long battle with technology, Mike.Samson@apcc.com, an earthling, wrote:
> I need a reliable way to determine the overall size of our database.
> Recently, I started using the database_size() function from the dbsize
> contrib.
>
> Everything seems to work great until I need to delete records.
> After I DELETE records, the pg_autovacuum daemon kicks in an runs a VACUUM.
>
> Once the VACUUM finishes, I run database_size() again and the size does not
> decrease.
>
> I would expect the database size to decrease after the VACUUM.
I wouldn't. VACUUM doesn't decrease database size; it only reclaims
empty pages for re-use.
After a VACUUM, there will be empty space ready to be reused by later
activity.
> I also don't want to run a VACUUM FULL because this kills the
> performance of the system.
Fair enough...
It is, however, only VACUUM FULL that will actually decrease the size
of the database.
With a regimen of running VACUUM reasonably often is that if the
database isn't steadily growing, you'll find that the size stabilizes,
with there being some portion of unused space that will accomodate
upcoming UPDATE/INSERT activity.
If the database *is* steadily growing, well, the unused space will
soon be used :-).
You might want to look in the contrib area for a function called
"pgstattuple" which does a detailed analysis, for any requested table,
as to what portion is live versus dead.
--
If this was helpful, <http://svcs.affero.net/rm.php?r=cbbrowne> rate me
http://cbbrowne.com/info/rdbms.html
THAT COMMAND IS NOT KNOWN TO THIS PROGRAM.
MAYBE YOU SHOULD LOG IN? (TYPE HELP FOR DETAILS)
