Hi all,

I had a database which uses to hold some 50 Mill records and disk
space used was 103 GB. I deleted around 34 Mill records but still the
disk size is same. Can some on please shed some light on this.

Thank in advance for all the help.

Dhawal Choksi

am Fri, dem 08.06.2007, um 1:22:14 -0700 mailte choksi folgendes:
> Hi all,
>
> I had a database which uses to hold some 50 Mill records and disk
> space used was 103 GB. I deleted around 34 Mill records but still the
> disk size is same. Can some on please shed some light on this.

DELETE only mark rows as deleted, if you need the space you need a
VACUUM FULL.

Read more: http://www.postgresql.org/docs/curre...ql-vacuum.html


Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

choksi writes:

> I had a database which uses to hold some 50 Mill records and disk
> space used was 103 GB. I deleted around 34 Mill records but still the
> disk size is same. Can some on please shed some light on this.

When records are deleted they are only marked in the database.
When you run vacuum in the database that space will be marked so new data
can use the space.

To lower the space used you need to run "vacuum full".
That however can take a while and I think it will lock the database for
some operations.

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

Hello group,

Moreover a reindex (REINDEX <name of your database> while in pgsql) followed
by an ANALYZE will claim more space.

Regards
J6M
----- Original Message -----
From: "Francisco Reyes" <lists@stringsutils.com>
To: "choksi" <choksi.dhaval@gmail.com>
Cc: <pgsql-performance@postgresql.org>
Sent: Friday, June 15, 2007 7:15 PM
Subject: Re: [PERFORM] Database size


> choksi writes:
>
>> I had a database which uses to hold some 50 Mill records and disk
>> space used was 103 GB. I deleted around 34 Mill records but still the
>> disk size is same. Can some on please shed some light on this.
>
> When records are deleted they are only marked in the database.
> When you run vacuum in the database that space will be marked so new data
> can use the space.
>
> To lower the space used you need to run "vacuum full".
> That however can take a while and I think it will lock the database for
> some operations.


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match