Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 80BB82E007F for ; Sat, 1 Mar 2008 00:23:43 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 87370-05 for ; Sat, 1 Mar 2008 00:23:39 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.174]) by postgresql.org (Postfix) with ESMTP id 644872E01E4 for ; Sat, 1 Mar 2008 00:23:39 -0400 (AST) Received: by ug-out-1314.google.com with SMTP id j3so611191ugf.49 for ; Fri, 29 Feb 2008 20:23:38 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; bh=AA2Rf188QVkj5IIglRolBDQrUupmecrg/QUctcOrGrQ=; b=hZNPSyznwJgKtRQi/L1UHnuFJh9kEpZuxs/xb05mz5EqB6sdrOb8jjC38QhIcfjYAHkCgTrwZpSoTFGIG0QXRoUD7e/QrvE9nABkuz+beNHWlT3ZPrbSYQ3z7cWtgcHZCkV132abyxb2vOb5To409daUUc6q2BPc4S/GZIPkfMs= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=KBgmHKhRENI3ZyYlQFLnU3BjMhkYw6UT6iSbYw3bsVN1pd1LSj6XzqIGD8PSVd2pokDIJ0CruL48jxPNHHBVcPk40P/qDLfut5Tr8LFpsBkgwcgjJELSP4jxUlOkf45Oiez3IKALxNooISUxGWED6eMXj0jJDuR+FlMxBd8xVSk= Received: by 10.114.120.1 with SMTP id s1mr12731396wac.107.1204345417000; Fri, 29 Feb 2008 20:23:37 -0800 (PST) Received: by 10.114.133.6 with HTTP; Fri, 29 Feb 2008 20:23:37 -0800 (PST) Message-ID: Date: Fri, 29 Feb 2008 22:23:37 -0600 From: "Scott Marlowe" To: "Antonio Grassi" Subject: Re: drop database, vacuum full and disk space Cc: pgsql-admin@postgresql.org In-Reply-To: <7fb931000802280917u40d75389t791954b8ea8e00eb@mail.gmail.com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <7fb931000802280917u40d75389t791954b8ea8e00eb@mail.gmail.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200803/1 X-Sequence-Number: 28484 On Thu, Feb 28, 2008 at 11:17 AM, Antonio Grassi wrote: > Hi list. I've searched the archives and I've seen a lot of posts related to > database size, vacuuming, etc., but I can't totally understand this. > > I've a small database of about 300Mb (when initially loaded), whose data is > almost completely replaced in the night (via various "drop table" and the > load of dumps of such tables from other postgresql installation). > > Altough I've autovacuum activated, the size of this database is now about > 3Gb. I've also full vacuumed the base by hand from time to time since the > base was created, but that didn't help either. > > Yesterday I recreated the database with another name, thus having database A > with 3Gb and database B with 300Mb. Then I made a "drop database A", and ran > vacuumdb -af. But the space used by postgresql didn't go down. This makes me > think that vacuum full just releases pages associated to existent database > objects, is this correct? If you drop all but the template0/1 databases, I'm guessing you'll still see 3G used, because it sounds like you've got bloat in your system tables, like pg_class etc. Just guessing. You can use oid2name and du -sh to get an idea how much each db dir is using.