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 am trying to find answers for my below few queries which will help me in fine tuning my postgres DB. I did some googling but was not able to find answer.

1) Out of vacuum or Auto vacuum which is more resource intensive operation?
2) Is there a way to run Auto vacuum only when there is unusually heavy update activity?
3) How can I find out based on setting when my auto vacuum will start next time, based on threshold.

share|improve this question

migrated from stackoverflow.com Aug 13 '13 at 8:44

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

add comment

2 Answers

up vote 3 down vote accepted

1) If you don't count your own time as a resource, then you should always be able to hand-craft a vacuum schedule which uses fewer total resources than autovacuum does. If you do count your own time, this is almost surely not worthwhile.

2) Other than manually or algorithmically turning it on or off, no. Nor would it make sense to do that. A database that sees only a very steady predictable load with no unusual spikes still needs vacuuming. However, if you do spend a lot of time coming up with a highly optimized hand-crafted vacuum schedule, then autovacuum will never have any vacuuming to do except under unusual activity not anticipated in your custom schedule; so in this sense what you want is automatically the case.

3) It isn't clear whether you mean when an autovacuum worker will start up in a given database, or when any given table will start to be vacuumed. The first is determined by autovacuum_naptime, unless autovacuum_max_workers becomes the limiting factor. The second is determined by when the table crosses the threshold, with a granularity of autovacuum_naptime, except when autovacuum_max_workers becomes the limiting factor. How accurately you can predict this depends on how steady the activity on that table is, and whether the vacuum tasks are generally overloaded or are mostly idle.

share|improve this answer
    
Thanks JJanes. For 3rd point what i m looking is that say my Auto vacuum last ran when i had 200 thousand dead tuple and now based on some settings like you and sambler in next comment suggested how can i conculded when i will have next auto vacuum running (purely based on dead tuples count) –  prvnsaini Aug 15 '13 at 21:28
add comment

1) they perform the same task so use the same resources, as autovacuum is run when as it is needed it can use less resources at any one time, whereas if you disable it and manually run vacuum once a week then you effectively accumulated the work and do it all at one time.

2 + 3) 23.1.6 of the manual explains autovacuum and how the relevant settings work to determine when it does it's work.

share|improve this answer
    
Thanks sambler, i got this formula from link you shared vacuum threshold = vacuum base threshold + vacuum scale factor * number of tuples but i m confused against what it will compare this calculated value to start Auto vacuum process. –  prvnsaini Aug 15 '13 at 21:29
    
@prvnsaini - From that page -- if the number of tuples obsoleted since the last VACUUM exceeds the "vacuum threshold", the table is vacuumed. –  sambler Aug 16 '13 at 8:40
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.