Received: from localhost (unknown [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id E69192E01D8 for ; Fri, 4 Jan 2008 02:03:35 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 85041-05 for ; Fri, 4 Jan 2008 02:03:33 -0400 (AST) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 216D82E191D for ; Fri, 4 Jan 2008 02:03:33 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.1/8.14.1) with ESMTP id m0463RBT010657; Fri, 4 Jan 2008 01:03:27 -0500 (EST) To: James Cloos cc: pgsql-admin@postgresql.org Subject: Re: When does VACUUM FULL not clean out all deleted data? In-reply-to: References: Comments: In-reply-to James Cloos message dated "Fri, 04 Jan 2008 05:42:27 +0000" Date: Fri, 04 Jan 2008 01:03:27 -0500 Message-ID: <10656.1199426607@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200801/51 X-Sequence-Number: 27825 James Cloos writes: > Right after the restore the db took up less than ten percent as much > space as the backup. (For the backup I stopped pg and used rsync on > the /var/lib/postgresql/data directory.) > Why was the db using that extra five plus gigs? Smells like a hadn't-been-vacuuming-often-enough problem. Please note that vacuum-full-once-in-a-long-while is *not* a substitute for frequent-plain-vacuum, because VACUUM FULL isn't good at reducing index space usage --- in fact it usually makes it worse. Your numbers didn't separate table vs index bloat, but I'm suspicious that a lot of your problem was the latter. regards, tom lane