How To Find and Kill Long Running Queries In PostgreSQL

Faruk Erdem

6 years ago

You can use the following queries to monitor the performance of the database or to find and kill queries that cause lock.

The following query returns queries that last longer than 5 minutes.

SELECT  pid,  
now() - pg_stat_activity.query_start AS duration,
  query, 
state  
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes';

SELECT pid,

now() - pg_stat_activity.query_start AS duration,

query,

state

FROM pg_stat_activity

WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes';

You can find queries that have ExclusiveLock with the following query.

SELECT * FROM pg_locks pl LEFT JOIN pg_stat_activity psa ON pl.pid = psa.pid where mode ='ExclusiveLock';

1	SELECT * FROM pg_locks pl LEFT JOIN pg_stat_activity psa ON pl.pid = psa.pid where mode ='ExclusiveLock';

We have found your query with the help of the above queries.

After learning the pid (process id) value of the query, we can stop the query with the help of the following query.

SELECT pg_cancel_backend(__ pid__);

1	SELECT pg_cancel_backend(__ pid__);

It may take some time to stop the query completely using the pg_cancel_backend command.

Or you can kill that session directly by using the command below.

SELECT pg_terminate_backend(__ pid__);

1	SELECT pg_terminate_backend(__ pid__);

Important Note:The pg_terminate_backend command can cause inconsistency.