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.
Will analyze help? I was under the impression that was more for statistics
gathering and index optimising?
On Mon, May 28, 2012 at 11:16 PM, Jens Wilke <jens@wilke.org> wrote:
Show quoted text
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
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 Mon, May 28, 2012 at 11:23 PM, Anthony Bull <antsbull@gmail.com> wrote:
Show quoted text
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.Will analyze help? I was under the impression that was more for
statistics gathering and index optimising?On Mon, May 28, 2012 at 11:16 PM, Jens Wilke <jens@wilke.org> wrote:
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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