며칠 전인 9월 24일에 PostgreSQL 13 정식 버전이 릴리즈 되었다.
이번 기회에 개인적으로 공부도 할겸, 공식 문서와 해외 여러 곳에서 다룬 이번 PG13에 대한 글들을 한글로 정리하고자 한다.
참조한 곳들은 이 글들의 가장 아래에 언급해 두었다. 내용이 이해가 가지 않거나, 불명확한 부분이 있다면 그곳을 참고하기 바란다.
PG 13 버전에서 성능과 관련하여 변경된 점들 중, 가장 관심이 가는 부분들은 중복 데이터 관련하여 B-TREE 인덱스 사이즈가 줄어든 것,인덱스에 대한 VACUUM 작업과 REINDEX 작업이 병렬 처리 지원이 된다는 점들이다. 그 외에도 파티션 관련 성능 개선이나 사용되었던 복제 슬롯으로 인한 WAL 파일 늘어남으로 인한 DISK 용량 관리 문제 등이 해결되었다.
이 중 제일 먼저 다룰 부분은 중복 데이터 존재시의 B-TREE 인덱스 크기 변화이며, PG 11, 12 그리고 13 버전에서의 용량 그리고 성능 차이를 다뤄 보겠다.
B-Tree Index 크기 변화
PostgreSQL 12 버전 릴리즈 때에도 multi-column에 생성한 인덱스 관련하여 중복값 관련 인덱스 용량이 30%가량 줄어들었던 것으로 기억하는데, PG 13에서는 어떤 변화가 있는지에 대해 알아보도록 하자.
인덱스 생성에 사용한 구문(300만건)
# CREATE TABLE rel (
# aid bigint NOT NULL,
# bid bigint NOT NULL
# );
CREATE TABLE
# ALTER TABLE rel
# ADD CONSTRAINT rel_pkey PRIMARY KEY (aid, bid);
ALTER TABLE
# CREATE INDEX rel_bid_idx ON rel (bid);
CREATE INDEX
# INSERT INTO rel (aid, bid)
# SELECT i, i / 10000
# FROM generate_series(1, 3000000) AS i;
위의 방식으로 생성한 데이터는 aid의 경우는 1부터 300만까지가 중복없이 존재하고 bid의 경우는 0부터 299까지의 중복되는 값이 대략 10000건씩 존재하게 된다. 이제 각 버전에서 각 인덱스들이 차지하는 용량을 비교해보도록 하자.
버전별로 생성된 인덱스들이 차지하는 디스크 용량
*PostgreSQL 11 *
# select version();
version
------------------------------------------------------------------------------------------
PostgreSQL 11.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8
(1 row)
# \di+
List of relations
Schema | Name | Type | Owner | Table | Size | Description
----------+-------------+-------+----------+-------+--------+-------------
public | rel_bid_idx | index | postgres | rel | 281 MB |
public | rel_pkey | index | postgres | rel | 299 MB |
(2 rows)
PostgreSQL 12
# select version();
version
------------------------------------------------------------------------------------------
---------------
PostgreSQL 12.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8
.5-39), 64-bit
(1 row)
# \di+
List of relations
Schema | Name | Type | Owner | Table | Size | Description
---------+-------------+-------+---------+-------+--------+-------------
public | rel_bid_idx | index | postgres | rel | 152 MB |
public | rel_pkey | index | postgres | rel | 134 MB |
(2 rows)
PostgreSQL 13
# select version();
version
------------------------------------------------------------------------------------------
PostgreSQL 13.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8
(1 row)
# \di+
List of relations
Schema | Name | Type | Owner | Table | Persistence | Size | Description
---------+-------------+-------+---------+-------+-------------+--------+-------------
public | rel_bid_idx | index | postgres | rel | permanent | 56 MB |
public | rel_pkey | index | postgres | rel | permanent | 179 MB |
(2 rows)
| PG version |
rel_bid_idx |
rel_pkey |
| 11.7 |
281 MB |
299 MB |
| 12.3 |
152 MB(54%) |
134 MB(44%) |
| 13.0 |
56 MB(20%) |
179 MB(59%) |
용량의 변화가 인덱스를 사용한 쿼리 플랜에 어떤 영향을 미치는지 확인해보기 위해, 하단의 간단한 쿼리를 수행시켜, 수행시간을 비교해보았다.
select min(bid) from rel where aid between 10000 and 150000;
PostgreSQL 11
QUERY PLAN
---------------------------------------------------------------------------
Result (actual rows=1 loops=1)
Buffers: shared hit=174
InitPlan 1 (returns $0)
-> Limit (actual rows=1 loops=1)
Buffers: shared hit=174
-> Index Scan using rel_bid_idx on rel (actual rows=1 loops=1)
Index Cond: (bid IS NOT NULL)
Filter: ((aid >= 10000) AND (aid <= 150000))
Rows Removed by Filter: 9999
Buffers: shared hit=174
PostgreSQL 12
QUERY PLAN
---------------------------------------------------------------------------
Result (actual rows=1 loops=1)
Buffers: shared hit=85
InitPlan 1 (returns $0)
-> Limit (actual rows=1 loops=1)
Buffers: shared hit=85
-> Index Scan using rel_bid_idx on rel (actual rows=1 loops=1)
Index Cond: (bid IS NOT NULL)
Filter: ((aid >= 10000) AND (aid <= 150000))
Rows Removed by Filter: 9999
Buffers: shared hit=85
PostgreSQL 13
QUERY PLAN
---------------------------------------------------------------------------
Result (actual rows=1 loops=1)
Buffers: shared hit=67
InitPlan 1 (returns $0)
-> Limit (actual rows=1 loops=1)
Buffers: shared hit=67
-> Index Scan using rel_bid_idx on rel (actual rows=1 loops=1)
Index Cond: (bid IS NOT NULL)
Filter: ((aid >= 10000) AND (aid <= 150000))
Rows Removed by Filter: 9999
Buffers: shared hit=67
Planning:
Buffers: shared hit=8
실제로 인덱스 블록의 READ 수가 줄어들었음을 확인할 수 있으며, 이는 결국 성능 개선으로 연결될 것이다. 테이블의 사이즈가 더 커지고, 사용하는 인덱스가 많아질 수록, 이러한 변화는 DB 레벨로 보았을 때 결코 적지 않은 차이로 이어질 것이다.
한가지 염두에 두어야 할 점이 있다면, PostgreSQL 12버전 릴리즈 때도 마찬가지였지만 기존 버전을 PG_UPGRADE를 이용하여 버전을 변경한 경우, 인덱스 용량 개선과 그로 인한 성능 개선의 혜택을 보고 싶다면, REINDEX 작업이 필요하다. pg_dump&restore 방식으로 작업한다면 이러한 문제는 없겠지만, 별도의 용량과 시간이 필요한 것은 마찬가지이므로, PostgreSQL 운영 환경의 메이저 버전 업그레이드를 생각하고 있다면, PG_UPGRADE 후에 REINDEX 작업이 필요하다는 점을 염두에 두도록 하자.
관련되어 PG13부터 추가된 공식 문서에는 어떤 방식으로 중복값 제거가 도입되었고, 어떤 경우에 중복값 제거의 사용이 안되는지에 대해서 언급하고 있다.
https://www.postgresql.org/docs/13/btree-implementation.html
