pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Mathijs Brands <mathijs@ilse.nl>
To: xuyifeng <jamexu@telekbird.com.cn>
Cc: pgsql-hackers@postgresql.org
Cc: pgsql-sql@postgresql.org
Subject: Re: Re: [HACKERS] why the DB file size does not reduce when 'delete'the data in DB?
Date: Wed, 7 Mar 2001 00:46:01 +0100
Message-ID: <20010307004601.A51050@ilse.nl> (raw)
In-Reply-To: <008601c0a44f$0d657b20$cc01a8c0@xyf>
References: <Pine.BSF.4.33.0103020804370.88613-100000@mobile.hub.org>
	<008601c0a44f$0d657b20$cc01a8c0@xyf>

On Sun, Mar 04, 2001 at 10:01:37AM +0800, xuyifeng allegedly wrote:
> ----- Original Message ----- 
> From: The Hermit Hacker <scrappy@hub.org>
> To: Jaruwan Laongmal <jaruwan@gits.net.th>
> Cc: <pgsql-hackers@postgresql.org>; <pgsql-sql@postgresql.org>
> Sent: Friday, March 02, 2001 8:04 PM
> Subject: Re: [HACKERS] why the DB file size does not reduce when 'delete'the data in DB?
> 
> > On Fri, 2 Mar 2001, Jaruwan Laongmal wrote:
> > 
> > > I had deleted a very large number of records out of my SQL table in order to
> > > decrease the harddisk space.  But after I use command 'ls -l
> > > /usr/local/pgsql/data/base/', it is found that the size of concerning files
> > > do not reduce due to the effect of 'delete' SQL command.  What should I do
> > > if I would like to decrease the harddisk space?
> > 
> > VACUUM
> 
> could anyone remove this nasty bug in 7.2? this is already a big pain and is the reason 
> why am I still using MySQL in my product server. another nasty thing is it does not 
> allow me to reference table in another database.  sigh.

Why would this be a bug? Sure, maybe it's not what you expected, but I hardly think
it qualifies as a bug. For instance, Oracle doesn't release storage (datafiles
specifically) after it has allocated space for them. In fact, I wish I could force
pgsql to allocate storage it might need in the future. It would be great if I could
force pgsql to allocated four datafiles spread across four harddisks, so I would
enjoy a) better database performance and b) rest assured I have the diskspace when
I need it in the future. Call it a poor mans RAID; I think MySQL can perform this
trick.  If pgsql can do this, please let me know

But back to your problem. One way to get the amount of space allocated to shrink is
by recreating the database. Dump it using pg_dump and recreate it using the backup
you just made. This is a fairly simple and quick process. Give it a try on a small
test database first; you don't want to risk loosing your data.

Cheers,

Mathijs
-- 
It's not that perl programmers are idiots, it's that the language
rewards idiotic behavior in a way that no other language or tool has
ever done.
                                                    Erik Naggum



view thread (12+ messages)  latest in thread

Message-ID: <20010307004601.A51050@ilse.nl>
Permalink:  ../20010307004601.A51050@ilse.nl/
Also on:    postgresql.org/message-id/20010307004601.A51050@ilse.nl

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: mathijs@ilse.nl, jamexu@telekbird.com.cn, pgsql-hackers@postgresql.org
  Subject: Re: [HACKERS] why the DB file size does not reduce when 'delete'the data in DB?
  In-Reply-To: <20010307004601.A51050@ilse.nl>
  References: <Pine.BSF.4.33.0103020804370.88613-100000@mobile.hub.org> <008601c0a44f$0d657b20$cc01a8c0@xyf> <20010307004601.A51050@ilse.nl>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox