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

On Wed, Feb 15, 2017 at 3:26 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> 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) 
>
> 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.

Followup: https://www.postgresql.org/docs/9.3/static/sql-createtable.html#SQL-CREATETABLE-STORAGE-PARAMETERS