space not reclaimed after repeated full vacuums

From: Tom O'Brien <Tom(dot)OBrien(at)entrust(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: space not reclaimed after repeated full vacuums
Date: 2006-07-04 19:47:44
Message-ID: 7A3E1242FA9989439AD1F9B2D71C287F0B6B6C7C@sottmxs05.entrust.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Hi all:
I'm running Pgsql 7.4.6 on Red Hat Enterprise Linux 3.0 (UR4 and UR6).

My question/problem is around how much disk space PostgreSQL uses when
tables are grown (by a restore) and then shrunk down again (delete + vacuum
--full).

When I initialize my database and load my schema df tells me that 62MB of
space is used in my DB data partition (with the database running).

I then restore a backup to the new server with pg_restore, I get a final
data size of 49500 MB. This is all fine/expected.

I then want to reduce the space taken by the data down to say 10GB of data,
so I wrote a simple minded script that does the following (on a test
system):

While df returns more than 10GB of data in DB data partition
DELETE 33% of rows from all non-static tables in the schema
/usr/local/pgsql/bin/vacuumdb --analyze --all --full
End while.

I left this running over the weekend, and was surprised to find the script
still running in it's 25th iteration this morning, with basically no rows
left in any of the non-static tables.

The interesting thing was that the data partition still had almost 26GB of
space used, no matter how much data was actually in the database.

I'm probably missing something rather fundimental here. Anything obvious
jump out at anyone? Restarting the DB doesn't change anything.

Du output shows:
[root(at)customer3 root]# du -h /var/pgsql/entu
4.5M /var/pgsql/entu/base/1
4.4M /var/pgsql/entu/base/17141
4.0K /var/pgsql/entu/base/17142/pgsql_tmp
26G /var/pgsql/entu/base/17142
26G /var/pgsql/entu/base
140K /var/pgsql/entu/global
129M /var/pgsql/entu/pg_xlog
20K /var/pgsql/entu/pg_clog
26G /var/pgsql/entu

Where /var/pgsql/entu is the root of our database data directory.

Thanks
Tom.

Browse pgsql-admin by date

  From Date Subject
Next Message Tom O'Brien 2006-07-04 20:49:14 Re: space not reclaimed after repeated full vacuums
Previous Message Rodrigo De Leon 2006-07-04 19:34:26 Re: Wrap (obfuscate) code