Re: weird run-times with pg_autovacuum

Postgres Learner wrote:
> Hi all!
> I recently started using pg_autovacuum instead of scheduling vacuum
> analyzes in a system that needs to be taken to production soon.
>
> However, I have noticed something funny that happens while using this.
>
> sometimes, some database operations take an unreasonably long time to finish.
> I have not been able to pin the problem down to any specific cause and
> the problem is also not reproducible( atleast I don't know how to make
> sure it happens again ) but what happens is that some queries take an
> unreasonably long time to finish.
>
> pg_stat_actiivity showed some selects running on a table with ~90k
> rows at one such time of weird behavior(they ran for a long long
> time).
>
> Is it possible that pg_autovacuum is auto vacuuming that same table at
> that time and there is some unnecessary waiting involved because the
> table is locked?

A simple vacuum shouldn't lock tables, although a vacuum full will. It
could be that on a busy system, the vacuum is pushing disk activity to
the limit. You can monitor overall activity with "vmstat 1" from the
command-line in Linux.

One solution might be to vacuum more often. Although a little
counter-intuitive, this means each run does less work.

--
   Richard Huxton
   Archonet Ltd