PostgreSQL Administration

Killing long-running queries

This section is about stopping, that is killing, queries that should not be running.

Because users run SQL queries, they may inadvertently execute erroneous queries. These queries usually run for excessive amounts of time and produce extremely large result sets.

Because SokweDB runs in the cloud and cloud billing is usage-based, in addition to slowing down the system, these queries can cost money.

Problem overview

It is relatively easy to write such a query, if you

SELECT ...
  FROM tablea, tableb, tablec ...

and do not supply any WHERE or JOIN conditions, the result will be the cross product of all rows of all tables. In other words, each row of each table will be paired up with every row of every other table, producing A times B times C number of output rows, where A, B, and C are the number of rows in tablea, tableb, and tablec.

Some of the generic database interfaces may have ways to monitor the database backend to discover and kill such bad queries. Alternately, use the manual process below.

Permissions required

No matter the user interface used, the permission requirements are the same: Any user can kill their own queries. Only an administrator can kill other user's queries.

Finding a query to kill

To kill such a query, first find it's process number, it's pid. This is found in the pid column of the following query:

SELECT * FROM pg_stat_activity;

Killing the query

The following statement kills the query, where MYPID is the pid of the query to be killed:

SELECT pg_terminate_backend(MYPID);

The query may not always immediately stop. It is best to check that no mistake was made and the process was actually killed.