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 have postgres9.1

I try to run full vacuum on a specific table , but it run for more than 5 hours. I stop all process against this table and i cannot have the process down any longer.

Is there a way to run vacuum in background? Is there a way to monitor vacuum?

share|improve this question

migrated from stackoverflow.com May 13 '13 at 10:17

This question came from our site for professional and enthusiast programmers.

    
Answer updated with a bit more info. –  Craig Ringer May 13 '13 at 1:37

1 Answer 1

up vote 1 down vote accepted

VACUUM FULL takes an ACCESS EXCLUSIVE lock on the table, then rewrites the whole table. This will take a long time for a large table, especially since it must also rebuild all indexes. You haven't said anything about the table size or the server's I/O subsystem so it's hard to say if 5 hours is unexpected or not.

You shouldn't generally need to use VACUUM FULL. Just use ordinary VACUUM - and turn autovacuum up so it works harder if you need to do a manual VACUUM more than very occasionally. It is safe to cancel the VACUUM FULL; doing so will undo all it's work, though.

You generally only need to run VACUUM FULL if you're trying to recover from severe table bloat caused by a long-running <IDLE> in transaction connection, an abaondoned prepared transaction, insufficiently aggressive autovacuum, etc. If you can't afford the downtime required for this you can look into pg_reorg as an online, reduced-locking alternative.

share|improve this answer

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.