Stopping long-running Postgres queries

As part of troubleshooting performance problems in PostgreSQL, you may need to terminate a query which is taking a long time to run.

To do this, find the Postgres process which is currently executing the query:

SELECT procpid, current_query, query_start
FROM pg_stat_activity;

The procpid column displays the Operating System's process ID that can then be used to send the INT signal which will cause Postgres to roll the query back and move on to the next one:

kill -INT procpid

If this happens regularly, you can also tell Postgres to kill any query which takes longer than a given number of milliseconds (say 1 hour) by using this setting:

statement_timeout = 3600000