4

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?

5

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.

5

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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