Robert Haas wrote: > I agree that you might not like that. But you might not like having > the table vacuumed slower than the configured rate, either. My > impression is that the time between vacuums isn't really all that > negotiable for some people. I had one customer who had horrible bloat > issues on a table that was vacuumed every minute; when we changed the > configuration so that it was vacuumed every 15 seconds, those problems > went away.
Wow, that's extreme. For that case you can set autovacuum_vacuum_cost_limit to 0, which disables the whole thing and lets vacuum run at full speed -- no throttling at all. Would that satisfy the concern? > Now that is obviously more a matter for autovacuum_naptime > than this option, but the point seems general to me: if you need the > table vacuumed every N seconds, minutes, or hours, and it only gets > vacuumed every 2N or 3N or 5N seconds, minutes, or hours because there > are other autovacuum workers running, the table is going to bloat. There might be another problem here which is that if you have all your workers busy because they are vacuuming large tables that don't have churn high enough to warrant disrupting the whole environment (thus low cost_limit), then the table will bloat no matter what you set its cost limit to. So there's not only a matter of a low enough naptime (which is a bad thing for the rest of the system, also) but also one of something similar to priority inversion; should you speed up the vacuuming of those large tables so that one worker is freed soon enough to get to the high-churn table? Was the solution for that customer to set an external tool running vacuum on that table? -- Álvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
