The SQL diagnostic

Run this read-only query against the PostgreSQL environment you are investigating. Interpret the results in the context of your workload and privileges.

SELECT
    pid,
    usename,
    datname,
    application_name,
    client_addr,
    state,
    wait_event_type,
    wait_event,
    CASE WHEN state = 'active'
         THEN now() - query_start END AS active_query_age,
    now() - xact_start AS transaction_age,
    left(query, 160) AS query_preview
FROM pg_stat_activity
WHERE backend_type = 'client backend'
  AND state <> 'idle'
ORDER BY xact_start NULLS LAST,
         query_start NULLS LAST;

Based on DBA-015. This version adds wait-event context and labels active-query age separately from transaction age.

How to read the results

Why the filter matters: ordinary idle sessions are excluded from this quick view. They still consume connections, so use a connection-pressure diagnostic if you need to count the whole pool.

What you are seeing: the query column shows the current statement for an active session, but the most recent statement for a non-active session. It is not proof that an idle-in-transaction session is still executing that text.

What to check next

1. Start with state and wait event. An active session waiting on a Lock event is different from an active query consuming CPU.
2. Look for long-open transactions. Investigate idle in transaction sessions whose transaction ages are unexpectedly large.
3. Identify the owning application. Check the username, database and application_name before contacting an application owner.
4. Move to the right next diagnostic. Use DBA-016 for elapsed queries and transactions, DBA-018 for blocker relationships, or DBA-020 for total connection pressure.

Related DBA tasks

Continue the investigation with these recipes from SQL Recipes for DBAs: DBA-016 (long-running queries and transactions), DBA-018 (blocking sessions), DBA-019 (inspect locks), and DBA-020 (connection pressure).