Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

I want know which one is good to follow whether auto vacuum or vacuum manually. Right now we are following manually in cron jobs, but sometimes it gets struck to vaccum on particular tables. so we are thinking about the auto vacuum. does it give good performance to production server? please suggest. Lot of thanks in advance.

share|improve this question

2 Answers 2

Autovacuum should be enough for most of the cases. If the autovacuum processes get too intrusive, or not fast enough - it's trivial to customize the settings so that it will be fine.

The only cases where manual vacuum should be used is if/when you know that what you did should be "fixed" immediately. For example - if you're doing massive updates of a table, running vaccuum between batches will make sure that the bloat in table will be limited.

share|improve this answer

Running an automatic lazy VACUUM (or simple VACUUM), ie VACUUM without the FULL parameter is something that is meant to be enabled in full production with no impact.

It is heavily recommended to run VACUUM FULL during scheduled maintenance windows.

share|improve this answer
1  
It is very not recommended to run vacuum full at all. That's one of the reasons it was suggested to be removed, but was finally replaces by totally different mechanics in 9.0. –  user13185 Apr 28 '11 at 8:48
    
Funny that Eisentraut et al (Postgres core team members) tell you to run VACUUM FULL during maintenance windows in the recently released 2nd edition of the excellent PostgreSQL Administration. –  pfo Apr 28 '11 at 8:52
1  
Don't have the book, so can't verify the claim, especially - since there is probably some context. Generally - if you "want" to run vacuum full - it has to be done in maintenance window, because it locks the table. So perhaps this is what they meant. Vacuum full is extremely slow, and it bloats indexes. Aside from really bad edge cases there is no reason at all to ever use it. –  user13185 Apr 28 '11 at 8:54
    
I have it right in front of me and the explanation for regularly running VACUUM is that it's a good idea if you don't have dedicated VACUUM FULL maintenance windows. –  pfo Apr 28 '11 at 9:00
1  
Again - I don't have the book, so I can't check your claim. On the other hand - I know what Josh Berkus wrote (it.toolbox.com/blogs/database-soup/…), and I know that vacuum full was rewritten in 9.0 (depesz.com/index.php/2010/01/11/…) - which seems to support the idea that vacuum full in 8.4 shouldn't be used. –  user13185 Apr 28 '11 at 9:06

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.