튜플 정보 확인 (feat. Vacuum)

PostgreSQL에는 Vacuum이라는 개념이 존재한다. PostgreSQL에선 Vacuum을 잘 이해하고 적절히 관리하는 게 중요하고 회사에서도 주기적으로 버큠작업을 해주고 있다.

• 버큠 관련 내용은 우형블로그 참고 (https://techblog.woowahan.com/9478/)

튜플 정보 확인 (live tuple / dead tuple 확인)

SELECT
	n.nspname AS schenma_name
	, c.relname AS table_name
	,  pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid) AS total_tuple
	, pg_stat_get_live_tuples(c.oid) AS live_tuple
	, pg_stat_get_dead_tuples(c.oid) AS dead_tupple
	, round(100*pg_stat_get_live_tuples(c.oid) / (pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid)),2) AS live_tuple_rate
	, round(100*pg_stat_get_dead_tuples(c.oid) / (pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid)),2) AS dead_tuple_rate    
	, pg_size_pretty(pg_total_relation_size(c.oid)) AS total_relation_size
	, pg_size_pretty(pg_relation_size(c.oid)) AS relation_size
FROM 
	pg_class AS c
	JOIN pg_catalog.pg_namespace AS n 
	ON n.oid = c.relnamespace
WHERE 
	pg_stat_get_live_tuples(c.oid) > 0
	AND c.relname NOT LIKE 'pg_%'
ORDER BY dead_tupple DESC
;

버큠 통계 정보 확인 쿼리, VACUUM ANALUZE 명령어 실행 시 갱신

• relid : 테이블의 oid
• schemaname / relname : 이 테이블이 있는 스키마의 이름 / 이 테이블의 이름
• seq_scan : 이 테이블에서 시작된 순차 스캔 수
• seq_tup_read : 순차 스캔에서 가져온 라이브 행 수
• idx_scan : 이 테이블에서 시작된 인덱스 스캔수
• idx_tup_fetch : 인덱스 스캔으로 가져온 라이브 행수
• n_tup_ins / n_tup_upd / n_tup_del : 삽입된 행 수 / 업데이트 된 행 수 / 삭제된 행 수
• n_tup_hot_upd : HOT(heap only tuple) 업데이트 된 행 수 (별도의 인덱스 업데이트 필요 X)
• n_live_tup / n_dead_tup : 예상 라이브 행 수 / 죽은 행의 예상 수
• last_vacuum / last_autovacuum : 이 테이블을 수동으로 제거한 마지막 시간 (vacuum full은 계산하지 않음) / autovacuum 데몬이 이 테이블을 마지막으로 정리한 시간
• last_analyze/last_autoanalyze: 수동 분석 마지막 시간 / autovacuum 데몬이 테이블을 마지막으로 분석한 시간
• vacuum_count : 이 테이블이 수동으로 제거된 횟수 (vacuum full 계산 x)
• analyze_count / autoanalyze_count : 수동 분석된 횟수 / autovacuum 데몬이 테이블 분석한 횟수

Vacuum 통계 정보 확인

SELECT * FROM pg_stat_all_tables ORDER BY schemaname, relname
;

물리적 파일 위치 찾는 쿼리

Vacuum FULL 실행시 pg_class의 relfilenode 값이 변경되므로, 아래 쿼리로 relfilenode의 물리적인 파일 위치를 확인.

• relfilenode : 해당 값은 디스크에 저장되는 파일명과 동일.

SELECT 
	oid
    , pg_relation_filepath(oid) 
    , relname 
    , relfilenode 
FROM pg_class LIMIT 10
;

현재 실행중인 Vacuum세션 정보를 확인

SELECT
	datname
	, usename
 	, pid
 	, CURRENT_TIMESTAMP -  xact_start AS xact_runtime
	, query
FROM
	pg_stat_activity
WHERE
 	upper(query) LIKE '%VACUUM%'
ORDER BY xact_start
;