Thread: Re: [HACKERS] why the DB file size does not reduce when 'delete' the data in DB?
Re: [HACKERS] why the DB file size does not reduce when 'delete' the data in DB?
From
The Hermit Hacker
Date:
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
Re: [HACKERS] why the DB file size does not reduce when 'delete'the data in DB?
From
"xuyifeng"
Date:
----- 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
Re: Re: [HACKERS] why the DB file size does not reduce when 'delete'the data in DB?
From
Mathijs Brands
Date:
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
Re: Re[2]: Re: [HACKERS] why the DB file size does not reduce when 'delete'the data in DB?
From
Tatsuo Ishii
Date:
> 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
Re: Re: why the DB file size does not reduce when 'delete'the data in DB?
From
Thomas Lockhart
Date:
> 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
Re[2]: Re: [HACKERS] why the DB file size does not reduce when 'delete'the data in DB?
From
Xu Yifeng
Date:
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