DBA Career & Certification Hub

First, at the operating system level, I run top or htop to verify whether the CPU consumption is driven by postgres: active worker processes. Inside PostgreSQL, I proceed through the following diagnostic steps: Inspect pg_stat_activity: Query for long-running sessions where state = 'active', examining the query, query_start, and specifically the wait_event_type and wait_event columns to determine if workers are waiting on I/O, locks, or are actively burning CPU cycles. Check for Lock Contention: If wait events indicate locking, I join pg_locks against pg_stat_activity to construct the blocking tree and identify the blocker session holding an exclusive lock. Mitigate the Immediate Load: I terminate non-critical, runaway queries using pg_cancel_backend(pid) (graceful signal) or pg_terminate_backend(pid) if the process does not yield. Post-Mortem Analysis: Once the cluster stabilizes, I consult the pg_stat_statements extension to isolate problematic queries exhibiting high total_exec_time, elevated mean_exec_time, or massive shared buffer reads (shared_blks_read). I inspect their execution plans using EXPLAIN (ANALYZE, BUFFERS) to spot missing indexes, sequential scans on large tables, or inaccurate planner estimates requiring manual ANALYZE."