PostgreSQL 에서 데이터베이스의 메타 정보 및 활동 내용은 다음을 통해 이루어집니다.

• System Catalog
• Information Schema
• Statistics Collector

System Catalog

System Catalog는 테이블 형태로 되어있고, 각 데이터베이스의 "pg_catalog" 스키마가 소유하고 있습니다. System Catalog에 대한 접근은 테이블을 직접 조회하거나, "Information_schema" 스키마 소유 뷰를 통해서 할 수 있습니다.

• System Catalog 테이블들의 정보는 pg_catalog.pg_class 테이블에 담겨있습니다.
• System Catalog는 신기능 추가 등의 이유로 데이터베이스 버전에 따라 다를 수 있습니다.

사용 샘플을 첨부합니다.

postgres=# select * from pg_calss limit 1 ;

postgres=# SELECT c.relkind, count(c.*)
postgres-# FROM pg_catalog.pg_class c, pg_catalog.pg_namespace n
postgres-# WHERE n.nspname = 'pg_catalog'
postgres-# AND c.relnamespace = n.oid
postgres-# GROUP BY c.relkind;
 relkind | count
---------+-------
 r       |    62
 v       |    72
 i       |   117
(3 rows)

# r - Relation(일반테이블)
# v - View
# i - Index

Information Schema

Information Schema는 현재 접속한 데이터베이스의 오브젝트에 대한 정보를 담고 있는 뷰들로 이루어져 있습니다. Information Schema은 SQL 표준으로 정의되어있기 때문에 System Catalog와는 달리 PostgreSQL만의 특징적인 기능과 관련한 정보를 제공하지 않습니다.

샘플 테스트를 통해 System Catalog 와 Information Schema가 보여주는 Constraint 정보의 차이를 보여줍니다.

postgres=# CREATE TABLE public.test (testid SERIAL PRIMARY KEY);
CREATE TABLE
postgres=# SELECT con.connamespace::regnamespace,
postgres-#        con.conname,
postgres-#        c.relnamespace::regnamespace,
postgres-#        con.conrelid::regclass,
postgres-#        con.contype
postgres-# FROM pg_constraint con, pg_class c
postgres-# WHERE con.conrelid = 'public.test'::regclass
postgres-# AND c.oid = con.conrelid;
 connamespace |  conname  | relnamespace | conrelid | contype
--------------+-----------+--------------+----------+---------
 public       | test_pkey | public       | test     | p
(1 row)

postgres=# SELECT constraint_schema,
postgres-#        constraint_name,
postgres-#        table_schema,
postgres-#        table_name,
postgres-#        constraint_type
postgres-# FROM information_schema.table_constraints
postgres-# WHERE table_schema = 'public' AND table_name = 'test';
 constraint_schema |    constraint_name    | table_schema | table_name | constraint_type
-------------------+-----------------------+--------------+------------+-----------------
 public            | test_pkey             | public       | test       | PRIMARY KEY
 public            | 2200_17942_1_not_null | public       | test       | CHECK
(2 rows)

Statistics Collector

Statistics Collector는 서버 활동에 대한 정보를 수집해서 보여주는 역할을 합니다. 테이블과 인덱스에 대한 접근 횟수를 디스크 블록 단위와 로우 단위로 수집합니다. 각 테이블의 전체 로우 건수와 각 테이블의 Vacuum 및 Analyze 정보를 추적하며, 사용자 정의 함수에 대한 호출 횟수와 전체 소요 시간도 수집합니다. 또한 서버 프로세스에 의해 실행 중인 명령어와 같은 현재 시스템에서 일어나고 있는 동적인 정보를 리포팅합니다.

System Collector 관련 파라미터는 "postgresql.conf" 파일에서 설정하여 전체 서버 레벨에서 적용할 수도 있고, "SET" 명령으로 세션 레벨에서 제어할 수도 있습니다. 설정할 수 있는 정보는 다음과 같습니다.

• track_activities: 서버 프로세스가 실행하고 있는 명령에 대한 모니터링 여부 설정
• track_count: 테이블과 인덱스에 대해 통계정보를 수집할 지 여부 설정
• track_functions: 사용자 정의 함수 사용에 대한 추적 설정
• track_io_timing: 블록 읽기, 쓰기에 대한 모니터링 여부 설정
• track_wal_io_timing: WAL 쓰기 횟수에 대한 모니터링 여부 설정

System Collector에 의해 수집된 정보를 보여주는 뷰(View)는 두가지가 있습니다. "pg_stat_activity"와 같이 시스템의 현재 상태를 보여주는 뷰와 "pg_stat_database"처럼 통계 수집의 결과를 보여주는 뷰가 있습니다.

간단한 테스트를 통해 System Collector 가 수집하는 정보 중 하나를 살펴보겠습니다. "pg_stat_user_tables"를 통해 본 앞서 만든 테스트용 테이블의 통계정보는 현재 아래와 같습니다.

postgres=# SELECT * FROM pg_stat_user_tables
postgres-# WHERE relid = 'public.test'::regclass;
-[ RECORD 1 ]-------+-------
relid               | 17942
schemaname          | public
relname             | test
seq_scan            | 3
seq_tup_read        | 0
idx_scan            | 0
idx_tup_fetch       | 0
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
n_ins_since_vacuum  | 0
last_vacuum         |
last_autovacuum     |
last_analyze        |
last_autoanalyze    |
vacuum_count        | 0
autovacuum_count    | 0
analyze_count       | 0
autoanalyze_count   | 0

위 테이블에 Insert와 Update를 한건씩 발생시키면 통계정보가 변경되는 것을 알 수 있습니다.

postgres=# INSERT INTO public.test VALUES (1);
INSERT 0 1
postgres=# UPDATE public.test SET testid = 2;
UPDATE 1
postgres=# SELECT * FROM public.test;
-[ RECORD 1 ]
testid | 2

postgres=# SELECT * FROM pg_stat_user_tables
postgres-# WHERE relid = 'public.test'::regclass;
-[ RECORD 1 ]-------+-------
relid               | 17942
schemaname          | public
relname             | test
seq_scan            | 6
seq_tup_read        | 2
idx_scan            | 0
idx_tup_fetch       | 0
n_tup_ins           | 1
n_tup_upd           | 1
n_tup_del           | 0
n_tup_hot_upd       | 0
n_live_tup          | 1
n_dead_tup          | 1
n_mod_since_analyze | 2
n_ins_since_vacuum  | 1
last_vacuum         |
last_autovacuum     |
last_analyze        |
last_autoanalyze    |
vacuum_count        | 0
autovacuum_count    | 0
analyze_count       | 0
autoanalyze_count   | 0

Vacuum 명령을 실행하면 deal tuple이 정리되고, Vacuum 및 Analyze 정보가 업데이트되는 것을 알 수 있습니다.

postgres=# VACUUM ANALYZE public.test;
VACUUM
postgres=# SELECT * FROM pg_stat_user_tables
postgres-# WHERE relid = 'public.test'::regclass;
-[ RECORD 1 ]-------+------------------------------
relid               | 17942
schemaname          | public
relname             | test
seq_scan            | 6
seq_tup_read        | 2
idx_scan            | 0
idx_tup_fetch       | 0
n_tup_ins           | 1
n_tup_upd           | 1
n_tup_del           | 0
n_tup_hot_upd       | 0
n_live_tup          | 1
n_dead_tup          | 0
n_mod_since_analyze | 0
n_ins_since_vacuum  | 0
last_vacuum         | 2021-11-29 05:37:29.624664+00
last_autovacuum     |
last_analyze        | 2021-11-29 05:37:29.626912+00
last_autoanalyze    |
vacuum_count        | 1
autovacuum_count    | 0
analyze_count       | 1
autoanalyze_count   | 0