Обсуждение: why the DB file size does not reduce when 'delete' the data in DB?

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?

I am looking forward to your response.  Thank you very much for any help.
-
Jaruwan

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?

Postgres will only mark them as deleted, but the rows will stay in the
DB.
Do a vacuum on the database and the deleted rows will be eliminated.

Saludos... ;-)

System Administration: It's a dirty job, 
but someone told I had to do it.
-----------------------------------------------------------------
Martín Marqués            email:     martin@math.unl.edu.ar
Santa Fe - Argentina        http://math.unl.edu.ar/~martin/
Administrador de sistemas en math.unl.edu.ar
-----------------------------------------------------------------

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

> 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?

Run "vacuum" from SQL or "vacuumdb" from the command line. Tables will
be reduced in size, though currently indices are not.
                       - Thomas

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?

Run the command VACUUM;

This will do the actual removal of deleted records. DELETE just marks 
them as deleted

> I am looking forward to your response.  Thank you very much for any help.
> -
> Jaruwan

----- 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.

Regards,
XuYifeng

On Sun, 4 Mar 2001, xuyifeng 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.

Its actually not considered a *bug*, but it was a feature that was part of
an older feature that was removed.  Vadim has plans for implementing an
OverWriting Storage Manager, but scheduale of it is uncertain ... could be
for v7.2 ...

----- Original Message ----- 
From: Roberto Mello <rmello@cc.usu.edu>
To: xuyifeng <jamexu@telekbird.com.cn>
Sent: Sunday, March 04, 2001 10:40 AM
Subject: Re: [HACKERS] why the DB file size does not reduce when 'delete'the data in DB?


> On Sun, Mar 04, 2001 at 10:01:37AM +0800, xuyifeng wrote:
> > 
> > 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.
> 
> I don "vacuum analyze" in my Postgres database every night very easily
> using a simple cron job. I create the cron job and forget about it. Really
> it's not that hard don't you think?
> Sure, it's not the desired way, but that's not big enough a reason to
> use PostgreSQL. I could think of a dozen reasons that counter that one and
> would favor PostgreSQL in technical terms. Compared to those, this is
> really really a minor issue.
> As to referring to a table in another DB, I don't know when is that
> going to be implemented.
> 
> -Roberto
> 
> -- 
> +----| http://fslc.usu.edu USU Free Software & GNU/Linux Club|------+
>   Roberto Mello - Computer Science, USU - http://www.brasileiro.net 
>       http://www.sdl.usu.edu - Space Dynamics Lab, Web Developer    
> << Politically Un-Correct Tagline Deleted! >>
> 

I know your situations,  your DB is not updated and inserted lots of records in few minutes,
mine is difference,  I have a real time Stock Trading  system, you know, stock, its price 
is changed every minute or even every second ,  I need update and insert delta change into DB,  
draw their trend graphics,  suppose there are 3000 stocks in market,  there maybe 9000 records 
changed and inserted in one minutes, because PGSQL's storage manager problem( it does not 
reuse deleted record space),  in 4 hours trading period,  my harddisk can be full filled.  because in 
the period, the table indeed gets very large, doing VACCUME is impossible in realtime, it will lock
out other clients too long time,  my point of view is PGSQL is fit for static or small changed database, 
not fit for lots of change in short time.

Regards,
Xu Yifeng

> I know your situations,  your DB is not updated and inserted lots of
records in few minutes,
> mine is difference,  I have a real time Stock Trading  system, you know,
stock, its price
> is changed every minute or even every second ,  I need update and insert
delta change into DB,
> draw their trend graphics,  suppose there are 3000 stocks in market,
there maybe 9000 records
> changed and inserted in one minutes, because PGSQL's storage manager
problem( it does not
> reuse deleted record space),  in 4 hours trading period,  my harddisk can
be full filled.  because in
> the period, the table indeed gets very large, doing VACCUME is impossible
in realtime, it will lock
> out other clients too long time,  my point of view is PGSQL is fit for
static or small changed database,
> not fit for lots of change in short time.

It's admitadly a problem so I don't think you need to convince everyone that
it's not the best way to handle things :-)

I hate to say it, but your options currently are to upgrade your storage
device or change databases... I think I'd fork out some cash for some new
hardware verses buying a commercial database or putting up with the missing
features of MySQL..

All my humble opinion of course, I wish you the best of luck.

-Mitch

Hello Mathijs,

Wednesday, March 07, 2001, 7:46:01 AM, you wrote:

MB> 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.

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

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

MB> Cheers,

MB> Mathijs

do you really know the problem of PGSQL storage manager? it DOES NOT
reuse deleted record space. it also grows database size when you just
update but not insert record. it is a MS ACCESS like storage manager.
it is a functional bug. there is logic bug, performance bug...

-- 
Best regards,
Xu Yifeng

On Wed, 7 Mar 2001, Xu Yifeng wrote:

> do you really know the problem of PGSQL storage manager? it DOES NOT
> reuse deleted record space. it also grows database size when you just
> update but not insert record. it is a MS ACCESS like storage manager.
> it is a functional bug. there is logic bug, performance bug...

Well, as always, we look forward to seeing patches from you to fix this
glaring functional bug :)

> do you really know the problem of PGSQL storage manager? it DOES NOT
> reuse deleted record space. it also grows database size when you just
> update but not insert record. it is a MS ACCESS like storage manager.
> it is a functional bug. there is logic bug, performance bug...

imho a designed-in feature can not be called a bug, even if you disagree
with its intent or implementation. The term "bug" should be reserved for
code which does not behave as designed.

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.

We have continuing discussions on how to evolve the performance and
behavior of PostgreSQL, and you can check the archives on these past
discussions.

Regards.
                    - Thomas

> do you really know the problem of PGSQL storage manager? it DOES NOT
> reuse deleted record space. it also grows database size when you just
> update but not insert record. it is a MS ACCESS like storage manager.
> it is a functional bug. there is logic bug, performance bug...

It's not a bug but a feature invented by Michael Stonebraker. Write
to him why do you think that is a bug:-)
--
Tatsuo Ishii

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

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