How to find long-running queries in PostgreSQL

Long queries can slow down your system/app, and it will be helpful to be able to identify them and kill if needed.

These few PostgreSQL methods will help you identify these long-running queries and kill them:

1. pg_stat_activity

   💡 Used to identify long-running queries by comparing the current time with the query_start time.

2. pg_terminate_backend

   💡 To kill long-running queries identified using pg_stat_activity. It's a safer alternative to the harsher kill command at the OS level, as it allows PostgreSQL to clean up the connection properly.

3. pg_locks

   💡 Helpful in understanding the locks that long-running queries might be holding, which can cause blockages in the database.

4. EXPLAIN and EXPLAIN ANALYZE

   💡 Useful for analyzing why certain queries are running for a long time by understanding their execution plans.

5. pg_cancel_backend

   💡 To attempt to cancel a long-running query before deciding to terminate it entirely with pg_terminate_backend.

Find PostgreSQL Queries running longer than 2 Minutes

To identify queries running for more than two minutes, we can use the PostgreSQL's pg_stat_activity view.

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

 pid  |   duration   |                               query                                
------+--------------+--------------------------------------------------------------------
 1234 | 00:03:12.345 | SELECT * FROM large_table WHERE condition;

This query lists the process ID (pid), duration, and the query text of each query running longer than two minutes.

Killing Long-Running PostgreSQL Queries (> 2 minutes)

To terminate a long-running query, use the pg_terminate_backend function.

SELECT pg_terminate_backend(pid) 
FROM pg_stat_activity 
WHERE (now() - pg_stat_activity.query_start) > interval '2 minutes';

 pg_terminate_backend 
----------------------
 t

Here, t indicates successful termination of the query. Ensure to use this command cautiously, as abruptly terminating queries can lead to partial data updates or other issues.

Show PostgreSQL Locks

Long-running queries often cause locks in the database. To view these locks:

SELECT pid, relation::regclass, mode, granted 
FROM pg_locks 
JOIN pg_stat_activity ON pg_locks.pid = pg_stat_activity.pid;

 pid  |   relation   |        mode         | granted 
------+--------------+---------------------+---------
 1234 | large_table  | AccessShareLock     | t

This output provides details on which tables are locked, the type of lock, and whether the lock was granted.

Finding Long Queries on a Specific Table

To find long queries specifically affecting a particular table, modify the initial query:

SELECT pid, now() - pg_stat_activity.query_start AS duration, query 
FROM pg_stat_activity 
WHERE (now() - pg_stat_activity.query_start) > interval '2 minutes'
AND query LIKE '%your_table_name%';

Replace your_table_name with the actual table name.

How to kill a Postgres Query using CLI

Aside from the SQL method mentioned earlier, you can also kill a process via the command line.

kill -9 [pid]

Replace [pid] with the actual process ID. This method should be used as a last resort, as it forcefully terminates the process and can lead to data corruption.