Diagnosing Long‑Running Active Queries in PostgreSQL

Recognizable condition

When the database shows sustained CPU or I/O spikes, a common first sign is a query that stays in the active state for an unusually long time without waiting on an event. In PostgreSQL this appears as rows in pg_stat_activity where state = 'active', wait_event_type IS NULL, and the query has been running for several minutes.

Short cause/diagnostic table

wait_event_type	Typical root cause
Lock	Row‑level lock contention or a transaction holding a lock longer than expected
IO	Data pages not found in shared buffers, causing heavy disk reads
Buffer	Buffer pinning delays, often due to insufficient work_mem causing spills to disk
NULL (no wait event)	CPU‑bound work: missing index, inefficient planner estimates, or a computationally heavy expression

Ordered checks

1. Identify the long‑running active query

   SELECT pid, usename, application_name, client_addr,
          state, state_change,
          now() - state_change AS runtime,
          query
   FROM pg_stat_activity
   WHERE state = 'active'
     AND wait_event_type IS NULL
     AND state_change < now() - interval '5 minutes'
   ORDER BY runtime DESC;
   

   Run this as a role with pg_read_all_stats (or superuser) permission. The output shows the PID and the full query text.

2. Check for locks held by the query

   SELECT blocked.pid    AS blocked_pid,
          blocked.usename AS blocked_user,
          blocking.pid    AS blocking_pid,
          blocking.usename AS blocking_user,
          locktype, mode,
          granted
   FROM pg_locks blocked
   JOIN pg_locks blocking
     ON blocked.locktype = blocking.locktype
    AND blocked.database IS NOT DISTINCT FROM blocking.database
    AND blocked.relation IS NOT DISTINCT FROM blocking.relation
    AND blocked.page IS NOT DISTINCT FROM blocking.page
    AND blocked.tuple IS NOT DISTINCT FROM blocking.tuple
    AND blocked.virtualxid IS NOT DISTINCT FROM blocking.virtualxid
    AND blocked.transactionid IS NOT DISTINCT FROM blocking.transactionid
    AND blocked.classid IS NOT DISTINCT FROM blocking.classid
    AND blocked.objid IS NOT DISTINCT FROM blocking.objid
    AND blocked.objsubid IS NOT DISTINCT FROM blocking.objsubid
   WHERE blocked.granted = false
     AND blocking.granted = true
     AND blocked.pid <> blocking.pid;
   

   If rows appear, the long‑running query is waiting on a lock held by another session.

3. Examine I/O and buffer statistics for the tables involved

   SELECT relname,
          heap_blks_read, heap_blks_hit,
          idx_blks_read, idx_blks_hit
   FROM pg_statio_all_tables
   WHERE schemaname = 'public'
   ORDER BY heap_blks_read DESC
   LIMIT 10;
   

   High heap_blks_read relative to heap_blks_hit suggests the query is reading many pages from disk.

4. Run EXPLAIN ANALYZE on the offending query

   Copy the query text from step 1 (replace any literals with placeholders if needed) and execute:

   EXPLAIN ANALYZE
   SELECT ...
   FROM ...
   WHERE ...;
   

   Look for:

   • Seq Scan on a large table (possible missing index)
   • High "Rows Removed by Filter" (ineffective filter)
   • Spill to disk in "Sort" or "Hash" nodes (insufficient work_mem)

Fixes tied to findings

• Missing index – If EXPLAIN ANALYZE shows a sequential scan where an index scan is expected, create the appropriate index:

  CREATE INDEX CONCURRENTLY idx_table_column ON public.table (column);
  

  Run during low‑traffic periods; CONCURRENTLY avoids locking writes.

• Lock contention – Identify the blocking PID from step 2 and consider:
  • Shortening the transaction that holds the lock (e.g., commit earlier).
  • Reviewing application logic for unnecessary long‑running transactions.

  If the blocker is idle or stuck, you may terminate it after verifying it is not a superuser or replication session:

  SELECT pg_terminate_backend();
  

  Only run with superuser rights; terminating a replication standby can cause lag.

• High I/O due to insufficient work_mem – If the plan shows a sort/hash spill, increase work_mem for the session or globally:

  SET work_mem = '64MB';  -- session-level
  -- or edit postgresql.conf
  work_mem = 64MB
  

  Monitor pg_stat_database.temp_files to confirm spill reduction.

• CPU‑bound complex expression – Consider rewriting the query, using LIMIT or OFFSET pagination, or materializing intermediate results in a temporary table.

Escalation criteria

1. If you have to terminate three or more sessions (superuser or replication) within a short window (15 minutes), stop and review:
• Application connection handling – are connections being returned to the pool?
• Whether a connection pooler (e.g., PgBouncer) is in use and correctly sized.
• Schema design – are hot tables excessively wide or lacking partitioning?
2. When the same query repeatedly appears as the top long‑running runner despite fixes, consider:
• Updating planner statistics: ANALYZE on the involved tables.
• Reviewing default_transaction_isolation; the default read committed is usually appropriate, but repeatable reads can increase lock duration.
3. If load spikes persist after query‑level fixes, evaluate system‑level resources:
• Check iostat or vmstat for sustained disk saturation.
• Verify shared_buffers and effective_cache_size are sized for the workload.

Verification

After applying a fix, confirm the symptom is gone:

1. Re‑run the query from step 1; the offending PID should no longer appear, or its runtime should be under the threshold (e.g., < 2 minutes).
2. Check that pg_stat_database.temp_files has decreased if you increased work_mem.
3. If you added an index, run EXPLAIN (ANALYZE, BUFFERS) and verify the plan now uses an index scan and buffer hits have increased.

Limitations and cautions

• The columns wait_event_type and wait_event were introduced in PostgreSQL 9.6; in older versions they may be NULL for all rows, so rely on state_change age instead.
• Killing a superuser session can abort critical maintenance tasks (e.g., VACUUM) and cause replication lag on standbys. Always verify the session’s purpose with pg_stat_replication or pg_bgworker before terminating.
• Increasing work_mem raises memory usage per operation; setting it too high can lead to out‑of‑memory errors under concurrent workloads. Monitor pg_stat_activity for mem_used (via pg_stat_progress_create_index or OS tools) after changes.