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.
Best Answer
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.