Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm running Postgres 8.3.3. I have a postgres database on a Linux filesystem that is quite full. I've run VACUUM and VACUUM FULL, yet none of the space has been returned to the OS. When I observe the size of the table in postgres it shows that the table is using considerably less space (down to 80MB form ~800MB).

Is there something else that should be run?

share|improve this question
add comment

2 Answers 2

up vote 5 down vote accepted

First you should at upgrade to the latest version of the 8.3 branch (which is 8.3.17). There were several fixes to VACUUM and autovacuum after the 8.3.3 (although I don't think any of those would fix your problem)

I think there are situations where even VACUUM FULL will no free all space possible. You could try to run CLUSTER on the table which does a better job of rebuilding it than VACUUM FULL (especially with that old version)

The drawback of CLUSTER is that it requires more intermediate space (because rebuilding isn't done "in-place"), so if your filesystem is really full, than it might not be possible.

share|improve this answer
add comment

PostgreSQL returns free space to the operating system for a table when a) the free space is at the very end of the table, and b) it can obtain an exclusive lock on the table. VACUUM FULL tries to force both of these things to happen, but there's probably a dozen bugs in this area fixed by later 8.3 versions that you might be hitting on 8.3.3.

Note that disk space is taken up by both tables and indexes here, and VACUUM FULL is known to increase index size while it tries to shrink table size. Use a query like the ones at Disk Usage to see where the distribution of space is at. If it's actually indexes that are using the space up, no amount of VACUUM will fix things; try REINDEX on them instead. That will need some more space to hold the rebuilt version. If really stuck for space, you might have to drop an index temporary and then recreate it instead. CLUSTER will completely rebuild the whole table and toss out all of the old cruft, but it needs to be able to hold a whole new copy of the table and all indexes to do it.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.