Re: [GENERAL] Autovacuum stuck for hours, blocking queries

On Wed, Feb 15, 2017 at 10:30 AM, Tim Bellis <Tim.Bellis@metaswitch.com> wrote:
> I have a postgres 9.3.4 database table which (intermittently but reliably) gets into a state where queries get
blockedindefinitely (at least for many hours) behind an automatic vacuum. I was under the impression that vacuum should
nevertake 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>
>
> The query being blocked is:
> ALTER TABLE <table_name> ALTER COLUMN <column_name> DROP DEFAULT
> (But I have seen this previously with other queries being blocked. I used the SQL in
https://wiki.postgresql.org/wiki/Lock_Monitoringto determine which queries were blocked) 

Yup, there's a priority inversion in DDL, DML and maintenance
(vacuum). Vacuum runs slow in the background. Normal
update/delete/insert work fine because of the type of lock vacuum has.

Then a ddl gets in line. It has to wait on the vacuum, and the vacuum,
set to run super slow. And everybody waits. On vacuum.

Basically it's bad practice to alter tables that are big and being
worked on, because one way or another you're going to pay a price.

I've used partitions for logging and auditing that autocreate and drop
and vacuum, but they never get ddl done on them when they're getting
updated and vice versa.

There are also ways of making the table less likely / not likely /
will not get vacuum automatically. If you're willing to schedule ddl
and vacuum on your own you can then mix the two in relative safety.