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

----------------------------------------------------------------------