How to Monitor Slow Running Queries in PostgreSQL

calendar May 19, 2025 / · 8 min read · postgresql performance monitoring troubleshooting pg_stat_activity  ·
Share on: linkedin copy

Monitor Slow Running Queries in PostgreSQL with pg_stat_activity

An application hangs, the connection pool fills up, and somewhere a statement has been running for minutes. pg_stat_activity shows it for every backend on the server: how long the current query has been going, which role and database it belongs to, and the wait event it is parked on. The query below filters that view down to active client queries older than 30 seconds, longest first.

Purpose and Overview

A slow-query log records a statement only after it finishes. That is the wrong tool when an application is hanging at this moment and the statement that causes it has not completed. pg_stat_activity shows the live side: one row per server process, with the text of the statement it is executing and the time that statement began.

The view reports a snapshot, not history. A query that finished a second ago is gone, and a query that is still running appears with its start time, so the age of a statement is simply the current time minus query_start. This page covers that live view. For the historical side, the per-statement totals kept by pg_stat_statements, see the pg_stat_statements timing query.

The script returns the process ID, the role, the database, the application name, the state, the wait event, the runtime, and the query text. The process ID is the column that matters most during an incident, because it is the handle that pg_cancel_backend() and pg_terminate_backend() take.

Sample Code

 1SELECT
 2    pid,
 3    now() - query_start AS runtime,
 4    usename,
 5    datname,
 6    application_name,
 7    state,
 8    wait_event_type,
 9    wait_event,
10    left(query, 200)    AS query
11FROM pg_stat_activity
12WHERE state = 'active'
13  AND backend_type = 'client backend'
14  AND pid <> pg_backend_pid()
15  AND now() - query_start > interval '30 seconds'
16ORDER BY runtime DESC;

A second variant targets transactions that are open but not running anything, which hold locks and block vacuum without ever showing up as a slow query:

 1SELECT
 2    pid,
 3    now() - xact_start    AS transaction_age,
 4    now() - state_change  AS time_in_state,
 5    usename,
 6    datname,
 7    state,
 8    left(query, 200)      AS last_query
 9FROM pg_stat_activity
10WHERE state IN ('idle in transaction', 'idle in transaction (aborted)')
11ORDER BY transaction_age DESC;

Notes: The first query needs PostgreSQL 10 or later because of backend_type; on 9.6 delete that condition, and on 9.2 through 9.5 also remove wait_event_type and wait_event (the waiting boolean is the older equivalent). Rows for other users' sessions show mostly null columns unless the caller is a superuser or holds the pg_read_all_stats role. Change the 30 seconds literal to match the service's latency budget.

Code Breakdown

The first query is a filter over one view, and each condition removes a different kind of noise.

Runtime from query_start

query_start is the time the currently active query began; when the state is not active, it holds the start of the last query instead. Subtracting it from now() gives the age of the statement. The expression is repeated in WHERE because a column alias cannot be used in a WHERE clause, although ORDER BY accepts the alias.

Filtering on state

Without a state filter the results mislead: an idle session that ran a quick query an hour ago still has a query_start an hour old, so it looks like a one-hour query. state = 'active' keeps only backends that are executing a statement. Sessions in idle in transaction are a separate problem and get their own query above.

Excluding background processes and itself

backend_type = 'client backend' removes autovacuum workers, WAL senders, the checkpointer and other server processes that appear in the same view. pid <> pg_backend_pid() removes the monitoring session itself, which is always active while the query runs.

Wait event columns

wait_event_type and wait_event are independent of state. An active backend may or may not be waiting; when it is active and the wait event is non-null, the statement is executing but blocked somewhere. A Lock type means the backend is waiting for a heavyweight lock, IO means it is waiting for an I/O operation to complete, and Client means it is waiting for activity on the socket connected to the application. A long query with no wait event is not reported as waiting, which moves the question to the plan itself.

Truncating the query text

left(query, 200) keeps the output readable in a terminal. The query column itself is capped by track_activity_query_size, which defaults to 1024 bytes and can be changed only at server start, so very long statements arrive truncated regardless.

Key Session States

The state column drives how every row should be read.

active

The backend is executing a query. This is the only state in which query is the statement currently running and query_start is the time it began.

idle

The backend is waiting for a new command from the client. The query column holds the previous statement. A long list of idle sessions is a connection-pool sizing question, not a slow-query one.

idle in transaction

The backend is inside a transaction but not executing a statement. Locks taken earlier remain held, so a session stuck here can block others while looking harmless in the first query. The (aborted) variant is the same, except a statement in the transaction raised an error and the transaction must be rolled back.

Other values

starting, fastpath function call and disabled also exist. disabled is reported when track_activities is turned off in that backend.

Practical Applications

The query is most useful as the first step of a short loop: find the process, read what it waits on, then decide between waiting, cancelling, and fixing.

Incident triage during a latency spike

When response times jump, run the first query and look at the top few rows. A single statement at the top with a Lock wait points at a blocker, so check which session holds the lock. Many statements with the same text and no wait event point at a plan problem or a missing index, and EXPLAIN (ANALYZE, BUFFERS) on that statement is the next step.

Cancelling a runaway report

Once the process ID is known, SELECT pg_cancel_backend(12345); cancels the current query of that backend and leaves the connection open. pg_terminate_backend() ends the whole session and is the heavier option, appropriate for a session stuck in idle in transaction. A non-superuser can signal a backend only when the caller is a member of the role that owns it or holds pg_signal_backend.

Catching blocked sessions

An active backend whose wait_event_type is Lock is waiting, not working. Sort or filter on that column to list the victims, then follow the chain to the holder with pg_blocking_pids() or a join against pg_locks.

Scheduling a repeating check

Wrapping the first query in a cron job or a monitoring probe that counts rows older than a threshold gives a cheap alert for stuck queries. Record the result with a timestamp, because the view keeps no history and a statement that finishes between two runs leaves no trace.

Version Compatibility

pg_stat_activity has changed shape across releases. Before 9.2 the process ID column was procpid and the statement text was current_query. From 9.2 the view has pid, state and a boolean waiting. From 9.6, wait_event_type and wait_event replace waiting, so a wait can be classified rather than only detected.

PostgreSQL 10 added backend_type, which lets a query separate client sessions from background processes. PostgreSQL 13 added leader_pid, which links a parallel worker to its leader, and PostgreSQL 14 added query_id, which holds an identifier that is null unless compute_query_id is enabled or a module computes one. Where that identifier is available, it can be matched against the queryid column of pg_stat_statements to move from a live session to its cumulative statistics.

The wait event names themselves change between major versions, and the list is long, so match the names against the manual for the version in use before alerting on a specific string.

Best Practices

  • Grant monitoring access deliberately — give the monitoring role pg_read_all_stats so it sees query text for every session without superuser rights.
  • Filter by state before reading durations — an old query_start on an idle row is not a slow query.
  • Pair with timeouts — statement_timeout (default 0, disabled) bounds a statement from the server side, and a role- or database-level setting is safer than one global value.
  • Log what you cannot catch live — log_min_duration_statement (default -1, disabled) records completed statements over a threshold, which covers queries that finish before anyone looks.
  • Cancel before terminating — pg_cancel_backend() stops the statement and preserves the connection; reserve pg_terminate_backend() for sessions that do not respond or that sit idle in a transaction.
  • Keep the 30-second default honest — set the threshold from the application's latency budget, not from habit.

References

Posts in this series