Blocking Queries

Impact

Queries that hold contended locks for a significant period of time may end up preventing other queries from making progress. This can even block simple SELECT queries, and in some cases, it can affect overall system performance or it can cause the system to run out of connections.

Common Causes

• Autovacuum (to prevent wraparound)

  In general, autovacuum won't block other queries. If a query tries to acquire a lock that conflicts with a running autovacuum, autovacuum will be interrupted so that the query can proceed. However, if autovacuum is running to prevent transaction ID wraparound, it will not yield like this and will block other queries trying to acquire conflicting locks.

• Long-running migration

  If some of these queries are DDL that is part of a database migration, they may need to rewrite a table (e.g., changing the type of a column) or scan a whole table (e.g., CREATE INDEX without CONCURRENTLY), which can take much longer in production than in a staging or development environment. If your migration is transactional, consider canceling it and refactor it to avoid table rewrites.

• Other long-running queries

  A long running query could block other queries if it holds locks that they require to make progress. Navigate to the Connection Traces page to check the overview of which query is blocking which queries to understand the impact. It is useful to know what kind of locks the blocking query is holding and what kind of locks the blocked queries are waiting for. You can check the wait event of these locks in the Wait Events page, with any wait events of type Lock. If you have log_lock_waits turned on, you can also see details about locking in the Logs page for these queries.

  You can run the queries like SELECT * FROM pg_locks WHERE pid = '<blocking_pid>'; to obtain more detailed lock information of the blocking query to see which locks the blocking query is holding. You can also run this query with the blocked query like SELECT * FROM pg_locks WHERE pid = '<blocked_pid>' WHERE NOT granted; to see which locks it's waiting for. This information can be used to investigate why this has happened and how to change the query to prevent this from happening again in the future.

Solution

You can cancel these queries with SELECT pg_cancel_backend('<query_pid>');. Note that this only treats the symptom: if the query runs again without any changes, it is likely to acquire the same locks and block other queries again.

If you're canceling an autovacuum to prevent wraparound, note that it does critical work. It will restart and need to be completed eventually, acquiring the same locks. Consider tuning your vacuum settings to be more aggressive to avoid the need for explicit vacuums to prevent wraparound.