0

This question already has an answer here:

Postgres recommends using the autovacuum to remove the dead tuples. But does running the autovacuum end up making other queries to the same table slow? Does it add locks (row or table level) which impact performance? Is there a significant load on CPU or does it consume lot of memory.

This will help us decide if we should turn on autovacuum for one of our tables which has very high amount of writes and deletes or if we should rather run a manual vacuum regularly during off peak hours.

marked as duplicate by Evan Carroll, LowlyDBA, mustaccio, joanolo, RDFozz Jun 19 '17 at 21:59

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

migrated from stackoverflow.com Jun 19 '17 at 14:08

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

1

It's memory usage is constrained by autovacuum_work_mem or maintenance_work_mem. It's IO consumption is constrained by autovacuum_vacuum_cost_delay and friends, and the defaults for these lead to quite low IO demand. The CPU usage is not directly constrained, but is indirectly constrained by the IO consumption throttle, and with the default setting will usually be close to negligible.

Autovacuum does take a lock on the table, but it is a weak lock which does not interfere with normal operations (SELECT, UPDATE, DELETE) but will interfere with things like adding indexes, or truncating the table. Autovacuum will usually cancel itself and surrender the lock when it detects it is blocking something, but it won't do that if it is in "to prevent wraparound" mode.

Autovacuum is on by default. You should not turn it off. Running manual vacuums during off-peak hours is fine, but leave autovacuum on as a back-stop to catch whatever your manual vacuum misses.

Not the answer you're looking for? Browse other questions tagged or ask your own question.