Locate Long-Running and Slow Queries

1. Find logged slow statements

If PostgreSQL is configured to log statement durations and exports are enabled, use Logs Insights to find matching duration lines. Adjust the regular expression to your log format and set the duration threshold for your service.

fields @timestamp, @logStream, @message
| filter @message like /duration:/
| parse @message /duration: (?<duration_ms>[0-9.]+) ms/
| filter duration_ms > 1000
| sort duration_ms desc
| limit 100

These events generally describe completed statements. They are not a real-time list of active SQL. Logging thresholds, log prefix, and statement parameter logging must be configured safely and can affect log volume and data exposure.

2. Find currently active PostgreSQL work

Connect through your approved database access path and use a monitoring role with the necessary visibility. The following read-only query reports current statements, elapsed query time, transaction age, and wait state:

SELECT pid, usename, application_name, state,
       wait_event_type, wait_event,
       now() - query_start AS query_age,
       now() - xact_start AS transaction_age,
       left(query, 200) AS query_sample
FROM pg_stat_activity
WHERE pid <> pg_backend_pid()
  AND query_start IS NOT NULL
ORDER BY query_start;

Review the query text and owner using approved access: visibility can depend on PostgreSQL role privileges. A long age alone does not mean the query is stuck; check state, wait event, expected work, and blocking PIDs before escalation.

3. Distinguish pool wait from SQL duration

For developers, measure separate spans for request duration, acquiring a connection from the pool, executing SQL, and consuming results. Emit a trace/request ID and operation name in structured logs. A slow endpoint with a fast database statement may point to pool exhaustion, network delay, retries, or application-side processing instead.

4. Investigate before intervening

1. Capture the exact time range, query sample or query identifier, database, application, and wait state.
2. Compare repeat count, duration distribution, throughput, rows, and related resource metrics.
3. Inspect an execution plan only through a safe, approved method; EXPLAIN ANALYZE executes the query.
4. Contact the query owner and check transaction/lock impact before considering cancellation.
5. Verify recovery and record the tuning or application follow-up with an owner.