PostgreSQL 9.5의 새로운 기능: BRIN 인덱스

PostgreSQL 9.5의 새로운 기능: BRIN 인덱스

 

 

최근 PostgreSQL 9.5가 릴리즈됐습니다(http://www.postgresql.org/about/news/1636/).

이번 포스트에서는 9.5에 새로 추가된 기능인 BRIN 인덱스에 대해 알아보겠습니다.

 

BRIN은 Block Range INdexes를 뜻합니다. 이 인덱스는 페이지에 대한 메타데이터를 저장하는 인덱스입니다. 즉 테이블의 페이지에 대해 검색에 도움이 될 수 있는 메타데이터를 뽑아서 인덱스로 구성하는 것입니다. 메타데이터의 한 가지 예로 현재는 페이지에 저장된 특정 컬럼의 최소값과 최대값을 저장합니다.

 

다음과 같은 저장 구조를 가진 테이블을 생각해 봅시다.

(예제 출처: http://pythonsweetness.tumblr.com/post/119568339102/block-range-brin-indexes-in-postgresql-95#notes)

 

Page Number	Item ID	ctid	Name	Age	Creation Date
1	1	(1, 1)	John	10	1998-01
	2	(1, 2)	Jack	99	1998-02
	3	(1, 3)	Jill	70	1998-03
	4	(1, 4)	Jemma	19	1998-04
2	1	(2, 1)	George	60	1998-05
	2	(2, 2)	James	44	1998-05
	3	(2, 3)	Jocelyn	55	1998-06
	4	(2, 4)	Jemima	22	1998-07
3	1	(3, 1)	Jerry	60	1999-01
	2	(3, 2)	Jarvis	44	1999-02
	3	(3, 3)	Jasper	55	1999-03
	4	(3, 4)	Josh	24	1999-04
4	1	(4, 1)	Jacob	60	2000-01
	2	(4, 2)	Jesse	44	2000-02
	3	(4, 3)	Janet	55	2000-03
	4	(4, 4)	Justine	24	2000-04

 

위 테이블은 4개의 페이지로 구성돼 있고, 각 페이지는 4개의 row를 갖고 있습니다. 위와 같은 테이블의 age 컬럼에 BRIN 인덱스를 만들면 다음과 같습니다.

 

 

Page Number	Has NULL values?	Lowest Age	Highest Age
1	No	10	99
2	No	22	60
3	No	24	60
4	No	24	60

 

 

위 BRIN 인덱스는 전체 테이블의 내용을 4개의 엔트리로 요약했습니다. 위의 내용은 다음과 같이 해석할 수 있습니다. 예를 들어 첫 번째 엔트리는 페이지 1의 row들이 가진 age 컬럼의 최소값은 10, 최대값은 99이라는 것을 알려줍니다. 이와 같이 BRIN 인덱스는 각 페이지에 저장된 값에 대한 메타데이터(최소값, 최대값)을 저장해 테이블의 구조를 요약한다고 볼 수 있습니다.

 

만일 SELECT * FROM person WHERE age BETWEEN 10 AND 15 와 같은 질의를 수행하게 되면 BRIN 인덱스를 통해 페이지 1번만 스캔하면 된다는 것을 알 수 있습니다.

 

이처럼 BRIN 인덱스를 통해 테이블을 전체 스캔하지 않고 질의에 필요한 페이지만을 스캔할 수 있습니다. 하지만 BRIN 인덱스가 항상 효율적이지만은 않습니다. 위의 질의에서는 우연히 질의 대상인 페이지가 1개였지만, 조건이 BETWEEN 10 AND 50과 같이 변경된다면 모든 페이지를 스캔해야 합니다. 또한 조건이 age = 30인 경우도 모든 페이지를 스캔해야 하지만 만일 btree 인덱스를 사용했다면 페이지 스캔 없이 이 조건에 맞는 row는 없다는 것을 알 수 있습니다.

 

BRIN 인덱스의 가치가 발휘되는 경우는 매우 큰 테이블에서 특정 컬럼의 저장 순서에 따라 컬럼 값이 정렬돼 있는 경우입니다. 위 테이블의 경우 Creation Date 컬럼이 정렬돼 있는 순서라는 것을 알 수 있습니다. 이 경우 특정 기간 동안 생성된 row를 SELECT하는 질의가 있다면 Creation Date 컬럼에 BRIN 인덱스를 생성하는 것이 좋습니다. 이 경우에는 btree 보다 성능이 좋을 수 있습니다.

 

참고 자료

http://www.postgresql.org/docs/9.5/static/brin-intro.html

http://pythonsweetness.tumblr.com/post/119568339102/block-range-brin-indexes-in-postgresql-95#notes

https://www.reddit.com/r/PostgreSQL/comments/3teoc1/when_should_i_use_brin_indexes/

Posted by Bitnine(비트나인)