pg_stat_activity Gaps: Queries Not Visible

Scenario

A developer reports that pg_stat_activity is showing connections with state = 'idle' and query containing only the last query — they want to see what queries are currently running. After an incident, the DBA finds that pg_stat_activity.query shows only 1024 characters truncated, the wait_event_type wasn’t recorded for a blocking query, and a monitoring user without pg_monitor role can only see their own sessions. The team is also confused about the difference between state = 'active' and state = 'idle in transaction'.

How to Identify

Conditions:

• pg_stat_activity.query shows truncated text ending with ...
• Monitoring user’s query returns fewer rows than expected (missing other users’ sessions)
• wait_event_type and wait_event are NULL for sessions that appear blocked
• state = 'idle in transaction' sessions have been running for hours
• application_name is empty — makes it hard to identify which app the connection is from

Analysis Steps

-- Find all active queries and their state:
SELECT pid, usename, application_name, state, wait_event_type, wait_event,
       now() - query_start AS query_age, left(query, 100) AS query_preview
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY query_start;

-- Find long-running transactions (idle in transaction = transaction open but not executing):
SELECT pid, usename, state,
       now() - xact_start AS txn_age,
       now() - state_change AS state_age,
       left(query, 80) AS last_query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY xact_start;

-- Find blocked queries (waiting for a lock):
SELECT pid, wait_event_type, wait_event, query
FROM pg_stat_activity
WHERE wait_event_type = 'Lock';

Pitfalls

• Non-superuser roles without pg_monitor or pg_read_all_stats can only see their own sessions in pg_stat_activity — other sessions appear with query = NULL or are hidden.
• track_activity_query_size defaults to 1024 bytes — long queries are truncated. Set higher for complex SQL.
• state = 'idle in transaction' is dangerous: the transaction holds locks and prevents autovacuum. Set idle_in_transaction_session_timeout to automatically terminate these.
• backend_type = 'autovacuum worker' rows in pg_stat_activity are expected — they represent background maintenance.
• pg_stat_activity.query shows the most recently executed statement for idle connections — not “currently executing”. Only state = 'active' sessions are running a query now.
• wait_event_type = 'Lock' is the most actionable — it means the query is blocked waiting for a lock held by another session.

Resolution Approach

Grant pg_monitor role to monitoring users to see all sessions. Increase track_activity_query_size to capture full query text. Set idle_in_transaction_session_timeout to kill stale open transactions. Use pg_blocking_pids() to identify lock chains.