1

Have a question on the difference between just allowing autovacuum to take care of vacuum'ing or having to run a full db vacuum in a certain interval (every night?, every week?).

We used to run a full vacuumdb every night before a full backup. It used to take around an hour and a half. Then we stopped and just allowed autovacuum to do it's thing.

Is that a good idea? Should there still be a vacuumdb run every night, week?

I noticed some processes tonight...

"autovacuum: VACUUM public.large_table (to prevent wraparound)"

Is that normal or is that because we stopped running a full vacuumdb every night?

Thanks all for any insight.

migrated from stackoverflow.com Apr 5 '14 at 2:21

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

  • 1
    If autovacuum is running often enough (i.e. low enough cost_delay, enough autovacuum_workers, etc) then it should generally be sufficient. Manual vacuum remains useful in cases where you want to force work to happen at particular times, you want something to be noninterruptible, or you've just done a bulk load. – Craig Ringer Apr 5 '14 at 3:47
3

A manual vacuumdb should only be needed for special circumstances, like after running pg_upgrade or after doing some kind of bulk load or major maintenance. Otherwise, just let autovacuum do its thing.

If your database is mostly idle at night, you can still do vacuumdb and it will concentrate the maintenance into the off hours so that autovacuum is less likely to kick in during the day. But if you do that, you probably still shouldn't turn off autovacuum.

The vacuums to prevent wrap around are normal for any system that processes hundreds of millions of transactions over its lifetime. They are nothing to worry about. It is sometimes nice to know when they are occurring, because anti-wrap around autovacuums will block some kinds of work, such as index builds, whereas regular autovacuums will not.

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.