When queries run for a long time, they can overload the database, increase CPU usage, and hold onto connections, slowing down your application. The priority is to quickly identify and stop these queries, then optimize them afterward. In this guide, we’ll cover how to detect and terminate long-running queries in PostgreSQL, helping you prevent bottlenecks and outages.

 

Step 1: Identify Long-Running Queries

PostgreSQL provides a system view called pg_stat_activity to help identify running queries. This view shows server process IDs (PIDs), sessions, queries, and other metadata for each active query process.

To find long-running queries, you can run the following query:

SELECT 
    pid, 
    age(clock_timestamp(), query_start) AS age, 
    query,
    *
FROM 
    pg_stat_activity
WHERE 
    query NOT ILIKE '%pg_stat_activity%'
    AND query_start IS NOT NULL
    AND state = 'active'
ORDER BY 
    age DESC;

Sample Output:

Here’s an example of what you might see when running the query:

  pid  |       age       |           query           | datid | datname  |  pid  | leader_pid | usesysid |     usename     | application_name | client_addr | client_hostname | client_port |         backend_start          |            xact_start            |           query_start            |           state_change           | wait_event_type | wait_event | state  | backend_xid | backend_xmin | query_id |           query           |  backend_type
-------+-----------------+---------------------------+-------+----------+-------+------------+----------+-----------------+------------------+-------------+-----------------+-------------+--------------------------------+----------------------------------+----------------------------------+----------------------------------+-----------------+------------+--------+-------------+--------------+----------+---------------------------+----------------
 14414 | 00:01:01.244224 | select * from test_table; | 14042 | postgres | 14414 |            |       10 | pramodchoudhari | psql             |             |                 |          -1 | 2024-09-10 14:20:36.3632+05:30 | 2024-09-10 15:12:12.661608+05:30 | 2024-09-10 15:12:12.661608+05:30 | 2024-09-10 15:12:12.661622+05:30 |                 |            | active |             |       417622 |          | select * from test_table; | client backend

The pid represents the process ID associated with the query, and the age column tells you for how long the query has been running.

 

Step 2: Stop the Long-Running Queries

There are two ways to stop a long-running query:

  1. Using pg_cancel_backend: This will cancel the query but keep the session alive.
  2. Using pg_terminate_backend: This will terminate the entire session and free up all the resources.

Example:

If you want to stop a long-running query with PID 14414 , you can use the following commands:

  • To cancel the query and keep the session alive:
SELECT pg_cancel_backend(14414);
  • To terminate the session and kill the query entirely:
SELECT pg_terminate_backend(14414);

 

Step 3: Setting statement_timeout at User Level

You can set different statement_timeout values for different types of users to balance performance and accommodate long-running queries in specific use cases:

  • Webserver User: Set a short timeout to ensure web requests don’t get stuck on long queries, ensuring responsiveness
ALTER ROLE webserver_user SET statement_timeout = '10s';

 

  • Script User: Set a longer timeout to allow more time for scripts, which might be running more intensive operations.
ALTER ROLE script_user SET statement_timeout = '1min';

 

  • Migration User: Set a even longer timeout for database migrations since they might involve extensive operations.
ALTER ROLE migration_user SET statement_timeout = '5min';

 

Example of statement_timeout in Action

postgres=# INSERT INTO large_table (name) SELECT 'Row ' || generate_series(1,10000000);
ERROR:  canceling statement due to statement timeout

In this case, the query to insert 10 million rows into the table was automatically canceled due to the configured statement_timeout. This showcases how PostgreSQL protects the system from excessively long queries when statement_timeout is set.

 

Summary

  • Use pg_cancel_backend when you want to stop a long-running query but keep the session alive.
  • Use pg_terminate_backend when you need to forcefully disconnect the session and free up resources.
  • Set statement_timeout at the user level for different roles (webserver, scripts, migrations) to ensure a balanced approach between performance and long-running tasks.
  • Regularly monitor your database for long-running queries and setup alarms to prevent such performance issues.