Re: Autovacuum stuck for hours, blocking queries

On Wed, Feb 15, 2017 at 3:26 PM, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> wrote:
> On Wed, Feb 15, 2017 at 10: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>
>>
>> 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_Monitoring to 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