Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a table where I am performing only inserts, never deletes/updates. I notice sometimes that autovacuum runs on this table, even though this is the case.

autovacuum: VACUUM ANALYZE public.twitter_shares (to prevent wraparound)  

It is taking a long time, and it is having an impact on the performance of my DB. Is it safe for me to just disable autovacuum for this table? Since I am not performing deletes/updates, I don't understand why autovacuum is even needed, and why postgres decides to run it.

share|improve this question
add comment

migrated from stackoverflow.com Aug 27 '13 at 20:03

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

1 Answer

(to prevent wraparound)

is the key bit. PostgreSQL must do freeze vacuums where transaction IDs are set to XID_FROZEN to prevent transaction ID wrap-around, and as a result massive data corruption.

If you disable these or cancel them all the time your database will eventually go into emergency shutdown.

See the user manual on VACUUM.

You can do a manual VACUUM FREEZE during a quiet period to get this all over and done with in one go if you want.

share|improve this answer
    
When are new transaction IDS created? When I do an insert or update? –  Henley Chiu Aug 27 '13 at 14:13
    
@HenleyChiu Any read/write statement will create a new xid if the transaction doesn't already have one. So will some other operations - I think WITH HOLD cursors do; selecting txid_current() certainly does, etc. –  Craig Ringer Aug 27 '13 at 23:53
    
Can I disable auovacuum transaction wrap-around for this table if I'm no longer doing future inserts/updates (ie it's an archive table) –  Henley Chiu Aug 28 '13 at 1:53
    
@HenleyChiu You can do a manual VACUUM FREEZE, which is what autovacuum is trying to do anyway. Once all tuples are frozen there's no need for autovacuum to do any more work on the table. –  Craig Ringer Aug 28 '13 at 6:27
add comment

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.