PostgreSQL Performance Monitoring Identify Slow Queries in Real-Time

calendar Aug 31, 2024 / · 3 min read · PostgreSQL SQL Database Performance Monitoring pg_stat_activity  ·
Share on: linkedin copy

PostgreSQL: Identify Slow Running Queries in Real-Time

Purpose

This PostgreSQL SQL query provides a real-time snapshot of queries currently executing on your database that have been running for more than 30 seconds. It acts as a powerful alternative to a traditional slow query log, helping you proactively identify and troubleshoot performance bottlenecks without relying solely on historical logs.

Sample Code from Command Line

 1SELECT
 2  now() - query_start as "runtime",
 3  usename,
 4  datname,
 5  wait_event,
 6  state,
 7  query
 8FROM
 9  pg_stat_activity
10WHERE
11  now() - query_start > '30 seconds'::interval
12ORDER BY
13  runtime DESC;

Code Breakdown:

  1. SELECT now() - query_start as "runtime", usename, datname, wait_event, state, query:
    • Calculates and retrieves the following columns:
      • runtime: Calculates the duration of the query execution by subtracting the query_start time from the current time (now()).
      • usename: Fetches the username associated with the query.
      • datname: Retrieves the name of the database where the query is running.
      • wait_event: (PostgreSQL 9.6+) Displays the event the query is currently waiting on, if any.
      • state: (PostgreSQL 9.2+) Shows the current state of the query (e.g., active, idle in transaction).
      • query: Presents the text of the currently executing SQL query.
  2. FROM pg_stat_activity:
    • Queries the pg_stat_activity system view, which provides real-time information about active server processes.
  3. WHERE now() - query_start > '30 seconds'::interval:
    • Filters the results to include only queries that have been running for more than 30 seconds.
  4. ORDER BY runtime DESC:
    • Sorts the output in descending order based on the calculated runtime, presenting the longest-running queries first.

Key Points:

  • Real-Time Monitoring: This query provides immediate visibility into currently executing slow queries, enabling proactive troubleshooting.
  • Slow Query Identification: Pinpoints queries exceeding a specified execution time threshold (30 seconds in this example).
  • Detailed Information: Retrieves essential details about each query, including its duration, user, database, wait event (if applicable), and state.
  • Performance Bottleneck Detection: Helps identify queries causing performance issues or contributing to system slowdowns.
  • PostgreSQL 9.2+ Compatibility: Requires PostgreSQL version 9.2 or higher to utilize the state column, and version 9.6 or higher to access the wait_event column.

Insights:

  • Proactive Performance Management: Identify and address slow-running queries in real-time, preventing potential issues from escalating.
  • Query Optimization Opportunities: Analyze the SQL text and execution details to identify candidates for optimization.
  • Resource Contention Analysis: The wait_event information (PostgreSQL 9.6+) can reveal resource conflicts or blocking scenarios.
  • Improved User Experience: By optimizing slow queries, you ensure faster response times and a smoother experience for your users.

Explanations:

  • pg_stat_activity: A key system view that tracks the current activity of all server processes.
  • now() - query_start: Calculates the query's runtime by subtracting its start time from the current time.
  • '30 seconds'::interval: Converts the string '30 seconds' into an interval data type for comparison with the calculated runtime.

Reference: