How to Investigate Postgres Query Lock

Problem

There is some long running query that make “DROP INDEX” command getting locked. And all subsequent commands getting locked also.

Investigation

1. Let check what are the running query by running below command

SELECT * FROM pg_stat_activity WHERE state != 'idle';

Postgres will show list of active queries

2. Take column “wait_event_type” into consideration. If there is “Lock” value. It mean, that query haven’t get executed yet. Waiting for some other running query to finish

3. Check example in below picture

Explanation:

• The yellow query is long running query
• And red query is not executed yet, because it getting locked by the YELLOW query
• All the subsequent queries getting locked from the RED query

Solution

1. Make sure it’s safe to kill the yellow query
2. Run below command to kill the yellow query, replace PID that show in last query

SELECT pg_terminate_backend(**PID**);

3. Run list active query again to make sure there is no lock query occurs.