2024년 9월 28일 PostgreSQL Global Development Group은 최신 버전의 PostgreSQL 17을 출시하였습니다. PostgreSQL 17 출시에 따른 주요 기능을 소개합니다.
📢 PostgreSQL 릴리즈 현황은 DB 인사이드 | PostgreSQL Setup - Version & Utility에서 확인할 수 있습니다.
PostgreSQL New Feature는 시리즈로 구성됩니다.
- PostgreSQL 17 주요 기능 설명과 파라미터, 시스템 카탈로그 변화
- PostgreSQL 17 추가, 개선 기능
- PostgreSQL 17 Vacuum 성능 향상
- PostgreSQL 17 증분백업(Incremental Backup)
본 문서에서는 PostgreSQL 17에서 추가, 개선된 내용을 테스트를 통해 소개합니다. 테스트는 PostgreSQL 17.2에서 진행하며, 비교 대상은 PostgreSQL 16.4입니다. 본 문서에서 소개하는 기능은 아래와 같습니다.
- Shared Buffer Flush
- NOT NULL과 NULL 조회 최적화
- 동일 테이블의 여러 조건절이 있을 경우 실행 계획 최적화
- pg_dump의 --fileter 옵션 추가
- pg_maintain Role과 MAINTAIN 권한 추가
- EXPLAIN의 확장
Shared Buffer 비우기
오라클에서
ALTER SYSTEM FLUSH BUFFER CACHE와 같이 명령어를 통해 Buffer Cache를 비우는 방법은 PostgreSQL에는 존재하지 않았고, PostgreSQL 16 까지는 인스턴스를 다시 시작하는 것 외에는 Shared Buffer를 비울 수 있는 방법이 없습니다.PostgreSQL 17에서 pg_buffercache_evict 함수를 사용하여 Shared Buffer를 비울 수 있습니다. pg_buffercache_evict 함수를 사용하기 위해서는 pg_buffercache 확장 모듈(Extension)을 설치해야 합니다. 또한, 테스트 중 Shared Buffer에 데이터를 미리 Load 하기 위해 pg_prewarm 확장 모듈(Extension)을 설치합니다.
확장 모듈(Extension) 설치 및 확인
테스트 데이터 생성
pg_buffercache를 사용한 마지막 SQL을 통해 총 58 블록의 table01 테이블이 Shared Buffer에 존재하는 것을 확인할 수 있습니다. pg_prewarm으로 table01 테이블을 로딩할 때 54 블록이 로딩되지만, table01 테이블과 연관된 fsm(free sapce map)과 vm(visibility map)도 같이 로딩됩니다.
Shared Buffer에서 table01 테이블 비우기
pg_buffercache_evict() 함수를 사용하여 Shared Buffer에서 해당 테이블의 Buffer를 완전히 제거합니다.Shared Buffer에서 table01 테이블 블록 삭제 확인
IS NOT NULL / IS NULL 조회 최적화
PostgreSQL 17에서 테이블 컬럼 중 NOT NULL이 설정되어 있는 경우 IS NULL과 IS NOT NULL 처리에 최적화가 포함되었습니다. 이는 NOT NULL 조건이 있는 컬럼에 대한 IS NOT NULL 조건을 제거할 수 있으며, NOT NULL 컬럼에서 IS NULL 조건이 있을 건너뛸 수 있습니다. 아래 테스트를 통해 확인해 보겠습니다.
테스트 데이터 생성
IS NOT NULL 비교
PostgreSQL 16.4에서는 c1 컬럼이 NOT NULL로 설정되어 있더라도 IS NOT NULL을 수행하여 Filter: (c1 IS NOT NULL) 실행 계획이 수립됩니다.
PostgreSQL 17에서는 c1 컬럼이 NOT NULL로 설정되어 있다는 것을 알고 IS NOT NULL을 수행하지 않고 해당 조건을 제거한 실행 계획이 수립됩니다.
IS NULL 비교
PostgreSQL 16.4에서 IS NOT NULL을 처리한 것과 동일하게 IS NULL을 수행하여 Filter: (c1 IS NULL) 실행 계획이 수립됩니다.
PostgreSQL 17에서는 c1 컬럼이 NOT NULL로 설정되어 있다는 것을 알고 IS NULL을 수행하지 않고 해당 조건을 제거한 실행 계획이 수립됩니다.
동일 테이블의 여러 조건절
여러 조건절이 포함된 테이블을 조회할 때 먼저 SQL을 수행하지 않고 해당 SQL을 먼저 분석하여 효율적인 실행 계획을 세웁니다.
테스트 데이터 생성
위 예시는 c1=3 이면서 c1=5인 데이터를 조회하는 모순이 있습니다. PostgreSQL 16에서 실행 계획을 확인하면 SQL을 먼저 분석하기 전에 테이블에 먼저 Access(c1=5) 한 후 다음 조건에서는 테이블에 Access 하지 않습니다. 결국 실행 계획을 세우는데 리소스가 낭비되었음을 알 수 있습니다.
EXPLAIN 비교
PostgreSQL 17에서는 SQL을 수행하기 전에 SQL을 먼저 분석하여 테이블에 Access 하지 않아도 된다는 실행 계획을 세웁니다.
pg_dump 옵션 추가
-filter 옵션 추가
옵션 | 설명 |
extension | -e/--extension 또는 --exclude-extension 옵션처럼 수행 |
foreign_data | --include-foreign-data 옵션처럼 수행. include만 사용 가능 |
table | -t/--table 또는 -T/--exclude-table 옵션처럼 수행 |
table_and_children | --table-and-children 또는 --exclude-table-and-children 옵션처럼 수행 |
table_data | --exclude-table-data 옵션처럼 수행. exclude만 사용 가능 |
table_data_and_children | --exclude-table-data-and-children 옵션처럼 수행. exclude만 사용 가능 |
schema | -n/--schema 또는 -N/--exclude-schema 옵션처럼 수행 |
📢 pg_dumpall과 pg_restore Utility에서도 해당 옵션이 추가되었습니다.
사용법
pg_maintain Role과 MAINTAIN 권한 생성
PostgreSQL 17에서 데이터베이스 유지 관리 작업에 대한 pg_maintain Role과 MAINTAIN 권한이 새롭게 추가되었습니다.
pg_maintain Role
pg_maintain Role은 데이터베이스 유지 관리와 관련된 작업을 수행할 수 있는 권한을 제공하는 사전에 정의된 Role입니다. 데이터베이스 전체에 대한 유지 관리 권한이 포함되어 있으며, 슈퍼유저 권한 없이 데이터베이스 유지 관리 작업을 쉽게 위임할 수 있습니다. pg_maintain Role은 데이터베이스 유지 관리에 특화되어 있으며, 유지 관리 작업 외에 데이터 조작이나 스키마 변경 등의 작업은 불가능합니다.
pg_maintain Role이 제공하는 작업
역할 | 설명 |
VACUUM | 테이블의 불필요한 데이터 정리 |
ANALYZE | 테이블 통계를 수집 |
REINDEX | 인덱스 재구성 |
CLUSTER | 테이블 데이터를 인덱스 순서로 재배치 |
REFRESH MATERIALIZED VIEW | Materialized View를 최신 상태로 업데이트 |
CHECKPOINT | 데이터 변경 사항을 디스크에 기록 |
pg_maintain Role 부여 및 회수
MAINTIN 권한
MAINTAIN 권한은 특정 데이터베이스 Object에 대한 유지 관리 작업을 수행할 수 있는 권한으로, 특정 Object에 대한 유지 관리 작업만 수행하도록 제한할 수 있으며 pg_maintain Role과 같이 데이터베이스 Object 관리 측면의 작업을 쉽게 위임할 수 있습니다. MAINTAIN 권한은 특정 데이터베이스 Object 유지 관리에 특화되어 있으며, 유지 관리 작업 외에 데이터 조작이나 스키마 변경 등의 작업은 불가능합니다.
MAINTAIN 권한이 제공하는 작업
역할 | 설명 |
VACUUM | 테이블의 불필요한 데이터 정리 |
ANALYZE | 테이블 통계를 수집 |
REINDEX | 인덱스 재구성 |
CLUSTER | 테이블 데이터를 인덱스 순서로 재배치 |
REFRESH MATERIALIZED VIEW | Materialized View를 최신 상태로 업데이트 |
MAINTAIN 부여 및 회수
pg_maintain Role과 MAINTAIN 권한 차이
특징 | pg_maintain Role | MAINTAIN 권한 |
적용 대상 | 데이터베이스 전체 | 특정 Object (테이블, 스키마, 뷰 등) |
부여 범위 | 데이터베이스 전체 유지 관리 권한 제공 | 특정 Object에 대해 세분화된 권한 설정 가능 |
작업 범위 | 데이터베이스 내 모든 유지 관리 작업 가능 | 해당 Object의 유지 관리 작업만 가능 |
pg_maintain Role과 MAINTAIN 권한 테스트
User를 생성하여 pg_maintain Role과 MAINTAIN 권한에 대한 테스트를 진행합니다.
user01과 user02 테스트 User를 생성하였고, public 스키마에 10건의 데이터를 입력한 table01과 table02 테이블을 생성하였습니다. 다음으로, 생성한 각 User로 접속하여 테이블을 조회하고 VACUUM을 수행할 수 있는지 확인합니다.
아직 User에 권한을 부여하지 않았기에 테이블을 조회하거나 VACUUM을 수행할 수 있는 권한이 없다는 에러 메시지가 출력됩니다. 위 예시에서는 table01 테이블에 대해서만 조회와 VACUUM을 수행하였지만, table02에 대한 권한이 없는 것은 마찬가지이기 때문에 데이터 조회와 VACUUM은 수행되지 않습니다. 다음으로, user01에는 pg_maintain Role을 부여하고, user02에는 table01에 대한 MAINTAIN 권한을 부여한 후 table01 테이블을 조회하고 VACUUM을 수행합니다.
user01과 user02가 table01 테이블에 대해 pg_maintain Role과 MAINTAIN 권한을 부여받더라도, table01 테이블에 대한 조작 행위(SELECT, INSERT)는 불가능하다는 것을 확인할 수 있습니다. pg_maintain Role과 MAINTAIN 권한에서 VACUUM 작업은 가능하기 때문에 각 User에서 VACUUM은 수행이 된 것을 확인할 수 있습니다. 다음으로, table02 테이블에 대한 VACUUM 수행을 확인해 보겠습니다.(위 테스트에서 테이블 조작 행위는 불가능하다는 것을 확인했기 때문에 테이블 조회는 생략합니다.)
user01은 pg_maintain Role을 부여받았기 때문에 데이터베이스 전체에 대한 유지 관리 작업을 할 수 있습니다. 따라서 table02 테이블에 대한 VACUUM 수행이 가능합니다. 반면에 user02는 table01 테이블에 대한 MAINTAIN 권한을 부여받았기 때문에 table02 테이블에 대한 VACUUM 수행은 불가능합니다. 즉, MAINTAIN 권한은 권한을 부여받은 특정 Object에 대해서만 유지 관리 작업을 할 수 있다는 것을 확인할 수 있습니다.
EXPLAIN의 확장
PostgreSQL 17에서 EXPLAIN 명령어의 새로운 옵션이 추가되어 쿼리 성능 분석을 좀 더 자세하게 확인할 수 있습니다.
SERIALIZE와 MEMORY 옵션이 추가되었고, BUFFERS 옵션에서 I/O 시간을 확인할 수 있도록 개선되었습니다.구분 | 설명 |
SERIALIZE 옵션 추가 | 네트워크 전송을 위한 데이터 변환에 소요된 시간을 확인할 수 있습니다. ANALYZE 옵션과 함께 사용해야 하며, SERIALIZE의 세부 옵션으로 TEXT와 BINARY를 지정할 수 있습니다. |
MEMORY 옵션 추가 | 실행 계획 생성에 할당된 메모리 양과 실제로 사용된 메모리 양의 정보가 출력됩니다.단, 실제 쿼리 수행 시 사용되는 메모리 양이 아니라는 점에 유의해야 합니다. |
BUFFERS 옵션 개선 | Local I/O Read Time과 Local I/O Write Time이 출력 되도록 개선되었습니다.PostgreSQL 16까지 I/O Read Time과 I/O Write Time은 Shared에 대한 정보만 출력했지만, Shared I/O Read Time과 Shared I/O Write Time으로 변경되면서 Shared와 Local의 대한 정보를 같이 출력합니다. |
테스트 데이터 생성
SERIALIZE 옵션
SERIALIZE 옵션은 ANALYZE 옵션과 함께 사용해야 하며, 네트워크 전송을 위한 데이터 변환에 소요된 시간을 확인할 수 있습니다. SERIALIZE의 세부 옵션은 TEXT와 BINARY를 지정할 수 있고, 세부 옵션을 지정하지 않을 경우 기본값으로 TEXT가 선택됩니다.
MEMORY 옵션
실행 계획 생성에 할당된 메모리 양(allocated)과 실제로 사용된 메모리 양(used)의 정보가 출력됩니다. 실제 쿼리 수행 시 사용되는 메모리 양이 아니라는 점에 유의해야 합니다.
BUFFERS 옵션
PostgreSQL 17에서 Local I/O Read Time과 Local I/O Write Time이 출력되도록 개선되었고, 기존의 I/O Read Time과 I/O Write Time은 Shared I/O Read Time와 Shared I/O Write Time으로 변경되었습니다. 이러한 내용을 출력하기 위해서는
track_io_timing=on 으로 파라미터를 설정해야 합니다.EXPLAIN의 기본 출력 포맷은 TEXT로, 항목의 값이 0인 경우에는 출력이 생략되기 때문에 출력 포맷을 JSON으로 변경하여 다시 수행하면 Shared I/O Read Time, Shared I/O Write Time, Local I/O Read Time, Local I/O Write Time 항목을 확인할 수 있습니다. PostgreSQL 16과 PostgreSQL 17에서 BUFFERS의 전체 결과는 아래 토글에서 확인할 수 있습니다.
PostgreSQL 16.4에서 EXPLAIN BUFFERS 결과
PostgreSQL 17.2에서 EXPLAIN BUFFERS 결과
함께 보면 좋은 아티클
