Tue, 06 Jan 1998 15:53:35 +0000
Tony Rios <tonester@ccom.net>
Re: [HACKERS] database size
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 ?

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



Tue, 06 Jan 1998 18:09:52 -0500
Shiby Thomas <sthomas@cise.ufl.edu>
database size
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 ?

This is OK. First thing - int is not 2 bytes long, it's 4 bytes long.
Use int2 if you want so. Second - you have to add up other per-record
stuff like oids and other internal attributes. 

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

It ain't good. Seems like the psort is very hungry. 

Mike

-- 
WWW: http://www.lodz.pdi.net/~mimo  tel: Int. Acc. Code + 48 42 148340
add: Michal Mosiewicz  *  Bugaj 66 m.54 *  95-200 Pabianice  *  POLAND

Tue, 06 Jan 1998 23:37:43 +0000
"Micha��� Mosiewicz" <mimo@lodz.pdi.net>
Re: [HACKERS] database size
On Tue, 6 Jan 1998, 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 ?
> 
> 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.

	What version of PostgreSQL are you running? *raised eyebrow*

Marc G. Fournier                                
Systems Administrator @ hub.org 
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org 


Tue, 6 Jan 1998 19:42:18 -0400 (AST)
The Hermit Hacker <scrappy@hub.org>
Re: [HACKERS] database size
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 


Tue, 6 Jan 1998 20:18:19 -0400 (AST)
The Hermit Hacker <scrappy@hub.org>
Re: [HACKERS] database size

=> 	What version of PostgreSQL are you running? *raised eyebrow*
6.2.1. I haven't yet applied the patches(put in the PostgreSQL web page) 
though.

--shiby




Tue, 06 Jan 1998 20:10:39 -0500
Shiby Thomas <sthomas@cise.ufl.edu>
Re: [HACKERS] database size 
> 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

Tue, 6 Jan 1998 20:16:34 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] database size