Postgresql - Finding and Killing Long Running Queries

there is a loss of our database performance has decreased. and we must check against the current query. any query that runs very long which causes stuck background process.
this can make our server very slow and then down.

We can check with this query below :

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

If state is idle you don’t need to worry about it, but active queries may be the reason behind low performances on your database.

For cancel long running query you can execute with this

SELECT pg_cancel_backend(__pid__);

Pid is paramater that you can get from pg_stat_activity select.

If you find the procces can not cancel and stuck you can kill it by running

SELECT pg_terminate_backend(__pid__);

Pg_terminate will terminate the entire process which can lead to a full database restart in order to recover consistency. So be careful with that