PostgreSQL 성능 진단을 위한 자료 수집 용 쿼리

-- ====================================================================
-- 각 지표 마다 부하가 발생 시점의 쿼리를 확인 하여 성능에 문제점을 유추한다..
-- ====================================================================

-- 0) SESSIONS (state 분포)
 
SELECT state, count(*) AS cnt
FROM pg_stat_activity
GROUP BY state;

-- 1) WAIT EVENTS (I/O/Lock 등) 
SELECT wait_event_type, wait_event, count(*) AS cnt
FROM pg_stat_activity
WHERE wait_event IS NOT NULL
GROUP BY 1,2;

-- 2) CACHE HIT (DB별) 
SELECT d.datname,
       (sum(blks_hit)::numeric / NULLIF(sum(blks_hit)+sum(blks_read),0)) AS cache_hit_ratio,
       CASE
         WHEN (sum(blks_hit) + sum(blks_read)) = 0 THEN 'OK'
         WHEN (sum(blks_hit)::numeric / NULLIF(sum(blks_hit)+sum(blks_read),0)) >= 0.99 THEN 'OK'
         WHEN (sum(blks_hit)::numeric / NULLIF(sum(blks_hit)+sum(blks_read),0)) >= 0.95 THEN 'WARN'
         ELSE 'BAD'
       END AS status
FROM pg_stat_database d
GROUP BY d.datname;

-- 3) IO HOT TABLES (읽기 많은 순) 
SELECT relname,
       heap_blks_read,
       heap_blks_hit,
       COALESCE(idx_blks_read,0) AS idx_blks_read,
       COALESCE(idx_blks_hit,0)  AS idx_blks_hit,
       (heap_blks_read + COALESCE(idx_blks_read,0)) AS total_reads
FROM pg_statio_user_tables;

-- 4) BGWRITER / CHECKPOINT 
SELECT checkpoints_timed, checkpoints_req,
       checkpoint_write_time, checkpoint_sync_time,
       buffers_checkpoint, buffers_clean, maxwritten_clean,
       buffers_backend, buffers_backend_fsync, buffers_alloc
FROM pg_stat_bgwriter;

-- 5) TEMP/BLK IO TIME 
SELECT datname,
       temp_files,
       temp_bytes,
       (temp_bytes/1024.0/1024.0)::numeric(20,2) AS temp_mb,
       blk_read_time, blk_write_time,
       CASE
         WHEN temp_bytes IS NULL OR temp_bytes=0 THEN 'OK'
         WHEN temp_bytes < 512*1024*1024 THEN 'OK'     -- < 512MB
         WHEN temp_bytes < 2*1024*1024*1024 THEN 'WARN'-- < 2GB
         ELSE 'BAD'
       END AS status
FROM pg_stat_database;

-- 6) LOCKS (요약) 
SELECT locktype, mode, granted, count(*) AS cnt
FROM pg_locks
GROUP BY 1,2,3;

-- 6-1) LOCK WAITERS 
SELECT a.pid         AS waiter_pid,
       a.usename     AS waiter_user,
       a.state       AS waiter_state,
       a.wait_event_type, a.wait_event,
       now() - a.query_start AS waiting_for,
       LEFT(a.query, 800)    AS waiter_query,
       bl.pid        AS blocker_pid,
       bl.usename    AS blocker_user,
       now() - bl.query_start AS blocker_runtime,
       LEFT(bl.query, 800)   AS blocker_query
FROM pg_stat_activity a
JOIN pg_locks wl ON wl.pid = a.pid AND NOT wl.granted
JOIN pg_locks blck
  ON blck.locktype      = wl.locktype
 AND blck.DATABASE      IS NOT DISTINCT FROM wl.DATABASE
 AND blck.relation      IS NOT DISTINCT FROM wl.relation
 AND blck.page          IS NOT DISTINCT FROM wl.page
 AND blck.tuple         IS NOT DISTINCT FROM wl.tuple
 AND blck.virtualxid    IS NOT DISTINCT FROM wl.virtualxid
 AND blck.transactionid IS NOT DISTINCT FROM wl.transactionid
 AND blck.classid       IS NOT DISTINCT FROM wl.classid
 AND blck.objid         IS NOT DISTINCT FROM wl.objid
 AND blck.objsubid      IS NOT DISTINCT FROM wl.objsubid
 AND blck.granted
JOIN pg_stat_activity bl ON bl.pid = blck.pid;

-- 7) AUTOVAC / ANALYZE 최근 기록 
SELECT schemaname, relname,
       last_vacuum, last_autovacuum,
       last_analyze, last_autoanalyze
FROM pg_stat_all_tables
WHERE schemaname NOT IN ('pg_catalog','information_schema');

-- 8) INDEX USAGE (인덱스 기준) 
SELECT schemaname,
       relname      AS table_name,
       indexrelname AS index_name,
       idx_scan,
       idx_tup_read,
       idx_tup_fetch
FROM pg_stat_user_indexes;

-- 8-1) TABLE별 인덱스 사용률 
SELECT relname,
       seq_scan,
       idx_scan,
       CASE WHEN (seq_scan + idx_scan) > 0
            THEN ROUND(100.0 * idx_scan / (seq_scan + idx_scan), 2)
            ELSE NULL
       END AS idx_usage_pct
FROM pg_stat_user_tables;

-- 9) LONG RUNNING (idle 제외) 
SELECT pid, usename, datname, application_name, client_addr,
       now() - xact_start  AS xact_age,
       now() - query_start AS query_age,
       state, wait_event_type, wait_event,
       LEFT(query, 1200) AS query
FROM pg_stat_activity
WHERE state <> 'idle';