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 wrote a script to backup a postgresql using COPY and then delete those records. I have used pg_relation_size before and after delete.

  1. pg_relation_size
  2. DELETE
  3. VACUUM ANALYZE
  4. pg_relation_size

But both pg_relation_size shows same results. Does this mean VACUUM ANALYZE was not helpful?

share|improve this question
add comment

2 Answers

up vote 5 down vote accepted

VACUUM does not necessarily make unused space available to the filesystem, it merely makes the blocks re-usable for further INSERTs (or UPDATEs).

If I'm not mistaken the only way to also actively reduce the size on the file system would be a VACUUM FULL, but beware that needs an exclusive lock on the table(s).

Do you expect the table to not get any new rows or updates? Because if it will get new rows, then it doesn't really make sense to try to shrink the files phyiscally now if they are going to be increased again later due to new rows arriving.

share|improve this answer
add comment

a_horse_with_no_name responded to original question, let me just add a link - if you want to remove bloat from table, you might find this informative.

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.