Re: Autovacuum stuck for hours, blocking queries

On Wed, Feb 15, 2017 at 9:30 AM, Tim Bellis <Tim(dot)Bellis(at)metaswitch(dot)com>
wrote:

> I have a postgres 9.3.4 database table which (intermittently but reliably)
> gets into a state where queries get blocked indefinitely (at least for many
> hours) behind an automatic vacuum. I was under the impression that vacuum
> should never take any blocking locks for any significant period of time,
> and so would like help resolving the issue.
>
> The process blocking the query is:
> postgres 21985 11304 98 Feb13 ? 1-14:20:52 postgres: autovacuum
> worker process <db_name>
> which is running the query
> autovacuum: VACUUM public.<table_name>
>

Are you sure it doesn't really say:

autovacuum: VACUUM public.<table_name> (to prevent wraparound)

If it doesn't include the "to prevent wraparound", then it should sacrifice
itself as soon as it realizes it is blocking something else. If it is not
doing that, something is wrong.

If it does say "(to prevent wraparound)", then see all the other comments
on this thread.

> Notes:
> - This database table is used for about 6 million row writes per day,
> all of which are then deleted at the end of the day.
>

How many transactions do those 6 million writes comprise?

>
> - Our application kicks off a manual vacuum against this table each
> night which doesn't hit this problem, as far as we're aware.
>

If it were having problems, would you be aware of it? Do you see in the
log files the completion of the vacuum? Or look in pg_stat_user_tables to
see when last_vacuum was. If it runs every night and succeeds, it is hard
to see why wraparound would ever kick in. Unless you are hitting
150,000,000 transactions in a day.

Cheers,

Jeff