How to View Running Queries in PostgreSQL

In PostgreSQL, monitoring active queries is essential for understanding the performance and health of your database. This guide covers how to view running queries in PostgreSQL, which is crucial for diagnosing slow queries, understanding workload patterns, and identifying bottlenecks.

How to view running queries in PostgreSQL?

To see the currently executing queries in PostgreSQL, you can use the pg_stat_activity view. This view provides a snapshot of all active connections and their queries. Here’s how to retrieve information about running queries:

SELECT pid, usename, datname, query_start, state, query
FROM pg_stat_activity
WHERE state = 'active';

This SQL command will list the process ID (pid), user name (usename), database name (datname), query start time (query_start), current state of the query (state), and the text of the SQL query itself (query) for all active queries.

Filter long-running queries

Sometimes, you may want to focus on long-running queries that could be causing performance issues. You can modify the above query to filter for queries running longer than a certain duration, for example, longer than 5 minutes:

SELECT pid, usename, datname, query_start, state, query
FROM pg_stat_activity
WHERE state = 'active'
AND now() - query_start > interval '5 minutes';

Cancel or terminate queries

If you identify a problematic query that is affecting database performance, you can choose to cancel or terminate it. To cancel a query, you can use the pg_cancel_backend function with the process ID:

SELECT pg_cancel_backend(pid);

To forcibly terminate a query (and the connection), use pg_terminate_backend:

SELECT pg_terminate_backend(pid);

Use these commands with caution, as terminating queries can lead to incomplete transactions or data inconsistencies.

Understanding and monitoring running queries is a fundamental part of database administration. By regularly checking the active queries, you can gain valuable insights into the operational aspects of your PostgreSQL database and take appropriate actions to maintain its performance.