0

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.

sathish
  • 1
  • 1
  • 1

2 Answers2

1

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.

0

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.

pfo
  • 5,630
  • 23
  • 36
  • 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. –  Apr 28 '11 at 08: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 08: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. –  Apr 28 '11 at 08: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 09: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 (http://it.toolbox.com/blogs/database-soup/getting-rid-of-vacuum-full-feedback-needed-33959), and I know that vacuum full was rewritten in 9.0 (http://www.depesz.com/index.php/2010/01/11/waiting-for-8-5-vacuum-full-change/) - which seems to support the idea that vacuum full in 8.4 shouldn't be used. –  Apr 28 '11 at 09:06
  • You are right, for 9 it's way faster and works somewhat differently. – pfo Apr 28 '11 at 09:09
  • Vacuum full would cause index bloat in pre 9.0 versions. Starting with 9.0 this issue has been resolved so VACUUM FULL can be used for maintenance. Pre 9.0 it could be used but it was wise to rebuild the indexes afterwards. Or even drop them first do a full vacuum (runs faster without indexes) and recreate them. – Eelke Apr 30 '11 at 06:07