X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 04B2311F65A1 for ; Wed, 26 Apr 2006 19:47:47 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 43581-06 for ; Wed, 26 Apr 2006 19:47:34 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from noel.decibel.org (noel.decibel.org [67.100.216.10]) by postgresql.org (Postfix) with ESMTP id 2A46711F645C for ; Wed, 26 Apr 2006 19:47:32 -0300 (ADT) Received: by noel.decibel.org (Postfix, from userid 1001) id 41C9756423; Wed, 26 Apr 2006 17:47:33 -0500 (CDT) Received: (hashcash-sendmail, from uid 1001); Wed, 26 Apr 2006 17:47:32 -0500 Date: Wed, 26 Apr 2006 17:47:32 -0500 From: "Jim C. Nasby" To: Jason Minion Cc: "mcelroy, tim" , pgsql-admin@postgresql.org Subject: Re: dbsize & pg_dump Message-ID: <20060426224732.GF97354@pervasive.com> References: <261CF9EEB14F5442894AB6DDA93AA6F4283AEB@mail.siglercompanies.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <261CF9EEB14F5442894AB6DDA93AA6F4283AEB@mail.siglercompanies.com> X-Operating-System: FreeBSD 6.0-RELEASE-p4 amd64 X-Distributed: Join the Effort! http://www.distributed.net User-Agent: Mutt/1.5.11 X-Hashcash: 1:20:060426:jason.minion@sigler.com::Y/StiDEvLjhNeAII:00000000000000 0000000000000000000000000KKn X-Hashcash: 1:20:060426:tim.mcelroy@bostonstock.com::gPWNcMJq9Ly6RP2v:0000000000 0000000000000000000000008BT3 X-Hashcash: 1:20:060426:pgsql-admin@postgresql.org::ZxWaYtLXIJlnxEsI:00000000000 0000000000000000000000004gSk X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200604/289 X-Sequence-Number: 21458 On Tue, Apr 25, 2006 at 04:47:53PM -0500, Jason Minion wrote: > Usually a dump is significantly smaller than a live database due to > space taken up by indexes and discarded tuples from MVCC. If it's > significantly smaller you may also want to take a look at your vacuuming > procedure. Between excluding the database overhead (mostly tuple headers), excluding indexes, and compression, getting a 10x reduction in database size isn't unexpected. Using pg_dumpall and bzip2 the databases on http://stats.distributed.net go from 41G down to 2G. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461