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,
    application_name,
    state,
    CASE WHEN state = 'active'
         THEN now() - query_start END AS active_query_age,
    now() - xact_start AS transaction_age,
    left(query, 120) AS query_preview
FROM pg_stat_activity
WHERE backend_type = 'client backend'
  AND (
       (state = 'active'
        AND query_start < now() - interval '5 minutes')
       OR
       (xact_start IS NOT NULL
        AND xact_start < now() - interval '15 minutes')
      )
ORDER BY xact_start NULLS LAST,
         query_start NULLS LAST;

Adapted from DBA-016. Query age is shown only for active statements, while transaction age is based on xact_start.

How to read the results

The example uses five-minute query and fifteen-minute transaction thresholds from the book’s troubleshooting approach. Those are starting thresholds, not universal danger limits. Analytical jobs and maintenance operations can legitimately run longer than short OLTP transactions.

Unlike a query-duration report, this includes a long-open transaction even when its session is currently idle in transaction.

What to check next

1. Separate slow statements from forgotten transactions. An active query may be working or waiting; an idle transaction is a session waiting for the next client command.
2. Check for blockers. Use the blocking-session report if other operations are hanging. Avoid interpreting elapsed time alone as proof of lock blocking.
3. Find the application owner. Trace the application name and connection pool settings before treating a session as abandoned.
4. Correct the cause. Look for transaction boundaries and application error paths that fail to commit or roll back.

Related DBA tasks

Continue the investigation with these recipes from SQL Recipes for DBAs: DBA-015 (active sessions), DBA-017 (cancel or terminate), DBA-018 (blocking sessions), and DBA-037 (vacuum and maintenance).