How To Find and Kill Long Running Queries In PostgreSQL

 

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.

 

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

 

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.

 

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.

Important Note:The pg_terminate_backend command can cause inconsistency.

Faruk Erdem
Author: Faruk Erdem

Leave a Reply

Your email address will not be published. Required fields are marked *