2024년 9월 28일 PostgreSQL Global Development Group은 최신 버전의 PostgreSQL 17을 출시하였습니다. PostgreSQL 17 출시에 따른 주요 기능을 소개합니다.
📢 PostgreSQL 릴리즈 현황은 DB 인사이드 | PostgreSQL Setup - Version & Utility에서 확인할 수 있습니다.
PostgreSQL New Feature는 시리즈로 구성됩니다.
- PostgreSQL 17 주요 기능 설명과 파라미터, 시스템 카탈로그 변화
- PostgreSQL 17 성능 최적화(Shared Buffer Flush, NOT NULL과 NULL 조회 최적화, 조건절에 따른 실행계획 최적화)
- PostgreSQL 17 Vacuum 성능 향상
- PostgreSQL 17 증분백업(Incremental Backup)
본 문서에서는 PostgreSQL 17의 신규 기능인 Shared Buffer Flush와 성능 개선이 된 조회 최적화에 대한 내용을 테스트를 통해 소개합니다. 테스트는 PostgreSQL 17.2에서 진행하며, 비교 대상은 PostgreSQL 16.4 입니다.
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) 설치 및 확인
## 확장 모듈(Extension) 설치 및 확인
postgres=# CREAET extension pg_buffercache ;
CREATE EXTENSION
postgres=# CREAET extension pg_prewarm ;
CREATE EXTENSION
postgres=# \dx
List of installed extensions
Name | Version | Schema | Description
----------------+---------+------------+---------------------------------
pg_buffercache | 1.5 | public | examine the shared buffer cache
pg_prewarm | 1.2 | public | prewarm relation data
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language테스트 데이터 생성
-- table01 테스트 테이블 생성
CREATE TABLE table01 AS SELECT i AS c1 , i::text AS c2 FROM generate_series( 1, 10000 ) AS i ;
-- table01 테이블 로딩
SELECT pg_prewarm( 'table01' ) ;
pg_prewarm
------------
54
-- table01 테이블 경로 확인
SELECT pg_relation_filepath( 'table01' ) ;
pg_relation_filepath
----------------------
base/5/16445
-- table01 테이블(relfilenode=16445)의 buffercache 블록 사용량 확인
SELECT COUNT(*)
FROM pg_buffercache
WHERE relfilenode = 16445 ;
count
-------
58pg_buffercache를 사용한 마지막 SQL을 통해 총 58 블록의 table01 테이블이 Shared Buffer에 존재하는 것을 확인 할 수 있습니다. pg_prewarm으로 table01 테이블을 로딩 할 때 54 블록이 로딩 되지만, table01 테이블과 연관된 fsm(free sapce map)과 vm(visibility map)도 같이 로딩됩니다.
$ ls -alh $PGDATA/base/5/16445*
-rw------- 1 postgres postgres 432K Dec 3 16:27 /home/postgres/pg17.data/base/5/16445
-rw------- 1 postgres postgres 24K Dec 3 16:27 /home/postgres/pg17.data/base/5/16445_fsm
-rw------- 1 postgres postgres 8.0K Dec 3 16:27 /home/postgres/pg17.data/base/5/16445_vm
## 16445 파일의 총 용량 = 432K + 24K + 8K = 464K / 8K = 58 Block-- relforknumber = 1 : fsm 블록
-- relforknumber = 2 : vm 블록
SELECT relforknumber
FROM pg_buffercache
WHERE relfilenode = 16445
AND relforknumber != 0 ;
relforknumber
---------------
1
1
1
2Shared Buffer에서 table01 테이블 비우기
pg_buffercache_evict() 함수를 사용하여 Shared Buffer에서 해당 테이블의 Buffer를 완전히 제거합니다.
SELECT pg_buffercache_evict( bufferid )
FROM pg_buffercache
WHERE relfilenode = 16445 ;
pg_buffercache_evict
----------------------
t
t
t
... 생략 ...
(58 rows)Shared Buffer에서 table01 테이블 블록 삭제 확인
SELECT COUNT(*)
FROM pg_buffercache
WHERE relfilenode = 16445 ;
count
-------
0
(1 row)
IS NOT NULL / IS NULL 조회 최적화
테이블 컬럼 중 NOT NULL이 설정된 컬럼에 대해 IS NOT NULL 조건으로 조회하거나, IS NULL 조건으로 조회할 경우 해당 조건을 제거합니다.
테스트 데이터 생성
CREATE TABLE table01 ( c1 INTEGER NOT NULL , c2 TEXT ) ;
INSERT INTO table01 SELECT i , i::text FROM generate_series( 1, 10000 ) AS i ;IS NOT NULL 비교
-- 실행 SQL
EXPLAIN ( ANALYZE , COSTS OFF )
SELECT *
FROM table01
WHERE c1 IS NOT NULL ;-- PostgreSQL 16.4
-- c1 IS NOT NULL 조건 Filter 처리 (table01.c1은 NOT NULL 컬럼)
QUERY PLAN
-------------------------------------------------------------------
Seq Scan on table01 (actual time=0.046..12.210 rows=10000 loops=1)
Filter: (c1 IS NOT NULL)
Planning Time: 0.210 ms
Execution Time: 23.288 ms
(4 rows)-- PostgreSQL 17.2
-- c1 IS NOT NULL 조건 제거 (table01.c1은 NOT NULL 컬럼)
QUERY PLAN
-------------------------------------------------------------------
Seq Scan on table01 (actual time=0.036..12.345 rows=10000 loops=1)
Planning Time: 0.166 ms
Execution Time: 23.697 ms
(3 rows)IS NULL 비교
-- 실행 SQL
EXPLAIN ( ANALYZE , COSTS OFF )
SELECT *
FROM table01
WHERE c1 IS NULL ;-- PostgreSQL 16.4
-- c1 IS NULL 조건 Filter 처리 (table01.c1은 NOT NULL 컬럼)
QUERY PLAN
--------------------------------------------------------------
Seq Scan on table01 (actual time=1.811..1.813 rows=0 loops=1)
Filter: (c1 IS NULL)
Rows Removed by Filter: 10000
Planning Time: 0.088 ms
Execution Time: 1.852 ms
(5 rows)-- PostgreSQL 17.2
-- c1 IS NULL 조건 제거 (table01.c1은 NOT NULL 컬럼)
QUERY PLAN
--------------------------------------------------
Result (actual time=0.007..0.009 rows=0 loops=1)
One-Time Filter: false
Planning Time: 0.138 ms
Execution Time: 0.044 ms
(4 rows)
동일 테이블의 여러 조건절
여러 조건절이 포함된 테이블을 조회할 때 먼저 SQL을 수행하지 않고 해당 SQL을 먼저 분석하여 효율적인 실행 계획을 세웁니다.
테스트 데이터 생성
CREATE TABLE table01 AS
SELECT i AS c1 , md5( random()::text ) AS c2 FROM generate_series( 1, 1000000 ) AS i ;-- 실행 SQL
EXPLAIN ( ANALYZE , COSTS OFF )
SELECT *
FROM table01
WHERE c1 = 3
AND c1 = 5 ;위 예시는 c1=3 이면서 c1=5인 데이터를 조회하는 모순이 있습니다. PostgreSQL 16에서 실행 계획을 확인 하면 SQL을 먼저 분석하기 전에 테이블에 먼저 Access(c1=5)한 후 다음 조건에서는 테이블에 Access하지 않습니다. 결국 실행 계획을 세우는데 리소스가 낭비되었음을 알 수 있습니다.
EXPLAIN 비교
-- PostgreSQL 16.4
QUERY PLAN
--------------------------------------------------
Gather (actual time=23.795..25.214 rows=0 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Result (actual time=0.005..0.008 rows=0 loops=3)
One-Time Filter: false
-> Parallel Seq Scan on table01 (never executed)
Filter: (c1 = 3)
Planning Time: 0.198 ms
Execution Time: 25.262 ms
(9 rows)PostgreSQL 17에서는 SQL을 수행하기 전에 SQL을 먼저 분석하여 테이블에 Access하지 않아도 된다는 실행 계획을 세웁니다.
-- PostgreSQL 17.2
QUERY PLAN
--------------------------------------------------
Result (actual time=0.004..0.006 rows=0 loops=1)
One-Time Filter: false
Planning Time: 0.195 ms
Execution Time: 0.036 ms
(4 rows)
pg_dump 옵션 추가
--filter 옵션 추가
{ include | exclude } { extension | foreign_data | table | table_and_children | table_data | table_data_and_children | schema } PATTERN
## example
## table로 시작하는 테이블 중 table01은 제외
include table table*
exclude table table01
| 옵션 | 설명 |
| 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에서도 해당 옵션이 추가되었습니다.
사용법
vi filter.txt
include table table*
exclude table table01
pg_dump --filter=filter.txt mydb > db.sql
기획 및 글 | 플랫폼기술연구팀
'엑셈 경쟁력 > DB 인사이드' 카테고리의 다른 글
| DB 인사이드 | PostgreSQL New Feature - 17 Release (1) (0) | 2024.12.30 |
|---|---|
| DB 인사이드 | PWI - Shared Buffer > Wait Event (0) | 2024.11.27 |
| DB 인사이드 | PWI - Shared Buffer > 동작원리 (0) | 2024.10.25 |
| DB 인사이드 | PWI - LOCK > Other Locks (0) | 2024.09.25 |
| DB 인사이드 | PWI - LOCK > Row-level Lock(2) (0) | 2024.08.05 |
댓글