DBA를 위한 PostgreSQL 17 시스템 카탈로그 업데이트

작성자: 권기순 수석_DB R&D센터-Core OSS R&D팀

 

 

PostgreSQL(포스트그레SQL) 17 beta Release를 통해 우리는 아래 주된 기능들이 강화되었다는 것을 확인할 수 있다.

1. VACUUM 성능 강화
2. 인덱스 알고리즘 강화를 통한 쿼리 최적화
3. Logical Replication 기능 강화
4. JSON

이러한 업데이트 과정에서, 데이터베이스의 메타데이터를 저장하고 관리하는 데 사용되는 시스템 카탈로그(pg_catalog)도 일부 업데이트 되었다.

이 글에서는 PG 17의 업데이트된 시스템 카탈로그에 대해 알아보고, PG16과 비교했을때 어떤 변화가 있는지, DBA입장에서 활용할 수 있는 것들이 무엇이 있는지 알아보도록 한다.

What is System Catalog(pg_catalog)?

그 전에 우리는 시스템 카탈로그가 무엇인지 좀 더 자세히 알고 넘어가보도록 하자.

Postgres 17 문서에는 pg_catalog에 대하여 다음과 같이 설명하고 있다.

The system catalogs are the place where a relational database management system stores schema metadata, such as information about tables and columns, and internal bookkeeping information.  시스템 카탈로그는 관계형 데이터베이스 관리 시스템이 표 및 열에 대한 정보, 내부 부기 정보 등 스키마 메타데이터를 저장하는 곳이다.

결론부터 말하자면, 시스템 카탈로그는 데이터베이스 내부 정보를 여러 형태로 저장되는 곳이며, 다양한 DBMS 태스크를 관리하고 수행하는 데 활용 되지만, 일반적으로 사용자가 직접 조작하는 것은 바람직하지 않다는 것이 핵심 내용이다.

 

시스템 카탈로그의 특징

1. PostgreSQL의 시스템 카탈로그는 일반 테이블로 구성되어 있다.
2. 직접 시스템 카탈로그를 변경(테이블을 삭제, 생성, 열 추가, 값 삽입/수정 등)하는 작업을 한다면, 시스템을 엉망으로 만들 수 있기 때문에 일반적으로 사용자가 직접 시스템 카탈로그를 변경해서는 안된다.
3. 직접 시스템 카탈로그를 변경하는 대신 SQL 명령어를 사용해야 한다. 예를 들어 CREATE DATABASE 명령어는 pg_database 카탈로그에 행을 삽입하고 실제 디스크에 데이터베이스를 생성한다. 일부 특수한 경우에는 시스템 카탈로그를 직접 조작해야 할 수 있지만, 점점 더 많은 작업들이 SQL 명령어로 제공되면서 이러한 필요성은 점점 감소하고 있다.

시스템 카탈로그의 활용

1. PostgreSQL 서버에서 쿼리 최적화, 액세스 제어 및 개체 확인에 사용 가능하다.
2. 사용자(application 사용자 또는 특정 tool)가 메타데이터를 조회하여 데이터베이스의 구조를 이해할 수 있다.
3. 유지관리 및 관리: 인덱싱, 권한 및 스키마 변경과 같은 데이터베이스 유지관리 작업에 필요한 정보를 제공한다.

1. 최적화/튜닝: 데이터가 저장되고 액세스되는 방식에 대한 통찰력을 제공하여 성능 튜닝을 돕는다.
2. 보안 감사: 역할과 권한을 검토하여 적절한 액세스 제어를 보장한다.

pg_catalog changes in PG 17

이제 본격적으로, PG 17에서는 어떤 카탈로그들이 어떻게 변화되었는지 알아보도록 하자.

1. Optimizer 영역

pg_stats

PostgreSQL 6.x부터 pg_statistic이라는 시스템 카탈로그로 시작한 pg_stats는 테이블 및 인덱스의 통계 정보(데이터 분포, 유니크 값 개수, 상관관계 등)를 저장하는 시스템 카탈로그이다. pg_stats는 PostgreSQL의 쿼리 옵티마이저가 쿼리 계획을 수립할 때 사용된다.

PG17 업데이트로 pg_stats에  범위 유형(range-type) 히스토그램 정보를 보고하기 위한 칼럼들이 새로 추가되었다.

postgres=# \d pg_catalog.pg_stats;                     View "pg_catalog.pg_stats"         Column         |   Type   | Collation | Nullable | Default ------------------------+----------+-----------+----------+--------- schemaname             | name     |           |          | tablename              | name     |           |          | attname                | name     |           |          | inherited              | boolean  |           |          | null_frac              | real     |           |          | avg_width              | integer  |           |          | n_distinct             | real     |           |          | most_common_vals       | anyarray |           |          | most_common_freqs      | real[]   |           |          | histogram_bounds       | anyarray |           |          | correlation            | real     |           |          | most_common_elems      | anyarray |           |          | most_common_elem_freqs | real[]   |           |          | elem_count_histogram   | real[]   |           |          | range_length_histogram | anyarray |           |          | range_empty_frac       | real     |           |          | range_bounds_histogram | anyarray |           |          |

칼럼	자료형	설명
range_length_histogram	anyarray	범위 유형 열의 비어 있지 않은 범위 값과 비어 있지 않은 범위 값의 길이를 나타내는 히스토그램. (범위 유형이 아닌 경우 Null) 범위 경계의 포함 여부에 관계없이 subtype_diffrange 함수를 사용하여 계산된다.
range_empty_frac	real	값이 빈 범위인 열 항목의 비율(범위가 아닌 유형의 경우 Null)
range_bounds_histogram	anyarray	비어 있지 않은 범위 값과 널이 아닌 범위 값의 하한과 상한을 나타내는 히스토그램(범위가 아닌 유형의 경우 Null) 이 두 히스토그램은 범위들의 단일 배열로 표현되며, 그 범위들의 하한은 하한의 히스토그램을 나타내고, 상한은 상한의 히스토그램을 나타낸다.

 

아래 예제를 통해 새로 추가된 칼럼을 활용해 볼 수 있다.

postgres=# -- 테이블 생성 postgres=# CREATE TABLE test(r int4range); CREATE TABLE postgres=# -- 1부터 10,000까지의 숫자를 생성하고, 각 숫자에 대해 int4range 타입의 랜덤 범위 값을 생성하여 test 테이블에 삽입 postgres=# INSERT INTO test postgres-#      SELECT int4range((random()*10)::integer,(10+random()*10)::integer) postgres-#      FROM generate_series(1,10000); INSERT 0 10000 postgres=# SET default_statistics_target = 10; -- 통계 정보 수집 대상 수를 10으로 설정 SET postgres=# -- test 테이블에 대한 통계 정보를 수집 postgres=# ANALYZE test; ANALYZE postgres=# -- pg_stats 시스템 카탈로그에서 test 테이블의 통계 정보를 조회 postgres=# SELECT range_length_histogram, range_empty_frac,                                                                                                                                                                                             range_bounds_histogram                                                                                                                                                                                                                                  FROM pg_stats                                                                                                                                                                                                                                           WHERE tablename = 'test' \gx -[ RECORD 1 ]----------+------------------------------------------------------------------------------------------------------ range_length_histogram | {1,5,6,8,9,10,11,12,14,15,20} range_empty_frac       | 0.0026666666 range_bounds_histogram | {"[0,10)","[1,11)","[2,12)","[3,13)","[4,14)","[5,15)","[6,16)","[7,17)","[8,18)","[9,19)","[10,20)"}

 

2. Migration 영역

pg_stat_bgwriter

PostgreSQL은 데이터 무결성을 유지하기 위해 모든 데이터 변경 사항을 먼저 WAL(Write-Ahead Log)에 기록한다. background writer 프로세스는 이 WAL 레코드를 주기적으로 실제 데이터 파일에 기록하는 역할을 하며, 이를 통해 데이터베이스 시스템의 안정성과 성능을 향상시킬 수 있다.

 

PostgreSQL 8.3부터 도입된 pg_stat_bgwriter 뷰는 background writer 프로세스의 활동을 모니터링하여  background writer 프로세스의 다양한 활동 데이터를 확인 할 수 있게 되었고, 데이터베이스 성능과 안정성을 이해하는데 도움을 주기 시작하였다. 예를 들어, background writer 프로세스가 지연되거나 과도한 I/O 활동을 하는 경우 pg_stat_bgwriter를 통해 성능 문제의 원인을 파악할 수 있다.

이번 PG 17의 업데이트를 통해 pg_stat_bgwriter의 buffers_backend 칼럼과 buffers_backend_fsync 칼럼은 pg_stat_io의 특정 칼럼들과 유사하게 중복되어 제거되었다.

PG16의 pg_catalog.pg_stat_io뷰를 보면 다음과 같은 칼럼이 있다.

칼럼	자료형	설명
buffers_backend	bigint	op_bytes에 지정된 각각의 크기인 쓰기 연산 횟수.
fsyncs	bigint	fsync 호출 수. 이것들은 컨텍스트 노멀에서만 추적된다.

 

그리고 PG16의 pg_stat_bgwriter 뷰를 살표보면 아래와 같은 칼럼들을 확인할 수 있다.

칼럼	자료형	설명
buffers_backend	bigint	백엔드(세션) 프로세스가 직접 기록한 총 버퍼 수.
buffers_backend_fsync	bigint	백엔드(세션) 프로세스가 직접 fsync 작업을 한 회수 (일반적으로 백엔드(세션) 프로세스가 공유 버퍼를 디스크로 내려 쓰더라도, 이 동기화 작업은 writer 프로세스가 담당한다. 즉 writer 프로세스의 fsync 작업 부하량을 파악하는데 도움이 된다.)

위 두개의 표를 통해 각뷰의 특정 칼럼들이 서로 유사성을 가지고 있다는 것을 확인할 수 있다.

 

또한 PG 17 업데이트를 통해 아래 예제와 같이 buffers_backend 칼럼과 buffers_backend_fsync 칼럼 등이 제거된 모습을 확인할 수 있다.

postgres=# SELECT * FROM pg_stat_bgwriter\gx -[ RECORD 1 ]---------+------------------------------ checkpoints_timed     | 124                           -- pg_stat_checkpointer 관련 칼럼으로 이동. checkpoints_req       | 10                            -- pg_stat_checkpointer 관련 칼럼으로 이동. checkpoint_write_time | 3370                          -- pg_stat_checkpointer 관련 칼럼으로 이동. checkpoint_sync_time  | 14                            -- pg_stat_checkpointer 관련 칼럼으로 이동. buffers_checkpoint    | 75                            -- pg_stat_checkpointer 관련 칼럼으로 이동. buffers_clean         | 0 maxwritten_clean      | 0 buffers_backend       | 9                             -- pg_catalog.pg_stat_io에 존재하여 제거. buffers_backend_fsync | 0                             -- pg_catalog.pg_stat_io에 존재하여 제거. buffers_alloc         | 2935 stats_reset           | 2024-07-12 16:55:11.046794+09

pg_stat_slru

이전에는 SLRU(Simple Least Recently Used) 관련 정보를 확인하기 위해 로그 파일 분석이나 내부 함수 호출 등 복잡한 방법을 사용해야 했다. 이로 인해 SLRU 캐시 상태를 실시간으로 모니터링하고 문제를 진단하기 어려웠다.

또한 SLRU 캐시 상태에 대한 가시성 부족으로 인해 SLRU 캐시 크기, 교체 정책 등 SLRU 관련 매개변수에 대한 튜닝이 어려웠다. 그래서 DBA는 SLRU 캐시 상태를 쉽게 확인하고 모니터링할 수 있는 기능을 요구했고, PostgreSQL 13에서 pg_stat_slru 뷰를 도입했다. 이를 통해 SLRU 캐시 상태에 대한 가시성을 크게 향상시키고, SLRU 튜닝을 용이하게 만들었다.

 

이번 PG17 업데이트에는 SLRU 버퍼 크기를 구성하기 위해 몇 개의 GUC를 추가하는 향후 패치에 적용될 이름과 일치시킬 목적으로 pg_stat_slru 뷰의 SLRU 칼럼 이름이 변경되었다. 또한 이 변경과 관련하여 pg_stat_reset_slru() 함수의 입력값도 변경된 칼럼 이름을 사용해야 한다. 이번 변경은 PG17 이전버전과 하위호환성 문제를 야기시킬 수 있으니 DBA입장에서는 이 점을 특히 주의해야 한다.

 

PG 16과 PG 17의 pg_stat_slru.name을 비교해 보면 아래와 같다.

PG 16	PG 17
postgres=# SELECT name FROM pg_stat_slru;       name        -----------------  CommitTs  MultiXactMember  MultiXactOffset  Notify  Serial  Subtrans  Xact  other (8 rows)	postgres=# SELECT name FROM pg_stat_slru;       name       ------------------ commit_timestamp multixact_member multixact_offset notify serializable subtransaction transaction other (8 rows)

 

pg_attribute

pg_attribute 테이블은 PostgreSQL의 핵심 시스템 카탈로그 테이블로, 데이터베이스 객체의 열 정보를 제공하고 있다. 이번 PG17 업데이트에서는 pg_attribute.attstattarget 및 pg_statistic_ext.stxstattarget칼럼의 기본 통계 대상을 NULL로 나타내도록 변경되었다.

먼저 pg_attribute.attstattarget 및 pg_statistic_ext.stxstattarget 칼럼에 대해 아래 표로 간략히 알아보자.

pg_attribute.attstattarget	pg_statistic_ext.stxstattarget
* ANALYZE를 통해 이 칼럼에 대한 축적된 통계의 세부 수준을 제어한다. * 0 값은 통계를 수집하지 않아야 함을 나타낸다. * null 값은 시스템 기본 통계 대상을 사용하라는 의미이다. * 양의 값은 정확히 무엇을 의미하는지는 데이터 유형에 따라 다르다. * 스칼라 데이터 유형의 경우 수집할 "가장 일반적인 값"의 목표 개수와 생성할 히스토그램 바이너리들의 목표 개수이다.	* 이 통계 오브젝트에 대해 ANALYZE 별로 누적된 통계의 세부 수준을 제어한다. * 0 값은 통계를 수집하지 않아야 함을 나타낸다. * null 값은 참조되는 열의 통계 대상 중 최대 값을 사용하거나 시스템 기본 통계 대상을 사용하도록 지시한다. * 양수 값은 수집할 "가장 일반적인 값"의 목표 개수를 결정한다.

 

이전에는 이 두 칼럼의 기본값이 -1이었는데, 이는 통계 수집을 하지 않는다는 의미였다. 하지만 -1이라는 값은 0값과 혼란을 줄 수 있기 때문에, 이를 -1에서 NULL로 변경하여 기본적으로 통계 수집을 하지 않는다는 것을 명확히 하였다.

 

PG 16의 pg_attribute.attstattarget 및 pg_attribute.stxstattarget 칼럼을 보면 attnotnull항목이 true로 설정되어 있어 NULL 설정이 불가능 했지만, PG 17의 칼럼을 보면 false로 설정되어 NULL 값의  설정이 가능하다는것을 확인할 수 있다.

PG 16	PG 17
postgres=# select attname,attnotnull from pg_catalog.pg_attribute where attname = 'attstattarget' or attname='stxstattarget';     attname    | attnotnull ---------------+------------ attstattarget | t stxstattarget | t (2 rows)	postgres=# select attname,attnotnull from pg_catalog.pg_attribute where attname = 'attstattarget' or attname='stxstattarget';     attname    | attnotnull ---------------+------------ attstattarget | f stxstattarget | f (2 rows)

 

아래 예제를 통해 pg_attribute.attstattarget 및 pg_statistic_ext.stxstattarget 칼럼이 어떻게 변화되었는지 확인해볼 수 있다.

postgres=# -- test 테이블 생성 postgres=# CREATE TABLE test ( postgres(#     id SERIAL PRIMARY KEY, postgres(#     col1 INT, postgres(#     col2 VARCHAR(50) postgres(# ); CREATE TABLE postgres=# postgres=# -- 데이터 입력 postgres=# INSERT INTO test (col1, col2) VALUES postgres-#     (1, 'apple'), postgres-#     (2, 'banana'), postgres-#     (3, 'cherry'), postgres-#     (4, 'date'), postgres-#     (5, 'elderberry'); INSERT 0 5 postgres=# -- attstattarget 확인 : default 값이 NULL postgres=# SELECT attname, attstattarget postgres-# FROM pg_attribute postgres-# WHERE attrelid = 'test'::regclass AND attnum > 0; attname | attstattarget ---------+--------------- id      |            -1   -- PG 17 이전에는 -1로 표기되어 있었다. col1    |            -1   -- PG 17 이전에는 -1로 표기되어 있었다. col2    |            -1   -- PG 17 이전에는 -1로 표기되어 있었다. (3 rows) postgres=# -- STATISTICS 값 변경 후 확인 postgres=# ALTER TABLE test ALTER COLUMN col1 SET STATISTICS 50; ALTER TABLE postgres=# SELECT attname, attstattarget                                                                                                                                                                                                                FROM pg_attribute                                                                                                                                                                                                                                       WHERE attrelid = 'test'::regclass AND attnum > 0; attname | attstattarget ---------+--------------- id      |              col1    |            50 col2    |              (3 rows) postgres=# -- statistics 생성 postgres=# create statistics mystat1 on col1,col2 from test; CREATE STATISTICS postgres=# -- pg_catalog.pg_statistic_ext 확인 : stxstattarget이 NULL임을 확인할 수 있다. postgres=# select * from pg_catalog.pg_statistic_ext;   oid  | stxrelid | stxname | stxnamespace | stxowner | stxkeys | stxstattarget | stxkind | stxexprs -------+----------+---------+--------------+----------+---------+---------------+---------+---------- 16427 |    16420 | mystat1 |         2200 |       10 | 2 3     |            -1 | {d,f,m} | (1 row)

pg_stat_progress_vacuum

PostgreSQL 9.6이전 버전에서는 PostgreSQL 데이터베이스의 Garbage Collector 역할을 하고 있는 VACUUM 작업의 진행 상황을 추정하거나 로그 파일을 확인해야 했다. 이러한 불편함을 해결하기 위해  PostgreSQL 9.6에서 pg_stat_progress_vacuum 뷰가 처음 도입되어 VACUUM 작업의 진행 상황, 처리된 튜플 수, 남은 작업량 등을 실시간으로 모니터링할 수 있게 되었다.

이번 PG17 업데이트로 pg_stat_progress_vacuum의 max_dead_tuples 칼럼의 이름을 max_dead_tuple_bytes로 변경했고, num_dead_tuples 칼럼의 이름을 num_dead_tuple_ids로 변경헀으며, dead_tuple_bytes 칼럼을 재추가 하였다. 그리고 또한 indexes_total과 indexes_processed 칼럼이 추가되었다.

 

기존의 max_dead_tuples와 num_dead_tuples 칼럼은 데이터베이스 내에서 사용되지 않는 튜플의 수를 나타낸다. 그러나 실제로 DBA입장에서 관심을 가지는 것은 사용되지 않는 튜플들이 차지하는 디스크 공간의 크기였다. 그래서 max_dead_tuples을 제거하고 max_dead_tuple_bytes 칼럼을 추가하였고, num_dead_tuples을 제거하고 dead_tuple_bytes 칼럼을 추가하여 더 정확한 정보를 제공하게 되었다. 다만, 기존에 삭제된 num_dead_tuples의 경우,  여전히 사용자에게 의미 있는 정보를 제공하기 때문에, num_dead_item_ids라는 이름으로 재추가 하여 데드 튜플 수에 대한 칼럼으로 사용할 수 있게 되었다.

또한 indexes_total과 indexes_processed칼럼이 추가되어 vacuum 작업 중 처리되는 인덱스에 대한 모니터링도 가능하게 되었다.

 

PG 17의 pg_catalog.pg_stat_progress_vacuum를 보면 PG16의 max_dead_tuples와 num_dead_tuples가 제거되고 max_dead_tuple_bytes, dead_tuple_bytes, num_dead_item_ids가 각각 추가된 것을 확인할 수 있다.

postgres=# \d pg_catalog.pg_stat_progress_vacuum;             View "pg_catalog.pg_stat_progress_vacuum"         Column        |  Type   | Collation | Nullable | Default  ----------------------+---------+-----------+----------+---------  pid                  | integer |           |          |   datid                | oid     |           |          |   datname              | name    |           |          |   relid                | oid     |           |          |   phase                | text    |           |          |   heap_blks_total      | bigint  |           |          |   heap_blks_scanned    | bigint  |           |          |   heap_blks_vacuumed   | bigint  |           |          |   index_vacuum_count   | bigint  |           |          |   max_dead_tuples      | bigint  |           |          | num_dead_tuples      | bigint  |           |          |   max_dead_tuple_bytes | bigint  |           |          |   dead_tuple_bytes     | bigint  |           |          |   num_dead_item_ids    | bigint  |           |          |   indexes_total        | bigint  |           |          |   indexes_processed    | bigint  |           |          |

칼럼	자료형	설명
max_dead_tuple_bytes	bigint	maintenance_work_mem을 기반으로 index vacuum cycle을 수행하기 전에 저장할 수 있는 데드 튜플 데이터의 양.
dead_tuple_bytes	bigint	마지막 index vacuum cycle 이후 수집된 데드 튜플 데이터의 양.
num_dead_item_ids	bigint	마지막 index vacuum cycle 이후 수집된 데드 항목 식별자 수.
indexes_total	bigint	vacuum작업이 정리되었거나 정리될 인덱스의 총 개수. vacuuming indexes 단계 또는 cleaning up indexes 단계가 시작되면 확인 가능하다.
indexes_processed	bigint	처리된 인덱스 수. vacuuming indexes 작업 또는 cleaning up indexes작업 때에만 진행된다.

 

3. Monitoring 영역

pg_stat_checkpointer

기존에는 체크포인트에 대한 발생 시기, 소요 시간, 주기 등을 쉽게 파악하기 어려웠고, 체크포인트 관련 성능 문제를 진단하기 위해서는 로그 파일을 수동으로 확인해야 했다. 또한 체크포인트 과다 발생이나 과도한 I/O 문제, 체크포인트 지연, 중단 등의 문제를 해결하기 위한 최적화가 필요했으나 문제의 원인을 신속하게 파악하기가 어려웠다.

 

PG17 업데이트에서는 pg_stat_checkpointer 뷰가 새롭게 추가되었다. pg_stat_checkpointer는 체크포인터 프로세스의 활동을 모니터링하는 데 사용된다. DBA는 체크포인터 프로세스를 통해 WAL(Write-Ahead Log) 관리, 버퍼 관리, 체크포인트 수행, 데이터베이스 복구 지원등이 가능해졌다. 이를 통해 데이터베이스의 일관성과 안정성을 유지하는 것이 수월해졌다.

 

PG 16에는 pg_catalog.pg_stat_checkpointer 정보가 없지만,

postgres=# \d pg_catalog.pg_stat_checkpointer; Did not find any relation named "pg_catalog.pg_stat_checkpointer".

 

PG 17에서 pg_catalog.pg_stat_checkpointer 관한 내용 확인이 가능하다.

postgres=# \d pg_catalog.pg_stat_checkpointer;                      View "pg_catalog.pg_stat_checkpointer"        Column        |           Type           | Collation | Nullable | Default  ---------------------+--------------------------+-----------+----------+---------  num_timed           | bigint                   |           |          |   num_requested       | bigint                   |           |          |   restartpoints_timed | bigint                   |           |          |   restartpoints_req   | bigint                   |           |          |   restartpoints_done  | bigint                   |           |          |   write_time          | double precision         |           |          |   sync_time           | double precision         |           |          |   buffers_written     | bigint                   |           |          |   stats_reset         | timestamp with time zone |           |          |

 

칼럼	자료형	설명
num_timed	bigint	수행된 예약된 체크포인트 수
num_requested	bigint	수행된 요청된 체크포인트 수
restartpoints_timed	bigint	시간 초과 또는 수행 시도 실패로 인해 예약된 재시작 지점 수
restartpoints_req	bigint	요청된 재시작 지점 수
restartpoints_done	bigint	수행된 재시작 지점 수
write_time	double precision	파일이 디스크에 기록되는 체크포인트 및 재시작포인트 처리 부분에서 소요된 총 시간(milliseconds)
sync_time	double precision	파일이 디스크에 동기화되는 체크포인트 및 재시작포인트 처리 부분에서 소요된 총 시간(milliseconds)
buffers_written	bigint	체크포인트 및 재시작포인트 동안 작성된 버퍼 수
stats_reset	timestamp with time zone	이 통계가 마지막으로 재설정된 시간

 

아래 예시를 통해 pg_stat_checkpointer 사용법을 알아보자.

postgres=# -- checkpoint 확인 postgres=# SELECT * FROM pg_stat_checkpointer; num_timed | num_requested | restartpoints_timed | restartpoints_req | restartpoints_done | write_time | sync_time | buffers_written |          stats_reset          -----------+---------------+---------------------+-------------------+--------------------+------------+-----------+-----------------+-------------------------------         76 |             7 |                   0 |                 0 |                  0 |      49447 |        51 |             533 | 2024-07-16 16:31:50.167073+09 (1 row) postgres=# -- checkpoint 생성 postgres=# CHECKPOINT; CHECKPOINT postgres=# SELECT * FROM pg_stat_checkpointer; num_timed | num_requested | restartpoints_timed | restartpoints_req | restartpoints_done | write_time | sync_time | buffers_written |          stats_reset          -----------+---------------+---------------------+-------------------+--------------------+------------+-----------+-----------------+-------------------------------         77 |             8 |                   0 |                 0 |                  0 |      49448 |        52 |             533 | 2024-07-16 16:31:50.167073+09 (1 row) postgres=# -- checkpoint 리셋 postgres=# SELECT pg_stat_reset_shared('checkpointer'); pg_stat_reset_shared ---------------------- (1 row) postgres=# -- 리셋된 checkpoint 확인 postgres=# SELECT * FROM pg_stat_checkpointer; num_timed | num_requested | restartpoints_timed | restartpoints_req | restartpoints_done | write_time | sync_time | buffers_written |          stats_reset          -----------+---------------+---------------------+-------------------+--------------------+------------+-----------+-----------------+-------------------------------         0 |             0 |                   0 |                 0 |                  0 |          0 |         0 |               0 | 2024-07-17 17:44:07.178496+09 (1 row)

 

pg_wait_events

그동안 DBA는 데이터베이스 성능 저하의 원인을 찾기 위해 어떤 리소스에서 대기 상태가 발생하는지 파악해야 했고, 성능 문제의 근본 원인을 찾아내기 위한 대기 이벤트 정보가 필요했다. 이러한 정보가 없으면 문제 해결에 더 많은 시간이 소요되었다.

PG17에서는 대기 이벤트 유형을 확인 할 수 있는 시스템 뷰인 pg_wait_events가 추가되어 데이터베이스의 성능 병목 지점을 신속하게 파악하고, 효과적인 튜닝 및 최적화를 수행할 수 있게 되었다. 또한 이 뷰는 pg_stat_activity에서 보고된 대기 이벤트에 대한 설명을 추가하는 데 유용하기도 하다.

 

PG 16에는 pg_catalog.pg_wait_events 정보가 없지만,

postgres=# \d pg_catalog.pg_wait_events; Did not find any relation named "pg_catalog.pg_wait_events".

 

PG 17에서 pg_catalog.pg_wait_events가 새롭게 추가된 것을 확인 할 수 있다.

postgres=# \d pg_catalog.pg_wait_events;           View "pg_catalog.pg_wait_events"    Column    | Type | Collation | Nullable | Default  -------------+------+-----------+----------+---------  type        | text |           |          |   name        | text |           |          |   description | text |           |          |

 

칼럼	자료형	설명
type	text	이벤트 유형.
name	text	이벤트 이름.
description	text	이벤트에 대한 설명.

 

아래 예시를 통해 pg_wait_events를 확인해보자.

postgres=# -- pg_wait_events 확인 postgres=# SELECT * FROM pg_wait_events LIMIT 3;   type   |       name        |                     description                     ----------+-------------------+----------------------------------------------------- Activity | ArchiverMain      | Waiting in main loop of archiver process Activity | AutovacuumMain    | Waiting in main loop of autovacuum launcher process Activity | BgwriterHibernate | Waiting in background writer process, hibernating (3 rows) postgres=# -- pg_wait_events와 pg_stat_activity를 join postgres=# SELECT psa.pid, psa.application_name, psa.wait_event, postgres-#                   we.description postgres-#              FROM pg_stat_activity psa postgres-#              JOIN pg_wait_events we postgres-#                ON (psa.wait_event_type = we.type AND postgres(#                    psa.wait_event = we.name);   pid  | application_name |     wait_event      |                         description                          -------+------------------+---------------------+-------------------------------------------------------------- 92708 |                  | AutovacuumMain      | Waiting in main loop of autovacuum launcher process 92705 |                  | BgwriterHibernate   | Waiting in background writer process, hibernating 92704 |                  | CheckpointerMain    | Waiting in main loop of checkpointer process 92709 |                  | LogicalLauncherMain | Waiting in main loop of logical replication launcher process 92707 |                  | WalWriterMain       | Waiting in main loop of WAL writer process 92711 | psql             | ClientRead          | Waiting to read data from the client (6 rows)

 

4. Logical Replication 영역

pg_stat_subscription

기존에는 구독 복제의 상태, 지연 시간, 처리 지연 등의 정보를 확인하기 위한 상세한 정보를 찾기 힘들 뿐만 아니라 이를 통한 복제 성능 최적화 및  복제 시스템 관리가 매우 어려웠다.

그리하여 pg_stat_subscription뷰가 PostgreSQL 10부터 도입되어 복제 구독(replication subscription)에 대한 다양한 정보를 통해 DBA가 복제 관련 문제를 진단하고 해결할 수 있게 되었다.

PG 17에서는 논리적 복제 작업자 유형을 쉽게 확인하기 위한 worker_type칼럼이 추가되었다. 기존의 leader_pid 및 reid와 같은 다른 칼럼을 논리적 복제 작업자 유형을 유추 할 수 있었지만 worker_type칼럼을 통해 보다 직관적이고 명확하게 논리적 복제 작업자 유형을 파악할 수 있다.

 

아래 내용처럼 PG16대비 worker_type 칼럼이 PG17에 새롭게 추가된 것을 확인 할 수 있다.

postgres=# \d pg_catalog.pg_stat_subscription;                       View "pg_catalog.pg_stat_subscription"         Column         |           Type           | Collation | Nullable | Default  -----------------------+--------------------------+-----------+----------+---------  subid                 | oid                      |           |          |   subname               | name                     |           |          |   worker_type           | text                     |           |          |   pid                   | integer                  |           |          |   leader_pid            | integer                  |           |          |   relid                 | oid                      |           |          |   received_lsn          | pg_lsn                   |           |          |   last_msg_send_time    | timestamp with time zone |           |          |   last_msg_receipt_time | timestamp with time zone |           |          |   latest_end_lsn        | pg_lsn                   |           |          |   latest_end_time       | timestamp with time zone |           |          |

칼럼	자료형	설명
worker_type	text	구독 작업자(subscription worker) 프로세스의 유형. 가능한 유형은 apply, parallel apply, table synchronization.

 

worker_type칼럼의 유형에 대해 좀 더 자세히 살펴보면 다음과 같다.

worker_type 칼럼 유형	설명
apply	복제 프로세스가 대상 데이터베이스에 변경 사항을 적용하는 작업을 수행하고 있음을 나타낸다. 즉, 논리적 복제 프로세스가 원본 데이터베이스에서 수신한 변경 사항을 대상 데이터베이스에 순차적으로 적용하고 있는 상태이다.
parallel apply	복제 프로세스가 변경 사항을 병렬로 적용하는 작업을 수행하고 있음을 나타낸다.
table synchronization	복제 프로세스가 초기 데이터 동기화 작업을 수행하고 있음을 나타낸다. 새로운 구독이 생성되거나 구독이 재동기화될 때 이 작업이 수행된다. 테이블의 전체 데이터를 대상 데이터베이스로 복사하는 작업이다.

결론

PostgreSQL 17에서의 시스템 카탈로그(pg_catalog) 개선은 새로운 카탈로그 객체와 열의 추가, 기존 객체의 확장 및 개선 등을 통해 데이터베이스 내부 구조와 메타데이터에 대한 가시성과 접근성이 향상되었다. 이를 통해 보다 효율적인 모니터링, 문제 해결, 튜닝 등이 가능해지면서 DBA와 개발자에게 많은 이점을 제공하게 되었다.

시스템 카탈로그의 지속적인 발전은 PostgreSQL의 확장성과 유연성을 높일 것으로 기대된다. 새로운 기능 및 확장 모듈 개발 시 관련 메타데이터의 노출과 관리가 용이해질 것이며, 카탈로그 정보에 대한 향상된 API 제공 등을 통해 외부 도구와의 통합성도 향상될 것이다.

참고 문헌

• PostgreSQL: Documentation: 17: E.1. Release 17
• https://pgpedia.info/postgresql-versions/postgresql-17.html 
• https://www.postgresql.org/about/featurematrix/ 
• https://www.postgresql.org/docs/17/catalogs.html 
• https://techblog.woowahan.com/9478/