How to Identify and Monitor Slow Queries in PostgreSQL: A Practical Guide

calendar May 19, 2025 / · 3 min read · PostgreSQL SQL database performance monitor running queries pg_stat_activity  ·
Share on: linkedin copy

PostgreSQL Query for Monitoring Slow Running Queries

Purpose

Monitoring and optimizing database performance is critical for any production environment. One common challenge is identifying slow queries that may impact overall system responsiveness. This article explains how to use a PostgreSQL SQL query to find currently executing queries that have been running for more than 30 seconds, providing a practical alternative to the traditional slow query log.

Sample Code from Command Line

 1SELECT
 2    now() - query_start as "runtime",
 3    usename,
 4    datname,
 5    -- not available on PostgreSQL < 9.6
 6    wait_event,
 7    -- not available on PostgreSQL < 9.2
 8    state,
 9    -- current_query on PostgreSQL < 9.2
10    query
11FROM
12    pg_stat_activity
13WHERE
14    -- can't use 'runtime' here
15    now() - query_start > '30 seconds'::interval
16ORDER BY
17    runtime DESC;

Code Breakdown and Explanation

  • now() - query_start as "runtime": Calculates how long each query has been running by subtracting the query's start time from the current time.

  • usename: Displays the username of the session executing the query.

  • datname: Shows the database name where the query is running.

  • wait_event: Indicates what the backend is currently waiting for (available in PostgreSQL 9.6+).

  • state: Shows the current state of the query (e.g., active, idle). This is available in PostgreSQL 9.2+.

  • query: Displays the actual SQL query text being executed.

The query pulls data from the pg_stat_activity system view, which provides real-time information about all active backend processes.

Key Points and Insights

  • Current Snapshot: This query gives you a real-time snapshot of all queries currently running longer than 30 seconds, making it a valuable tool for live monitoring.

  • Version Compatibility: Some columns (like wait_event and state) are only available in newer PostgreSQL versions (9.2+ and 9.6+ respectively). If you are using an older version, you may need to adjust the query accordingly.

  • Alternative to Slow Query Log: Unlike the slow query log, which records queries after execution, this method lets you see long-running queries in real time, enabling faster intervention.

  • Sorting: The results are ordered by runtime, so the longest-running queries appear first, helping you prioritize which queries to investigate.

Practical Usage

  • Performance Troubleshooting: Use this query to quickly identify problematic queries that may be causing performance bottlenecks.

  • Resource Management: By monitoring long-running queries, you can decide whether to terminate problematic sessions or optimize specific queries.

  • Automation: Integrate this query into monitoring scripts or alerting systems to proactively notify DBAs about slow queries.

Conclusion

Proactively monitoring slow queries is essential for maintaining optimal PostgreSQL database performance. By leveraging the provided SQL query, administrators can quickly identify and address long-running queries, ensuring system health and responsiveness. Always check your PostgreSQL version for compatibility with specific columns in pg_stat_activity, and consider integrating this approach into your regular monitoring workflow for best results

References