How to find long-running queries in PostgreSQL

SimpleBackups founder

Laurent Lemaire

Co-founder, SimpleBackups

November 23, 2023

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

PostgreSQL Long Running Queries

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.


    💡 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';

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 =;
 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.

Back to blog

Stop worrying about your backups.
Focus on building amazing things!

Free 7-day trial. No credit card required.

Have a question? Need help getting started?
Get in touch via chat or at [email protected]

Customer support with experts
Security & compliance
Service that you'll love using