Observation and diagnostics · Intermediate

Reading active work with pg_stat_activity

Separate connection, statement and transaction state and clocks, then use wait events and blockers to investigate current backend work.

Ask Logeia about this articleTry the three questionsReview your answers

Updated

On this page
  1. Separate execution, waiting and connection state
  2. Compare three different start times
  3. Combine wait events with blocking evidence
  4. Preserve observation time and visibility limits
  5. Ask Logeia about this articleCheck your understanding

Separate execution, waiting and connection state

Pg_stat_activity is a starting point for understanding what PostgreSQL server processes are doing now. An established connection, an executing SQL command and a process actively consuming CPU describe different things. An active backend can still be waiting for a lock or I/O. Read database, application name, process identifier, state and wait event together at a recorded observation time. This turns a vague impression of too many connections into categories of work and waiting. Distinguish an idle connection from one that is idle while keeping an unfinished transaction open.

Compare three different start times

The start timestamps answer different questions. Query_start concerns the current or most recent statement, xact_start concerns the current transaction, and backend_start identifies when the connection began. A pooled connection can be hours old while its present statement is brief. Conversely, an idle-in-transaction session may have finished its last command while retaining an old transaction. These are illustrative states rather than measurements from a production system. Interpret timestamps with the state column instead of ranking every old timestamp as equivalent evidence of a long-running query or a backend that should be terminated.

sql
SELECT pid, application_name, state, xact_start, query_start,
       wait_event_type, wait_event, pg_blocking_pids(pid) AS blockers
FROM pg_stat_activity
WHERE datname = current_database() AND pid <> pg_backend_pid();

Combine wait events with blocking evidence

Wait_event_type and wait_event help identify the resource currently awaited. For an active process waiting on a lock, combine that observation with pg_blocking_pids(pid) and a lock view to investigate the dependency. A null wait event does not prove sustained CPU use. Sampling can miss short waits, and execution time can include multiple mechanisms. Match database observations to operating-system process data and application request times. In particular, keep statement age, transaction age and a specific blocking interval separate when deciding which mechanism deserves investigation and whether a proposed intervention addresses that mechanism.

Preserve observation time and visibility limits

Visibility into another user’s query text and other details depends on monitoring privileges. Missing detail must not silently become evidence of inactivity. Use an appropriately authorized monitoring path and handle query text as potentially sensitive. Repeated reads within one long transaction can reuse an observation snapshot; confirm transaction boundaries and snapshot behavior for the installed version. Before interrupting work, verify process identity, current operation, application retries and rollback consequences. A monitoring query offers candidates for investigation, not a justification for automatically terminating whichever connection has the oldest timestamp. Preserve enough context to explain both the suspected problem and the expected effect of the chosen action.

Reading and practice are recorded separately in this browser, for each language.

Learn this in order

Understand the systems behind the web · STEP 12

Choose a learning path