Received: from mail.postgresql.org (webmail.postgresql.org [216.126.85.28]) by mail.postgresql.org (8.11.1/8.11.1) with SMTP id f2GIjex39144 for ; Fri, 16 Mar 2001 13:45:40 -0500 (EST) (envelope-from pgsql-hackers-owner@postgresql.org) Received: from mail2.vlaanderen.net (makki.wait-a-bit.com [195.162.201.13]) by mail.postgresql.org (8.11.1/8.11.1) with SMTP id f2G1Rvx96721 for ; Thu, 15 Mar 2001 20:27:58 -0500 (EST) (envelope-from yves@mail2.vlaanderen.net) Received: (qmail 3893 invoked by uid 1000); 16 Mar 2001 01:28:46 -0000 From: yves@mail2.vlaanderen.net Date: Fri, 16 Mar 2001 02:28:46 +0100 To: lockhart@fourpalms.org Cc: pgsql-hackers@postgresql.org Subject: Re: Re: [SQL] Re: why the DB file size does not reduce when 'delete'the data in DB? Message-ID: <20010316022846.A3804@asua2.vlaanderen.net> References: <008601c0a44f$0d657b20$cc01a8c0@xyf> <20010307004601.A51050@ilse.nl> <1828207391.20010307111045@telekbird.com.cn> <3AA59C17.E2FDE23C@alumni.caltech.edu> <20010316004507.A3288@asua2.vlaanderen.net> <3AB157E0.9FEBFE69@alumni.caltech.edu> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline User-Agent: Mutt/1.2.5i In-Reply-To: <3AB157E0.9FEBFE69@alumni.caltech.edu>; from lockhart@alumni.caltech.edu on Fri, Mar 16, 2001 at 12:01:36AM +0000 X-Archive-Number: 200103/699 X-Sequence-Number: 6026 On Fri, Mar 16, 2001 at 12:01:36AM +0000, Thomas Lockhart wrote: > > > You are not quite factually correct above, even given your definition of > > > "bug". PostgreSQL does reuse deleted record space, but requires an > > > explicit maintenance step to do this. > > Could you tell us what that maintenance step is? dumping the db and restoring into a fresh one ? :/ > > :) No, "VACUUM" is your friend for this. Look in the reference manual > for details. > > - Thomas I'm having this problem: I have a database that is 3 megabyte in size (measured using pg_dump). When i go to the corresponding data directory (eg du -h data/base/mydbase), it seems the real disk usage is 135 megabyte! Doing a VACUUM doesn't really change the disk usage. Also query & updating speed increases when i dump all data and restore it into a fresh new database. I'm running postgresql-7.0.2-6 on a Debian potato. -Yves