PostgreSQL 13(1편 Index size)

 

며칠 전인 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