PostgreSQL의 통계 제공 view들

14~16 버전에서 추가된 view들에 대한 추가 필요

목록

---

• 데이터베이스 레벨에서 테이블 레벨, 인덱스 레벨 등으로 정보를 좁혀가며 확인하는 것이 좋습니다.
• 복제 관련 테이블(pg_stat_replication)을 모니터링하며 빠르게 복제 관련 이슈를 대응할 수 있습니다.
• 인덱스 생성이나 vacuum 등의 진행상황을 확인할 수 있는 view들이 추가되었으며 진행상황을 확인할 수 있습니다.
• 버전 별로 각 뷰들이 제공하는 정보들이 차이가 있을 수 있습니다.
• pg_stat_reset () 함수 호출로 현재 접속한 데이터베이스에 대한 통계를 초기화 할 수 있습니다.
  • 수퍼유저 권한 필요
  • **해당 함수 호출 이후에는 해당 데이터베이스 레벨 analyze를 권장합니다.**

뷰 이름설명지원 버전

pg_stat_all_tables	모든 테이블에 대한 통계 정보를 제공	11-13
pg_stat_user_tables	사용자 정의 테이블에 대한 통계 정보를 제공	11-13
pg_stat_xact_all_tables	현재 트랜잭션에서 모든 테이블에 대한 통계를 제공	11-13
pg_stat_xact_user_tables	현재 트랜잭션에서 사용자 정의 테이블에 대한 통계를 제공	11-13
pg_stat_all_indexes	모든 인덱스에 대한 통계를 제공	11-13
pg_stat_user_indexes	사용자 정의 인덱스에 대한 통계를 제공	11-13
pg_stat_xact_all_indexes	현재 트랜잭션에서 모든 인덱스에 대한 통계를 제공	11-13
pg_stat_xact_user_indexes	현재 트랜잭션에서 사용자 정의 인덱스에 대한 통계를 제공	11-13
pg_statio_all_tables	모든 테이블에 대한 디스크 I/O 통계를 제공	11-13
pg_statio_user_tables	사용자 정의 테이블에 대한 디스크 I/O 통계를 제공	11-13
pg_statio_all_indexes	모든 인덱스에 대한 디스크 I/O 통계를 제공	11-13
pg_statio_user_indexes	사용자 정의 인덱스에 대한 디스크 I/O 통계를 제공	11-13
pg_stat_activity	현재 연결된 각 백엔드의 활동과 상태를 제공	11-13
pg_stat_replication	현재 연결된 각 WAL 송신기의 상태를 제공	11-13
pg_stat_database	각 데이터베이스에 대한 통계를 제공	11-13
pg_stat_database_conflicts	각 데이터베이스에서 발생하는 컨플릭트에 대한 통계를 제공	11-13
pg_stat_progress_basebackup	백업 진행 상황에 대한 통계를 제공	12-13
pg_stat_progress_cluster	CLUSTER 및 VACUUM FULL 명령의 진행 상황을 보여줌	12-13
pg_stat_progress_create_index	CREATE INDEX 및 REINDEX 명령의 진행 상황을 보여줌	12-13
pg_stat_progress_analyze	ANALYZE 명령의 진행 상황을 보여줌	13

[PostgreSQL: Documentation: 11: 28.2. The Statistics Collector](https://www.postgresql.org/docs/11/monitoring-stats.html)

[PostgreSQL: Documentation: 12: 27.2. The Statistics Collector](https://www.postgresql.org/docs/12/monitoring-stats.html)

[PostgreSQL: Documentation: 13: 27.2. The Statistics Collector](https://www.postgresql.org/docs/13/monitoring-stats.html)

예시

---

• 데이터베이스 레벨 뷰
  • 해당 통계를 기반으로 temp사용량이 많은 데이터베이스를 확인, work_mem 파라미터 등의 조정을 고려할 수 있습니다. 다만 temp사용량은 temp table이나 unlogged 테이블 생성시에 사용되므로 확인 필요( log_temp_file 활성화를 통해 temp 사용하는 쿼리 확인 가능)

{
"select * from pg_stat_database where datname like 'm%'": [
	{
		"datid" : 16403,							### 데이터베이스 oid
		"datname" : "test_db",						### 데이터베이스명
		"numbackends" : 12,							### 백엔드 연결 수
		"xact_commit" : 4065007,					### commit된 트랜잭션 수
		"xact_rollback" : 6,						### 롤백된 트랜잭션 수 
		"blks_read" : 82335002,						### 디스크 블록 리드 수
		"blks_hit" : 2107484331,					### 버퍼 캐시 리드 수
		"tup_returned" : 5665571669,				### row 리턴 수
		"tup_fetched" : 685654924,					### row fetch 수
		"tup_inserted" : 29599104,					### row inserted 수
		"tup_updated" : 14807862,					### row update 수
		"tup_deleted" : 3853807,					### row delete 수
		"conflicts" : 0,							### 복제본과 충돌나서 쿼리 취소된 횟수
		"temp_files" : 16359,					    ### tempfile 생성된 회수
		"temp_bytes" : 85413165754,				    ### temp파일 발생량(byte)
		"deadlocks" : 0,						    ### 해당 DB에서 발생한 데드락 횟수
		"blk_read_time" : 1.289543325209E9,		    ### 블록 읽기에 소요된 시간
		"blk_write_time" : 0.0,                     ### 블록 쓰기에 소요된 시간
		"stats_reset" : "2023-05-24T08:22:48.903Z"  ### 마지막으로 통계가 초기화된 시점
	}
]}

• 테이블 통계 뷰
  • 해당 통계를 기반으로 analyze(테이블 데이터 통계정보수집), vacuum, autovacuum, autoanalyze 등에 대한 정보를 확인할 수 있습니다.
  • 현재 해당 테이블에 얼마만큼의 dead tuple이 존재하는지 live tuple이 존재하는지의 비율과 변경량 등을 확인 가능합니다.

{
"select * from pg_stat_user_tables": [
	{
		"relid" : 2017157389,
		"schemaname" : "public",
		"relname" : "test_temp",
		"seq_scan" : 2,
		"seq_tup_read" : 0,
		"idx_scan" : null,
		"idx_tup_fetch" : null,
		"n_tup_ins" : 0,
		"n_tup_upd" : 0,
		"n_tup_del" : 0,
		"n_tup_hot_upd" : 0,
		"n_live_tup" : 0,
		"n_dead_tup" : 0,
		"n_mod_since_analyze" : 0,
		"last_vacuum" : "2023-06-28T20:10:01.283Z",
		"last_autovacuum" : null,
		"last_analyze" : "2023-06-28T20:10:01.285Z",
		"last_autoanalyze" : null,
		"vacuum_count" : 3,
		"autovacuum_count" : 0,
		"analyze_count" : 4,
		"autoanalyze_count" : 0
	}
]}

• 인덱스 통계 뷰
  • 해당 통계를 기반으로 인덱스의 사용 빈도, 사용될 때 읽어 들이는 평균 row수 등을 확인 가능합니다
  • 이를 기반으로 사용되지 않는 인덱스, 인덱스에서 읽어 들이는 평균 row수 등을 확인 가능합니다.

{
"select * from pg_stat_user_indexes order by idx_scan desc": [
	{
		"relid" : 46389,
		"indexrelid" : 46419,
		"schemaname" : "public",
		"relname" : "test_temp",
		"indexrelname" : "idx_test_temp_01",
		"idx_scan" : 97664185023,
		"idx_tup_read" : 16665052192,
		"idx_tup_fetch" : 6487645140
	}
]}