If I see something like:
db=> SELECT datname,pid,state, (now() - query_start)::interval, query FROM pg_stat_activity where state != 'idle' and query like 'autovacuum%';
datname | pid | state | interval | query
---------+-------+--------+-----------------+-----------------------------------------------------
theseus | 21936 | active | 00:00:00.940774 | autovacuum: VACUUM ANALYZE pg_catalog.pg_constraint
theseus | 16775 | active | 00:33:57.896069 | autovacuum: VACUUM pg_toast.pg_toast_1234
How should I interpret xact_start vis a vis the vacuum_cost_limit and autovacuum_vacuum_cost_delay? In other words is xact_start the time at which the thread woke up again to do another chunk of vacuum_cost_limit work, or does it represent many chunks?
And a follow-up: my vacuum_cost_limit setting is the default of 200 (very conservative); if we're seeing a very long "transaction" interval and that interval should be measuring a very small amount of work, what could explain the long runtime? It's my understanding that autovacuum doesn't block on other activity.
Best Answer
The xact_start field should be before or equal to query_start, covering the entire duration of the vacuuming of the table.
Your vacuum is slow because your setting of
vacuum_cost_limitis very conservative. It is throttling itself, not blocking on other things.