PostgreSQL reclaiming table space

From: Anthony Bull <antsbull(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: PostgreSQL reclaiming table space
Date: 2012-05-28 10:24:58
Message-ID: CAGK=A1nyE29EEurLZBk63y4EGS6L8f26Sn_gMeeidpPeh8Bcgg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Greetings,

I am having problems reclaiming an excessive amount of disk space used by a
database, running on Windows PostgreSQL v8.3 (unfortunately we are stuck
with this version at the moment).

The database had a 16GB table, that I deleted a lot of data from. After
deleting from this table, I ran a VACUUM FULL on it and it didn't give the
freed space to the operating system - whats more, running "select
pg_database_size('db')" says that the database is still taking up 25GB.

After this I did some reading, and decided to recreate the table - so I
recreated it with a different name, ran a SELECT * to insert into it, and
recreated the indexes on it. This new table takes up 3GB only. After this
I dropped the old table, and renamed the new table to the same name as the
old table. I read on various sites that as soon as you commit a "DROP
TABLE" command, it returns the used disk space to the operating system.
This did not work either - postgres still reports 25GB being used by the
database. If I select the size of all the tables in the database postgres
reports around 9GB or so, yet reports that the database itself is 25GB.

After that, I ran a VACUUM FULL across the entire database, and it returned
about 1GB to the operating system, which I think was from another large
table that got cleaned out (but not recreated).

I am now at a loss, as I want the 16GB from that old table back in the
operating system for other means (disk space is severely limited on this
particular machine) and have tried all suggestions I could find from
googling to no avail. Due to new rules implemented in our data processing,
our database will not grow anywhere near as big as it did previously - so
postgres holding on to all this extra space is a complete waste also.

Note that it is not really feasible for me to uninstall postgres, upgrade
it and rebuild all my databases at this stage as our system has a high
availability requirement.

Is there anything else I can do to get postgres to give me back all that
unused space?

Thanks for any help people can give!

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Jens Wilke 2012-05-28 11:16:37 Re: PostgreSQL reclaiming table space
Previous Message Anthony Bull 2012-05-28 09:11:01 PostgreSQL disk space reclaiming