[PostgreSQL] 관리 및 성능 모니터링 SQL

세션 관련 SQL

1. 세션 수

select count(*) from pg_stat_activity ;

1. Active 세션 정보

SELECT datname, usename, state, query
FROM pg_stat_activity
WHERE state = 'active';

1. idle 세션 정보

SELECT datname, usename, state, query
FROM pg_stat_activity
WHERE state = 'idle';

1. 특정 사용자 세션 조회

SELECT datname, usename, state, query
FROM pg_stat_activity
WHERE username = '';

1. 현재 실행 중인 쿼리 중단

SELECT pg_cancel_backend(PID);

1. 현재 세션을 강제 종료

SELECT pg_terminate_backend(PID);

1. 최근 1일 동안 접속한 세션 정보 조회

SELECT
    pid,                           -- 프로세스 ID
    usename,                       -- 사용자 이름
    client_addr,                   -- 클라이언트의 IP 주소
    client_port,                   -- 클라이언트의 포트 번호
    backend_start,                 -- 백엔드 프로세스 시작 시간
    state,                         -- 세션 상태 (예: active, idle 등)
    query                          -- 현재 실행 중인 쿼리
FROM
    pg_stat_activity               -- PostgreSQL의 시스템 뷰
WHERE
    backend_start >= current_timestamp - INTERVAL '1 day'
   				   -- 최근 1일 동안의 세션만 필터링
ORDER BY
    backend_start DESC;            -- 세션 시작 시간 기준 내림차순으로 정렬

---

성능지표

1. 1분 이상 실행되는 쿼리

SELECT
    current_timestamp - query_start AS runtime,
    datname, usename, query
FROM pg_stat_activity
WHERE state = 'active'
      AND current_timestamp - query_start > '1 min'
ORDER BY 1 DESC;

1. LONG 실행 쿼리 확인

SELECT current_timestamp - query_start AS runtime,datname, usename, query
FROM pg_stat_activity
WHERE state = 'active' ORDER BY 1 DESC;

1. 자주 호출되는 SQL 분석

SELECT query FROM pg_stat_statements ORDER BY calls DESC;

1. 캐시 적중률

SELECT
  d.datname,
  u.usename,
  round(100.*sd.blks_hit/(sd.blks_read+sd.blks_hit), 2) AS dhitratio
FROM pg_stat_database sd
JOIN pg_database d ON (d.oid=sd.datid)
JOIN pg_user u ON (u.usesysid=d.datdba)
WHERE sd.blks_read+sd.blks_hit<>0;

1. 커밋 적중률

SELECT
  d.datname,
  u.usename,
  round(100.*sd.xact_commit/(sd.xact_commit+sd.xact_rollback), 2) AS dcommitratio
FROM pg_stat_database sd
JOIN pg_database d ON (d.oid=sd.datid)
JOIN pg_user u ON (u.usesysid=d.datdba)
WHERE sd.xact_commit+sd.xact_rollback<>0;

1. LOCK TABLE 조회

select locktype, relation::regclass, mode, transactionid tid, pid, granted
from pg_catalog.pg_locks
where not pid = pg_backend_pid() and relation::regclass::text not like 'pg%';

1. 서버 상태 정보

SELECT
    buffers_alloc, -- 할당된 버퍼 수 
    maxwritten_clean, -- 백그라운드 프로세스가 디스크에 기록한 최대 버퍼 수
    buffers_backend, -- 백엔드 프로세스에서 사용한 버퍼 수
    buffers_alloc / (SELECT count(*) FROM pg_stat_bgwriter) AS buffer_ratio
FROM
    pg_stat_bgwriter;

1. 디스크 I/O 성능 분석

SELECT
    relname,
    heap_blks_read,
    heap_blks_hit,
    idx_blks_read,
    idx_blks_hit,
    toast_blks_read,
    toast_blks_hit
FROM
    pg_statio_user_tables
ORDER BY
    heap_blks_read DESC;

---

테이블 오브젝트

1. db 별 사용량 확인

SELECT pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) AS size FROM pg_database;

1. 스키마별 사용량 확인

SELECT schema_name, pg_size_pretty(sum(table_size)::bigint) as "disk space", 
        (sum(table_size) / pg_database_size(current_database())) * 100 as "percent" 
        FROM (SELECT pg_catalog.pg_namespace.nspname as schema_name, pg_relation_size(pg_catalog.pg_class.oid) as table_size 
        FROM pg_catalog.pg_class JOIN pg_catalog.pg_namespace ON relnamespace = pg_catalog.pg_namespace.oid ) t 
        GROUP BY schema_name ORDER BY schema_name;

1. 스키마별 테이블 사용량 확인

SELECT schemaname, tablename, pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) FROM pg_tables where schemaname not in ('information_schema', 'pg_catalog');

1. 스키마별 row 건 수

SELECT 'SELECT ''' || schemaname || '.' || tablename || ''' as tablename, count(*) FROM ' || schemaname || '.' || tablename|| ' union all' FROM pg_tables where schemaname not in ('information_schema', 'pg_catalog');

1. 인덱스 건 수

SELECT * FROM pg_indexes WHERE schemaname not in ('pg_catalog');

1. 인덱스 통계 조회

SELECT
    relname AS table_name,
    indexrelname AS index_name,
    idx_scan AS number_of_scans, -- 스캔 횟수
    idx_tup_read AS tuples_read, -- 읽은 튜플 수
    idx_tup_fetch AS tuples_fetched -- 가져온 튜플 수
FROM
    pg_stat_user_indexes
JOIN
    pg_index ON pg_stat_user_indexes.indexrelid = pg_index.indexrelid
WHERE
    pg_stat_user_indexes.schemaname = 'public'  -- 원하는 스키마로 변경 가능
ORDER BY
    number_of_scans DESC;

1. 스키마별 PK,FK 건 수

select constraint_schema, constraint_type , count(*)
        from information_schema.table_constraints 
        where constraint_schema in('experdba')
        and constraint_type in ('FOREIGN KEY','PRIMARY KEY')
        group by constraint_schema,constraint_type 
        ORDER BY constraint_schema, constraint_type ;

1. VIEW 조회

SELECT
    schemaname,
    viewname,
    definition
FROM
    pg_views
WHERE
    schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY
    schemaname, viewname;

1. 시퀀스 조회

select * from pg_catalog.pg_sequences;

1. 테이블 스페이스 조회

select * from pg_catalog.pg_tablespace ;

1. role 조회

select * from pg_catalog.pg_roles where rolname not like 'pg_%';

1. user 별 권한 조회

SELECT
    usename,
    usecreatedb,
    usesuper,
    userepl,
    usebypassrls
FROM
    pg_user;

1. user 별 테이블 권한 조회

SELECT
    grantee,
    table_name,
    privilege_type
FROM
    information_schema.role_table_grants
WHERE
    table_name = '';

기타

1. DBMS 가동시간

select pg_postmaster_start_time();

1. DB 포트

show port;

1. DB 버전

select version();

1. 데이터 경로

show data_directory;

1. 로그 경로

show log_directory;

1. 사용가능한 extension 확인

select * from pg_available_extensions ;

1. 현재 시간

select now();

1. 현재 WAL 로그 파일(LSN) 확인

select pg_current_wal_lsn();

1. 아카이브 상태 확인

select *  from pg_stat_archiver ;

1. vacuum 로그 확인

SELECT relname, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze FROM pg_stat_user_tab