10.4 통계 뷰

SELECT pid, backend_type, state, wait_event_type, wait_event,
       xact_start, query_start, query
  FROM pg_stat_activity
 WHERE state = 'active'
 ORDER BY query_start;

SELECT datname,
       numbackends, xact_commit, xact_rollback,
       blks_read, blks_hit,
       round(100.0 * blks_hit / nullif(blks_hit + blks_read, 0), 1) AS cache_hit_pct,
       tup_returned, tup_fetched,
       tup_inserted, tup_updated, tup_deleted,
       conflicts, deadlocks, temp_files, temp_bytes,
       stats_reset
  FROM pg_stat_database
 WHERE datname IS NOT NULL;

SELECT schemaname, relname,
       n_live_tup, n_dead_tup,
       n_tup_ins, n_tup_upd, n_tup_del,
       n_tup_hot_upd,
       seq_scan, idx_scan,
       last_vacuum, last_autovacuum,
       last_analyze, last_autoanalyze
  FROM pg_stat_user_tables
 ORDER BY n_tup_upd DESC
 LIMIT 10;

SELECT relname AS table, indexrelname AS index,
       idx_scan, idx_tup_read, idx_tup_fetch,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
  FROM pg_stat_user_indexes
 ORDER BY idx_scan;

SELECT wal_records, wal_fpi, wal_bytes,
       wal_buffers_full, wal_write, wal_sync,
       wal_write_time, wal_sync_time, stats_reset
  FROM pg_stat_wal;

SELECT * FROM pg_stat_bgwriter;
SELECT * FROM pg_stat_checkpointer;   -- PG 17+

SELECT backend_type, context, object, reads, writes, evictions, extends
  FROM pg_stat_io
 ORDER BY reads + writes DESC;

SELECT slot_name, slot_type, active, restart_lsn, confirmed_flush_lsn,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS lag,
       wal_status
  FROM pg_replication_slots;

SELECT pid, datname, phase,
       heap_blks_scanned, heap_blks_total,
       round(100.0 * heap_blks_scanned / nullif(heap_blks_total, 0), 1) AS pct
  FROM pg_stat_progress_vacuum;

SELECT pg_stat_reset();                       -- 현재 DB 모든 통계
SELECT pg_stat_reset_shared('bgwriter');      -- 특정 영역만
SELECT pg_stat_reset_single_table_counters('orders'::regclass::oid);

-- TOP 5 슬로우 쿼리 (pg_stat_statements 필요)
SELECT mean_exec_time, calls, total_exec_time, LEFT(query, 100) FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 5;

-- 오래된 idle in transaction
SELECT pid, usename, datname, state, now() - xact_start AS age, query FROM pg_stat_activity WHERE state LIKE 'idle in transaction%' AND xact_start < now() - interval '5 min';

-- 락 대기
SELECT pid, pg_blocking_pids(pid), query FROM pg_stat_activity WHERE cardinality(pg_blocking_pids(pid)) > 0;

-- replication lag (primary에서)
SELECT application_name, state, write_lag, flush_lag, replay_lag FROM pg_stat_replication;