PostgreSQL lock monitoring: find the session behind the wait.

1. Find blocking sessions without exporting query text

This diagnostic query joins PostgreSQL's activity view to the sessions returned by pg_blocking_pids. It selects operational metadata, not application SQL text or row data.

SELECT waiting.pid AS waiting_pid,
       waiting.application_name AS waiting_application,
       now() - waiting.query_start AS waiting_query_age,
       waiting.wait_event_type,
       waiting.wait_event,
       blocker.pid AS blocking_pid,
       blocker.state AS blocking_state,
       now() - blocker.xact_start AS blocking_transaction_age
FROM pg_stat_activity AS waiting
CROSS JOIN LATERAL unnest(pg_blocking_pids(waiting.pid)) AS blocked_by(pid)
JOIN pg_stat_activity AS blocker ON blocker.pid = blocked_by.pid
ORDER BY waiting.query_start
LIMIT 20;

Run it with a role allowed to inspect the relevant statistics. This is a point-in-time diagnostic, not a load-tested polling loop. Frequent calls to pg_blocking_pids have overhead; measure collection impact before increasing frequency.

Reference: PostgreSQL session information functions.

2. Interpret the chain, not just the oldest query

• waiting_query_age is query age—not the time it has spent waiting for a lock.
• A blocking session can itself be blocked. Follow the chain to understand the upstream transaction; the query above shows blocking pairs, not a complete root-cause tree.
• An idle-in-transaction session may still hold locks. Check transaction age and the application responsible.
• One waiting session can have several blockers. Parallel queries and prepared transactions need additional interpretation; a prepared-transaction blocker represented by PID 0 is not shown by this join.

Do not automatically terminate whichever PID appears in the result. Understand the transaction, its owner, rollback consequences and retry behavior first. This guide does not issue cancellation or termination commands.

3. Check visibility on managed PostgreSQL

PostgreSQL restricts visibility into other sessions. A monitoring role may need statistics privileges such as pg_read_all_stats; your provider and plan determine which grants you can use. Review grants with the database owner rather than using the application's owner credentials by default.

For Heroku, RDS, Aurora, Cloud SQL and Azure, also verify network access, TLS and connection limits. A successfully connected agent does not prove that every session or metric is visible.

Reference: PostgreSQL statistics monitoring.

4. Move from a snapshot to continuous evidence

A manual query can miss a blocker that disappears before an engineer responds. GDE collects connection, cache and lock telemetry through a Go agent and presents masked blocking-query evidence alongside measurements.

SQL literal masking is not full anonymization: identifiers, structure and operational metadata can remain. GDE's SQL-only path does not collect CloudWatch metrics or managed storage capacity. Forecasts need sufficient history and are not guarantees that an incident will be predicted.