PostgreSQL pg_stat_user_tables 실무 활용

소개

PostgreSQL 운영과 성능 관리에 있어 통계 뷰는 빠르게 상태를 파악하는 핵심 도구다. 그중 pg_stat_user_tables는 사용자 테이블 단위의 활동과 비용을 보여준다. 처음 접하는 사람도 이해하기 쉽도록 기본 개념부터 실무 쿼리, 해석 방법까지 차근히 설명한다. 또한 pg_stat_user_tables 사용 postgres 환경에서 어떻게 보조 지표로 활용할지 예시를 통해 제시한다.

pg_stat_user_tables란?

기본 개념

pg_stat_user_tables는 현재 데이터베이스의 사용자 테이블에 대한 통계를 제공하는 시스템 뷰다. 각 테이블에 대한 스캔 횟수, 튜플 업데이트·삭제·삽입 통계, 마지막 분석 시각 등을 포함한다. postgres 통계 뷰 활용 관점에서, 쿼리 성능 이상징후나 빈번한 VACUUM 필요성을 빠르게 탐지하는데 유용하다.

주요 컬럼

• relid: 테이블 식별자
• schemaname, relname: 스키마와 테이블 이름
• seq_scan, seq_tup_read: 순차 스캔 횟수와 읽은 튜플 수
• idx_scan, idx_tup_fetch: 인덱스 스캔 횟수와 인덱스에서 가져온 튜플 수
• n_tup_ins, n_tup_upd, n_tup_del: 삽입·업데이트·삭제된 튜플 수
• n_live_tup, n_dead_tup: 살아있는 튜플과 죽은 튜플 수
• last_autovacuum, last_autoanalyze: 자동 정리 및 분석의 마지막 수행 시각

실무에서 유용한 쿼리 예

아래 쿼리를 통해 빈번한 VACUUM이 필요한 테이블, 높은 seq_scan 비중, 또는 인덱스 사용 비율을 확인할 수 있다.

1) 테이블별 기본 통계

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

2) 인덱스 사용 비율 확인

SELECT schemaname, relname,
       CASE WHEN seq_scan + idx_scan = 0 THEN 0
            ELSE round((idx_scan::numeric / (seq_scan + idx_scan)) * 100, 2)
       END AS idx_usage_percent
FROM pg_stat_user_tables
ORDER BY idx_usage_percent ASC
LIMIT 50;

3) 최근 활동 없는 큰 테이블 찾기

SELECT schemaname, relname, pg_relation_size(relid) AS size_bytes,
       n_live_tup, n_dead_tup, last_autoanalyze, last_autovacuum
FROM pg_stat_user_tables
WHERE pg_relation_size(relid) > 1024*1024*50
ORDER BY last_autoanalyze NULLS FIRST
LIMIT 50;

통계 해석 방법

몇 가지 핵심 포인트로 해석을 단순화한다. 먼저 n_dead_tup가 높은 테이블은 VACUUM이 필요할 가능성이 크다. 인덱스 사용 비율(idx_usage_percent)이 낮으면 인덱스 설계 재검토 또는 쿼리 리팩터링이 필요하다. seq_scan이 빈번하면 인덱스 힌트나 쿼리 조건 변경을 고려한다. 마지막으로 마지막 자동 정리 시각이 오래된 테이블은 분석(analyze) 부재로 인해 잘못된 통계에 기반한 비효율적 플랜 생성 위험이 있다.

모니터링과 알림 구성

모니터링 항목 제안

• n_dead_tup 비율 임계치 설정
• idx_usage_percent가 낮은 상위 테이블 감시
• last_autovacuum/last_autoanalyze의 휴면 시간 경고
• 빠른 증가를 보이는 seq_scan 또는 idx_scan 변화 추적

간단한 알림 예

예를 들어 n_dead_tup가 n_live_tup의 30%를 초과하면 알림을 발송하도록 설정한다. 모니터링 도구에 따라 쿼리를 등록하고 임계치 기반 경고를 구성하면 된다.

운영 중 취할 수 있는 조치

• VACUUM / VACUUM FULL: n_dead_tup 정리와 디스크 공간 회수
• ANALYZE: 최신 통계 갱신으로 쿼리 플랜 개선
• 인덱스 재설계 또는 추가: 낮은 idx_usage_percent 대응
• 쿼리 튜닝: 불필요한 seq_scan을 유발하는 조건 제거

주의사항

pg_stat_user_tables는 누적 통계다. 리셋되거나 재시작 시 값이 초기화될 수 있다. 또한 통계는 샘플 기반이라 완전한 진실을 보장하지 않는다. 따라서 장기적 추세와 즉시성 지표를 함께 사용하는 것이 안전하다.

결론

pg_stat_user_tables 사용 postgres 환경에서 빠른 문제 식별과 우선순위 결정을 돕는 실용적 뷰다. postgres 통계 뷰 활용을 통해 빈번한 VACUUM 필요성, 인덱스 활용도 부족, 데이터베이스 활동 패턴을 파악할 수 있다. 모니터링 체계에 정기 쿼리를 포함하고 임계치를 정하면 운영 안정성 향상에 큰 도움이 된다.