8.4 정기 점검 체크리스트

SELECT pid, usename, datname, state, xact_start,
       now() - xact_start AS age, query
  FROM pg_stat_activity
 WHERE state IN ('active','idle in transaction','idle in transaction (aborted)')
   AND xact_start < now() - interval '5 minutes'
 ORDER BY xact_start;

SELECT bl.pid AS blocked, bl.query AS blocked_query,
       kl.pid AS blocking, kl.query AS blocking_query
  FROM pg_stat_activity bl
  JOIN LATERAL (
    SELECT * FROM pg_stat_activity WHERE pid = ANY(pg_blocking_pids(bl.pid))
  ) kl ON true
 WHERE cardinality(pg_blocking_pids(bl.pid)) > 0;

SELECT slot_name, slot_type, active,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS lag,
       wal_status
  FROM pg_replication_slots
 ORDER BY pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) DESC NULLS LAST;

SELECT datname,
       age(datfrozenxid) AS xid_age,
       round(age(datfrozenxid)::numeric / 2147483648, 3) AS used_ratio
  FROM pg_database
 ORDER BY age(datfrozenxid) DESC;

SELECT relname, n_live_tup, n_dead_tup,
       round(100.0 * n_dead_tup / nullif(n_live_tup, 0), 1) AS dead_pct,
       last_vacuum, last_autovacuum
  FROM pg_stat_user_tables
 WHERE n_dead_tup > 10000
 ORDER BY dead_pct DESC NULLS LAST
 LIMIT 20;

SELECT s.relname AS table, s.indexrelname AS index,
       pg_size_pretty(pg_relation_size(s.indexrelid)) AS size, s.idx_scan
  FROM pg_stat_user_indexes s
  JOIN pg_index i ON s.indexrelid = i.indexrelid
 WHERE s.idx_scan = 0 AND NOT i.indisunique
   AND pg_relation_size(s.indexrelid) > 10*1024*1024
 ORDER BY pg_relation_size(s.indexrelid) DESC;

SELECT round(mean_exec_time::numeric, 1) AS avg_ms,
       calls,
       round(total_exec_time::numeric / 1000, 1) AS total_sec,
       LEFT(query, 80) AS query
  FROM pg_stat_statements
 ORDER BY total_exec_time DESC
 LIMIT 20;

SELECT i.relname AS index,
       pg_size_pretty(pg_relation_size(i.oid)) AS size,
       round(s.avg_leaf_density, 1) AS density
  FROM pg_class i
  JOIN pgstatindex(i.oid) s ON true
 WHERE i.relkind = 'i' AND pg_relation_size(i.oid) > 100*1024*1024
 ORDER BY density LIMIT 10;

SELECT schemaname, relname,
       pg_size_pretty(pg_total_relation_size(relid)) AS total,
       pg_size_pretty(pg_relation_size(relid)) AS table,
       pg_size_pretty(pg_indexes_size(relid)) AS indexes,
       pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) AS toast_idx
  FROM pg_stat_user_tables
 ORDER BY pg_total_relation_size(relid) DESC
 LIMIT 20;

SELECT relname, n_mod_since_analyze, last_analyze, last_autoanalyze
  FROM pg_stat_user_tables
 ORDER BY GREATEST(last_analyze, last_autoanalyze) NULLS FIRST
 LIMIT 10;

SELECT pid, datname, relid::regclass, query
  FROM pg_stat_activity
 WHERE backend_type = 'autovacuum worker';

SELECT wal_records, wal_fpi, wal_bytes, wal_buffers_full, stats_reset
  FROM pg_stat_wal;   -- PG 14+