| Lists: | pgsql-performance |
|---|
| From: | choksi <choksi(dot)dhaval(at)gmail(dot)com> |
|---|---|
| To: | pgsql-performance(at)postgresql(dot)org |
| Subject: | Database size |
| Date: | 2007-06-08 08:22:14 |
| Message-ID: | 1181290934.160823.161630@w5g2000hsg.googlegroups.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-performance |
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
| From: | "A(dot) Kretschmer" <andreas(dot)kretschmer(at)schollglas(dot)com> |
|---|---|
| To: | pgsql-performance(at)postgresql(dot)org |
| Subject: | Re: Database size |
| Date: | 2007-06-15 04:58:06 |
| Message-ID: | 20070615045806.GA14492@a-kretschmer.de |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-performance |
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/current/static/sql-vacuum.html
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
| From: | Francisco Reyes <lists(at)stringsutils(dot)com> |
|---|---|
| To: | choksi <choksi(dot)dhaval(at)gmail(dot)com> |
| Cc: | pgsql-performance(at)postgresql(dot)org |
| Subject: | Re: Database size |
| Date: | 2007-06-15 17:15:37 |
| Message-ID: | cone.1181927737.662704.53974.5001@35st.simplicato.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-performance |
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.
| From: | "J6M" <j6m(at)adm(dot)estp(dot)fr> |
|---|---|
| To: | "Francisco Reyes" <lists(at)stringsutils(dot)com>, "choksi" <choksi(dot)dhaval(at)gmail(dot)com> |
| Cc: | <pgsql-performance(at)postgresql(dot)org> |
| Subject: | Re: Database size |
| Date: | 2007-06-18 08:39:15 |
| Message-ID: | 075101c7b188$23e46df0$1500020a@eden.fr |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-performance |
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(at)stringsutils(dot)com>
To: "choksi" <choksi(dot)dhaval(at)gmail(dot)com>
Cc: <pgsql-performance(at)postgresql(dot)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.