Monitor Running Queries in PostgreSQL using pg_stat_activity

Monitor Running Queries in PostgreSQL (9.2+ and Newer)

Overview

When managing a PostgreSQL database, one of the most important tasks for database administrators and developers is monitoring currently running queries. This helps identify problematic sessions, troubleshoot performance bottlenecks, and optimize long-running queries. The query uses PostgreSQL’s built-in view pg_stat_activity to list all active queries.

Purpose of the Query

This query helps database administrators:

• Identify active queries running in PostgreSQL.
• Detect long-running queries that may indicate performance issues.
• Troubleshoot query locks, blocking sessions, or resource-heavy operations.
• Exclude the monitoring query itself (by filtering out references to pg_stat_activity).

Sample Code from Command Line

 1SELECT
 2    pid,
 3    age(clock_timestamp(), query_start),
 4    usename,
 5    application_name,
 6    query
 7FROM
 8    pg_stat_activity
 9WHERE
10    state != 'idle'
11      AND
12    query NOT ILIKE '%pg_stat_activity%'
13ORDER BY
14    query_start DESC;

Breakdown & Key Points

• pid → The process ID of the backend running the query. Useful for terminating queries with pg_terminate_backend(pid).
• age(clock_timestamp(), query_start) → Shows how long the query has been running. This helps spot queries consuming excessive execution time.
• usename → Username of the session owner running the query.
• application_name → Identifies the client application connected to PostgreSQL (e.g., psql, PgAdmin, or a custom app).
• query → The actual SQL text being executed by the session.
• state != 'idle' → Ensures only active queries are displayed (ignores idle connections).
• query NOT ILIKE '%pg_stat_activity%' → Prevents including this very monitoring query in the results.
• ORDER BY query_start DESC → Orders results by start time, showing the most recent queries first.

Key Insights & Use Cases

1. Performance Monitoring
   Quickly detect slow queries running for a long time, which may block other transactions.
2. Troubleshooting Client Applications
   Identify which users or applications are causing heavy loads.
3. Database Administration
   Track down sessions that need to be terminated when they consume too many resources or lock critical tables.
4. Query Optimization
   By regularly monitoring queries, you can collect evidence on what queries should be indexed, rewritten, or cached.

Best Practices

• Use this query as a foundation for building monitoring dashboards.
• Automate alerts for long-running queries (e.g., > 5 minutes).
• Combine with PostgreSQL’s pg_locks view when troubleshooting deadlocks or lock waits.
• Consider lightweight monitoring tools like pg_stat_statements for aggregate query statistics.

References & Further Reading

• PostgreSQL Official Docs: pg_stat_activity
• PostgreSQL Wiki: Monitoring Queries
• Tooling: pgAdmin, pgbadger, and custom dashboards for query monitoring.