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