On Wed, Feb 15, 2017 at 10:30 AM, Tim Bellis <Tim.Bellis@xxxxxxxxxxxxxx> 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) 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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
