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?

  • Answer updated with a bit more info. May 13 '13 at 1:37

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.

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.