X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 7654F5282C for ; Sun, 3 Jul 2005 14:52:44 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 68688-06 for ; Sun, 3 Jul 2005 17:52:26 +0000 (GMT) Received: from news.hub.org (news.hub.org [200.46.204.72]) by svr1.postgresql.org (Postfix) with ESMTP id EDF4F52809 for ; Sun, 3 Jul 2005 14:52:25 -0300 (ADT) Received: from news.hub.org (news.hub.org [200.46.204.72]) by news.hub.org (8.13.1/8.13.1) with ESMTP id j63HqPaR081915 for ; Sun, 3 Jul 2005 17:52:25 GMT (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.13.1/8.13.1/Submit) id j63Ho3hV081161 for pgsql-admin@postgresql.org; Sun, 3 Jul 2005 17:50:03 GMT (envelope-from news) From: Christopher Browne X-Newsgroups: pgsql.admin Subject: Re: database size after a DELETE and VACUUM Date: Sun, 03 Jul 2005 13:49:11 -0400 Organization: cbbrowne Computing Inc Lines: 43 Message-ID: References: Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Complaints-To: usenet@news.hub.org X-message-flag: Outlook is rather hackable, isn't it? X-Home-Page: http://www.cbbrowne.com/info/ X-Affero: http://svcs.affero.net/rm.php?r=cbbrowne User-Agent: Gnus/5.1007 (Gnus v5.10.7) XEmacs/21.4 (Jumbo Shrimp, linux) Cancel-Lock: sha1:3i4RWe+7UKOf/MPNcp2qUKbkIOw= To: pgsql-admin@postgresql.org X-Virus-Scanned: by amavisd-new at hub.org X-Archive-Number: 200507/12 X-Sequence-Number: 18068 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, 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)