[PostgreSql] 성능향상을 위한 패키지 설치

프로젝트 중 gitlab-ce 버전을 올려서 설치하는 임무?를 맡았다..

AS-IS는 gitlab-ce:12.3.5 버전이고, 현재나온 stable 버전이 gitlab-ce:14.10.5 버전이라 14로 설치하기로 했다.

설치 환경은 ARO(azure redhat openshift)에 gitlab-ce omnibus 버전이다. 

 

gitlab-ce 에서 사용하는 db는 postgresql 인데,

요구하는 버전이 gitlab14버전으로 오면서 postgresql 12.7 이상이 필요하다는 것을 보았고,

db upgrade를 통해 무슨 기능이 추가되었는지 히스토리를 찾아보았다.

Managing PostgreSQL extensions | GitLab

위 링크에서 확인해보니

postgresql 에 이런 확장 패키지가 존재하는 것이였다.

plpgsql 은 기본으로 설치가 되어있고,

pg_trgm, btree_gist 를 설치해야한다.

 

패키지설치

설치명령어는 굉장히 간단하다. postgresql 슈퍼유저로 접속한다음, 아래 명령어를 치면된다.

CREATE EXTENSION pg_trgm;
CREATE EXTENSION btree_gist;

 

자, 그럼 이 패키지가 무엇을 의미하는지 알아보자

 

PG TRGM

검색 속도를 높여주는 확장 패키지라고 한다.

Trigram 기반이기 때문에 3글자 이상부터 적용 가능하다(예시로 이런느낌..? like 'ABC%')

 

참고한 블로그 글을 보니

'검색어%' or '%검색어' 는 약 2-3배 빨라지고,

'%검색어%' 는 약 7-8배 빨라지는 처리속도의 차이가 났다고한다. 

 

타 프로젝트에서 oracle DB에 수천만 레코드에서 검색할 때 풀스캔을 타게 될 수 있어서 인덱싱을 추가한다. 정도로 인지하고 있었는데, postgreSQL 에서 이렇게 적용하는 건 처음알았다.

 

참고로 PG TRGM 인덱싱을 추가하는 방법은

CREATE INDEX idx_text ON 테이블 USING GIN("컬럼" gin_trgm_ops);

형태이다.

 

BTREE GIST

고유성을 적용할 수 있는 연산자 클래스라고 한다....(뭐라는거)... [gist를 사용한 B-tree]

postgresql 에서 지원하는 index type 종류는 B-Tree, GIN, GiST, SP-GiST, BRIN, HASH가 있는데

이런 패키지를 통해 더 성능이 좋아진 것 같다..

 

자세한 느낌은 예제를 통해 보자..

아래 예제는 btree의 예시로 42에 가장 가까운 항목 10개를 찾는 것이다.

CREATE TABLE test (a int4);
-- create index
CREATE INDEX testidx ON test USING GIST (a);
-- query
SELECT * FROM test WHERE a < 10;
-- nearest-neighbor search: find the ten entries closest to "42"
SELECT *, a <-> 42 AS dist FROM test ORDER BY a <-> 42 LIMIT 10;

뭐 그냥.. 비교조건에서 좋아 보인다.

 

아래 예제는 1개의 cage에 1마리의 동물만 들어갈 수 있다는 제약조건을 추가한 사례이다.

=> CREATE TABLE zoo (
  cage   INTEGER,
  animal TEXT,
  EXCLUDE USING GIST (cage WITH =, animal WITH <>)
);

=> INSERT INTO zoo VALUES(123, 'zebra');
INSERT 0 1
=> INSERT INTO zoo VALUES(123, 'zebra');
INSERT 0 1
=> INSERT INTO zoo VALUES(123, 'lion');
ERROR:  conflicting key value violates exclusion constraint "zoo_cage_animal_excl"
DETAIL:  Key (cage, animal)=(123, lion) conflicts with existing key (cage, animal)=(123, zebra).
=> INSERT INTO zoo VALUES(124, 'lion');
INSERT 0 1

케이지 123에 zebra가 있는 상태로 lion을 넣으면 error가 발생하고,

케이지 124에 lion을 넣으면 정상이다.

 

참고로 수퍼유저가 아닌 사용자도 설치할 수 있다.

 

내생각

DBA가 아니기 때문에 자세한 사항은 모른다.

하지만 릴레이션 데이터베이스에서 대량의 데이터를 컨트롤할 때 필요한 인덱싱 작업들을

각 데이터베이스 별로 어떻게 사용하는지 정도는 알면 좋을 것같다.

 

참고 페이지

1. pg_trgm

PostgreSQL에서 LIKE 검색 속도를 높여주는 pg_trgm 활용해보기 - YA-Hwang 기술 블로그

2. btree_gist

F.7. btree_gist