Thread: PostgreSQL reclaiming table space

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! 

On Montag, 28. Mai 2012, Anthony Bull wrote:

Hi,

>This did not
> work either - postgres still reports 25GB being used by the
> database.

Did you run analyze?
Did the disk usage reported by the OS shrink?

Regards, Jens

I did not run analyze, only vacuum full - the disk usage reported by the OS has stayed the same also - the data folder under postgres is still at over 25GB.  Postgres itself also still reports db size at 25GB, but when I ask postgres for a table breakdown by size, it reports only about 9GB of tables in the db.

About 3 hours after the Vacuum full completed, the disk space got returned to the OS - now Windows is reporting it has all that disk back.  Must have been waiting for something?  Anyway, great news!

On 2012-05-28, Anthony Bull <antsbull@gmail.com> wrote:

> 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).

try a REINDEX



--
⚂⚃ 100% natural

Anthony Bull <antsbull@gmail.com> writes:
> About 3 hours after the Vacuum full completed, the disk space got returned
> to the OS - now Windows is reporting it has all that disk back.  Must have
> been waiting for something?  Anyway, great news!

Probably means that some session was holding on to an open-file pointer
to the old copy of the table.  It's difficult to ensure that such
pointers are released quickly without causing performance degradation
for normal cases.  (But having said that, I think we've fixed some bugs
in which pointers were held open unnecessarily.  You didn't say exactly
how old a PG version you were using ...)

            regards, tom lane