Re: Monitoring autovacuum induced contention

On Wed, 2023-12-06 at 13:35 -0800, Joseph Hammerman wrote:
> We are working on tuning autovacuum correctly. While the biggest problem we have
> and most other OLTP deployments I have seen have is autovacuum resource starvation,
> I'd like to be able to reliably determine when we have turned autovacuum capacity
> dials too far.
>
> Is there any way to track autovacuum induced query waits?

Autovacuum won't directly block you, so you will never see a process waiting for
autovacuum. The exception to this rule is that statements that take high locks,
like CREATE INDEX or TRUNCATE, may be seen waiting for autovacuum to complete
occasionally. But that is probably not your problem.

Autovacuum will compete for system resources with your normal workload.
So you should monitor operating system resources like CPU or disk I/O, and if
any of these is at capacity while autovacuum is processing a table, you will
feel the impact.

Yours,
Laurenz Albe