처음 사용자를 위한 PostgreSQL 사용 샘플 - 8. 메타정보 조회

"처음 사용자를 위한 PostgreSQL 사용 샘플"이라는 제목의 시리즈로, 저처럼 PostgreSQL을 처음 접하는 사용자를 위한 기본적인 개념, 사용 샘플을 설명드립니다.

이전글

[Database/PostgreSQL] - 처음 사용자를 위한 PostgreSQL 사용 샘플 - 7. HammerDB를 이용한 부하테스트

 

아래 자료를 참조했습니다.

PostgreSQL Domcumentation
The Internals of PostgreSQL
PostgreSQL Tutorial

---

PostgreSQL에서 데이터베이스의 메타 정보 및 활동 내용 크게 System Catalog, Information Schema, Statistics Collector를 통해 이루어집니다. 이번 글에서는 PostgreSQL의 메타정보 관리에 대해 살펴보겠습니다.  

 

참고 자료

A Stroll Through The PostgreSQL Catalog

Chapter 37. The Information Schema
28.2. The Statistics Collector

 

System Catalog

System Catalog는 테이블, 칼럼 정보 같은 스키마 메타데이터와 파라미터, 락(lock) 등의 내부 설정 및 오퍼레이션 정보를 담고 있는 영역입니다. PostgreSQL의 System Catalog는 테이블 형태로 되어 있으며 각 데이터베이스의 "pg_catalog" 스키마가 소유하고 있습니다. System Catalog에 대한 접근은 테이블을 직접 조회하거나, "information_schema" 스키마 소유 뷰를 통해서 할 수 있습니다. Chapter 52. System Catalogs 에서 전체 System Catalog 목록을 확인할 수 있습니다.

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

몇가지 사용 샘플입니다. 먼저 System Catalog 스키마의 데이터베이스 오브젝트 갯수와 사이즈 조회입니다. 여기서 "r"은 relation(일반 테이블), "v"는 뷰, "i"는 인덱스를 의미합니다.

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)

postgres=# SELECT pg_size_pretty(sum(pg_total_relation_size(c.oid)))
postgres-# FROM pg_catalog.pg_class c, pg_catalog.pg_namespace n
postgres-# WHERE n.nspname = 'pg_catalog'
postgres-# AND c.relnamespace = n.oid;
 pg_size_pretty
----------------
 12 MB
(1 row)

 

같은 정보지만 System Catalog 스키마의 테이블에서 조회한 정보와 뷰에서 조회한 정보입니다. 차이점은 뷰의 경우 읽을 수 있는 형태의 칼럼명과 데이터를 제공합니다.

postgres=# SELECT schemaname, tablename, tableowner
postgres-# FROM pg_catalog.pg_tables -- From a catalog view
postgres-# WHERE schemaname = 'pg_catalog'
postgres-# AND tablename = 'pg_class';
 schemaname | tablename | tableowner
------------+-----------+------------
 pg_catalog | pg_class  | postgres
(1 row)

postgres=# SELECT c.relnamespace, c.relname, c.relowner
postgres-# FROM pg_catalog.pg_class c, -- From a catalog table
postgres-# pg_catalog.pg_namespace n
postgres-# WHERE n.nspname = 'pg_catalog'
postgres-# AND c.relname = 'pg_class'
postgres-# AND n.oid = c.relnamespace;
 relnamespace | relname  | relowner
--------------+----------+----------
           11 | pg_class |       10
(1 row)

 

참고로, psql 접속 시 "-E" 옵션으로 접속한 후, "\d" 명령으로 데이터베이스 오브젝트의 메타 정보를 조회할 경우, "\d"가 만들어 내는 실제 내부 쿼리를 보여줍니다.

...

 

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"처럼 통계 수집의 결과를 보여주는 뷰가 있습니다. 관련 전체 목록은 28.2. The Statistics Collector 에서 확인할 수 있습니다. 

 

간단한 테스트를 통해 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 및 Anlayze 정보가 업데이트되는 것을 알 수 있습니다.

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

 

<끝>