Sometimes when playing around with large PostgreSQL datasets in an SQL GUI, Grafana dashboards, or from any other tool, you might make a query mistake which makes the query run forever. This could eat up your database CPU or your max IOPs (in case of cloud services).

You can find long running queries by executing the next command in an SQL GUI:

-- list long running queries
-- (where "long" is the '2 minutes' specified below, tune the '2 minutes' as required):

select (now() - query_start) as runtime, *
from pg_stat_activity
where state = 'active'
and query_start < (now() - interval '2 minutes')
and query not like '%VACUUM%'
order by 1 desc
limit 10;Code language: SQL (Structured Query Language) (sql)

To nicely ask all long running queries to be stopped, execute this:

-- nicely ask to stop long running queries
-- (where "long" is the '2 minutes' specified below, tune the '2 minutes' as required):

select *, pg_cancel_backend(pid)
from pg_stat_activity
where state = 'active'
and query_start < (now() - interval '2 minutes')
and query not like '%VACUUM%'
limit 10;
commit;Code language: SQL (Structured Query Language) (sql)

And if you want a forced kill of long running queries, execute this:

-- KILL long running queries
-- (where "long" is the '2 minutes' specified below, tune the '2 minutes' as required):

select *, pg_terminate_backend(pid)
from pg_stat_activity
where state = 'active'
and query_start < (now() - interval '2 minutes')
and query not like '%VACUUM%'
limit 1000;
commit;Code language: SQL (Structured Query Language) (sql)

If instead of stopping multiple queries, you just want to stop one of them, you can sue one of the next two statements. You can see in the “query” column of the first “find slow ones” query what the actual SQL statement is in case you need to find a specific one. And you can find the proper “pid” field value to use in the cancel or terminate calls:

-- Nicely ask to stop one query, find it's PID value from the first mentioned select on this page
-- Fill in the PID value instead of the shown 123456:

select pg_cancel_backend(123456); commit;


-- Ask to KILL one query, find it's PID value from the first mentioned select on this page
-- Fill in the PID value instead of the shown 123456:

select pg_cancel_backend(123456); commit;Code language: SQL (Structured Query Language) (sql)