Re: SQL Perfomance during autovacuum

On Wed, Dec 19, 2018 at 1:04 AM anand086 <anand086(at)gmail(dot)com> wrote:

>
> The Execution time for the above sql is 17841.467 ms during normal
> operations but when autovacuum runs on table test_table, the same sql took
> 1628495.850 ms (from the postgres log).
>
> We have noticed this increase in execution times for the sqls only when
> autovacuum runs and it runs with prevent wraparound mode.

Some competition for resource is to be expected with autovacuum, but making
a one-hundred fold difference in run time is rather extreme. I'd suggest
that what you have is a locking issue. Something is trying to take a brief
Access Exclusive lock on the table. It blocks on the lock held by the
autovacuum, and then the Access Share lock needed for your query blocks
behind that.

Normally an autovacuum will yield the lock when it notices it is blocking
something else, but will not do so for wraparound.

If you have log_lock_waits turned on, you should see some evidence in the
log file if this is the case.

Cheers,

Jeff