Re: [HACKERS] database size
At 06:09 PM 1/6/98 -0500, Shiby Thomas wrote:
Hi,
I created a table with two columns of type int, and loaded about 300 K records
in it. So, the total size of the table is approx. that of 600 K integers,
roughly 2.4 MB.
But, the file corresponding to the table in pgsql/data/base directory
has a size of 19 MB. I was wondering if I have done something wrong in
the installation or usage, or is it the normal behavior ?
Just wondering.. did you happen to do an INSERT into the database,
then delete some rows.. say 19megs worth, then re-add... From what I've
seen msql db's will always be at least the size of the largest you've ever
had the database before. It will over time, overrite existing deleted
records, but it keeps the data still in there, just sets a delete flag.
If you really need to cut the size down, I've had to delete the database
completely, then create another table from scratch. Not sure if there
is a 'purge' type function available, but you have to be careful that
nobody is accessing the db at that time, since it's very sensitive at
that time.
-Tony
Also, I was trying to execute the query:
select item as item, count(*) as cnt into table C_temp
from temp group by item;
Here, temp is the name of the table which contains the data and item is an
integer attribute. While doing the sort for the group by, the size of one of
the temporary pg_psort relation grows to about 314 MB. The size of the temp
table is as mentioned above. If someone tried similar queries, could you
please tell me if this is normal.
The above query did not finish even after 2 hours. I am executing it on a
Sun Sparc 5 running Sun OS 5.5.
Thanks
--shiby
----------------------------------------------------------------------
<bold>Tony Rios Compass Communications,
Inc.
</bold> 2001 Sixth Ave, Suite
3205
Seattle, WA 98121
<bold>VOICE</bold>: (206) 777-9999 <bold>FAX</bold>:
(206) 777-3077
----------------------------------------------------------------------
Import Notes
Reply to msg id not found: 199801062310.SAA28681@cise.ufl.edu
On Tue, 6 Jan 1998, Tony Rios wrote:
At 06:09 PM 1/6/98 -0500, Shiby Thomas wrote:
Hi,
I created a table with two columns of type int, and loaded about 300 K records
in it. So, the total size of the table is approx. that of 600 K integers,
roughly 2.4 MB.
But, the file corresponding to the table in pgsql/data/base directory
has a size of 19 MB. I was wondering if I have done something wrong in
the installation or usage, or is it the normal behavior ?
Just wondering.. did you happen to do an INSERT into the database,
then delete some rows.. say 19megs worth, then re-add... From what I've
seen msql db's will always be at least the size of the largest you've ever
had the database before. It will over time, overrite existing deleted
records, but it keeps the data still in there, just sets a delete flag.
If you really need to cut the size down, I've had to delete the database
completely, then create another table from scratch. Not sure if there
is a 'purge' type function available, but you have to be careful that
nobody is accessing the db at that time, since it's very sensitive at
that time.
vacuum will clean out the deleted records and truncate the table...has
been so since v6.1, I believe...
Marc G. Fournier
Systems Administrator @ hub.org
primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org
Just wondering.. did you happen to do an INSERT into the database,
then delete some rows.. say 19megs worth, then re-add... From what I've
seen msql db's will always be at least the size of the largest you've ever
had the database before. It will over time, overrite existing deleted
records, but it keeps the data still in there, just sets a delete flag.
If you really need to cut the size down, I've had to delete the database
completely, then create another table from scratch. Not sure if there
is a 'purge' type function available, but you have to be careful that
nobody is accessing the db at that time, since it's very sensitive at
that time.
Thanks to Vadim, vacuum shrinks the size to the exact amount needed to
store the data. Also, the table is locked while vacuuming, so no one
can accidentally access it.
--
Bruce Momjian
maillist@candle.pha.pa.us