Thread: Database size
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/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
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.
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.