Monitor Running Queries in PostgreSQL using pg_stat_activity

calendar Aug 18, 2025 / · 8 min read · postgresql database sql performance monitoring tuning optimization queries pg_stat_activity pg_locks  ·
Share on: linkedin copy

Monitor Running Queries in PostgreSQL (9.2+ and Newer)

A query that has been running for forty minutes looks identical, from a load balancer's perspective, to one that finished in forty milliseconds — nothing in a typical connection-pool dashboard flags the difference between a session doing real, expected work and one stuck behind a lock or waiting on a slow disk. pg_stat_activity is where PostgreSQL keeps the answer current: one row per backend, updated in real time, showing exactly what every connected session is doing right now, how long it has been doing it, and who is running it. Reading it on a schedule turns a vague "the database feels slow" report into a specific PID, user, and SQL statement.

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.

The state column is what makes that filtering possible. A backend's state is always one of a small, fixed set of values: active (currently executing a query), idle (waiting for the next command from the client), idle in transaction (inside a transaction block but not currently running a statement), idle in transaction (aborted) (a transaction that hit an error and is waiting to be rolled back), fastpath function call, or disabled (when track_activities is turned off for that session). Filtering state != 'idle' in the sample query below removes the majority of connections in a typical pool — the ones simply waiting for work — and leaves the ones actually worth looking at. It's worth remembering that pg_stat_activity is a live snapshot, not a log: a query that finished a second ago is already gone from the view, which is why long-running-query alerting has to poll rather than query once and assume the picture is complete.

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).

That last point matters more than it looks. Any monitoring query issued against pg_stat_activity is itself a row in pg_stat_activity while it runs, and without the NOT ILIKE '%pg_stat_activity%' filter, every scheduled health check would show up as one more "active" query cluttering its own output. The same self-exclusion logic is worth applying to any wrapper script or dashboard job that polls this view on a fixed interval — otherwise the monitoring tool becomes noise in its own report.

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;

Notes: pg_stat_activity and the state column used above have been available since PostgreSQL 9.2. Only superusers, members of the pg_monitor role, or a session's own user can see the full query text for a row belonging to another user — everyone else sees the text replaced with <insufficient privilege>, so a monitoring role needs one of those grants to be useful.

Filtered variant, for queries that have been running longer than five minutes:

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

Blocking-session variant, using pg_blocking_pids():

 1SELECT
 2    pid,
 3    usename,
 4    wait_event_type,
 5    wait_event,
 6    query,
 7    pg_blocking_pids(pid) AS blocked_by
 8FROM
 9    pg_stat_activity
10WHERE
11    cardinality(pg_blocking_pids(pid)) > 0;

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.
  • clock_timestamp() - query_start > interval '5 minutes' → The threshold predicate in the filtered variant; swap the interval to match whatever duration counts as "too long" for a given workload.
  • cardinality(pg_blocking_pids(pid)) > 0 → Keeps only rows where the backend is currently waiting on at least one other session, turning a full activity dump into a short blocking-only list.
  • wait_event_type / wait_event → Report what class of resource a waiting backend is stuck on — a lock, a lightweight lock, client I/O, or disk I/O — and the specific named event within that class.

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.
  5. Capacity and Connection Pool Planning — A recurring pattern of many concurrent active rows against the same table points to a workload that has outgrown its current pool size or indexing strategy, well before it shows up as a user-facing timeout.

Run any of these as a one-off during an incident, or wrap the filtered variant in a scheduled job that writes results to a log table — the second approach turns a single diagnostic query into a searchable history of exactly when a given table or query pattern started causing trouble.

Version Compatibility

The base query works unmodified on PostgreSQL 9.2 and every release since — pid, query_start, usename, application_name, query, and state have been stable columns for the entire supported version range. The blocking-session variant needs more recent server versions: PostgreSQL 9.6 replaced the old boolean waiting column with the wait_event_type and wait_event pair used above, and added the pg_blocking_pids() function as the standard way to ask "who is this session waiting on" without hand-writing a self-join against pg_locks.

PostgreSQL 10 added the backend_type column, which separates ordinary client connections from background processes such as autovacuum workers and WAL senders — useful when a monitoring query needs to exclude PostgreSQL's own maintenance activity rather than just filtering by state. The same release introduced the pg_monitor predefined role referenced in the Sample Code notes above, letting a monitoring account see every session's query text without a full superuser grant.

PostgreSQL 14 added a query_id column to pg_stat_activity, populated when compute_query_id is enabled. It correlates a currently running statement directly with its aggregate row in pg_stat_statements — a live query and its historical average cost can be looked up with the same identifier instead of matching on normalized query text.

Practical Applications

The same view supports both a quick manual check during an incident and a standing job that runs unattended.

Diagnosing a Sudden Latency Spike

When response times climb without a matching deploy or traffic change, running the filtered five-minute variant is usually the fastest way to find the cause. A handful of active rows all touching the same table, all started around the same time, points at a query plan regression or a missing index far faster than reading application logs.

Building a Blocking-Session Alert

The blocking-session query pairs naturally with pg_locks for deeper investigation once pg_blocking_pids() has identified which sessions are involved. Scheduling the blocking variant every minute and alerting on any non-empty result catches lock pileups — a batch job holding a row lock while an unrelated report query waits behind it — before they cascade into a connection-pool exhaustion incident.

Safe Session Termination Workflow

Once a problem pid is identified, pg_cancel_backend(pid) stops the current query while leaving the session connected, and pg_terminate_backend(pid) closes the connection outright. Trying the softer cancel first, and confirming the query and usename columns actually match the intended target before running either function, avoids accidentally killing an unrelated session that happens to share a similar duration.

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.
  • Grant pg_monitor, not superuser, to monitoring accounts — it exposes query text and statistics views without handing out full administrative rights.
  • Poll on a fixed interval rather than once — pg_stat_activity has no history; a query that finished between two manual checks leaves no trace in the view itself.
  • Prefer pg_cancel_backend() before pg_terminate_backend() — canceling the query is less disruptive than dropping the whole connection, and often solves the problem without an application-side reconnect.

References

Posts in this series