Long-Running Query Detection | PG Pilot PostgreSQL Monitoring

What is this issue?

Long-running queries can be:
- Legitimate operations (large data exports, complex reports)
- Performance problems (missing indexes, inefficient queries)
- Runaway operations (infinite loops, lock waits)

While some long queries are expected, they warrant investigation because
they consume resources and may indicate problems.

Why it matters

Resource Consumption

Long queries hold memory, CPU, and I/O resources

Lock Holding

May hold locks that block other operations

Replication Impact

Long transactions can delay replica cleanup

Connection Slot

Uses a connection slot that could serve other requests

How PG Pilot detects it

```sql
SELECT
  pid,
  usename,
  application_name,
  client_addr,
  query_start,
  age(now(), query_start) AS duration,
  state,
  wait_event_type,
  wait_event,
  query
FROM pg_stat_activity
WHERE state = 'active'
  AND query_start < now() - interval '5 minutes'
ORDER BY query_start;
```

How to fix it

1

Investigate the query

First, understand what's running:

SELECT pid, query, wait_event_type, wait_event
FROM pg_stat_activity
WHERE pid = YOUR_PID;

Check if it's waiting for locks, I/O, or actively processing.

2

Check for lock waits

See if the query is blocked:

SELECT
  blocked_locks.pid AS blocked_pid,
  blocking_locks.pid AS blocking_pid,
  blocked_activity.query AS blocked_query
FROM pg_locks blocked_locks
JOIN pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype
  AND blocking_locks.relation = blocked_locks.relation
  AND blocking_locks.pid != blocked_locks.pid
JOIN pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
WHERE blocked_locks.pid = YOUR_PID;

3

Check query progress

For VACUUM, ANALYZE, CREATE INDEX, CLUSTER:

SELECT  FROM pg_stat_progress_vacuum WHERE pid = YOUR_PID;
SELECT  FROM pg_stat_progress_create_index WHERE pid = YOUR_PID;

4

Cancel if necessary

To cancel the query gracefully:

SELECT pg_cancel_backend(YOUR_PID);

If that doesn't work, terminate the connection:

SELECT pg_terminate_backend(YOUR_PID);

Prevention

Set statement_timeout for application queries
Add appropriate indexes for common query patterns
Review and optimize slow queries regularly
Use EXPLAIN ANALYZE to understand query plans

Related Issues

Idle Transaction Connection Blocking

Related Features

Connection Management Query Monitoring

PostgreSQL Documentation

PostgreSQL EXPLAIN