Postgresql 14
특정 API의 성능이 너무 느리게 나와서 속도를 측정해보니 하나의 쿼리 수행에 무려 60초나 걸리고 있었다.
이 쿼리는 2개의 테이블을 inner join 하여 특정 컬럼을 index에 태워 보내며 동작한다.
예를 들어 Students(id, name, department), Class(S_id, number) 라는 테이블이 존재할 떄
-- 쿼리
SELECT
*
FROM
Students inner join Class on Students.id = Class.S_id
WHERE
name in ('Kim', 'Jin', 'Lee')
and split_part(department, '_', 2) in ('CS', 'PM', 'FL')
and number in ('456', '223', '739')
-- Index
CREATE INDEX idx_01 ON Students USING BTREE (name, department)
name과 department는 Btree로 다중 컬럼 인덱스를 생성하여 인덱스를 사용하고 했다. 아쉽게도
name은 인덱스를 정상적으로 타지만, department는 인덱스를 타지 않았다.
만약
SELECT
*
FROM
Students inner join Class on Students.id = Class.S_id
WHERE
name in ('Kim', 'Jin', 'Lee')
and department in ('CS', 'PM', 'FL')
and number in ('456', '223', '739')
이런 구조였다면 무난하게 인덱스를 탈 수 있지만, split_part
를 사용하여 원본에서 변형되었기 때문에 name만 인덱스를 타고 department는 인덱스를 탈 수 없게 됐다.
department가 인덱스를 타지 못하기 때문에 쿼리의 전체 실행속도가 매우 느려졌다. 그러므로 가장 좋은 방법은 department를 인덱스에 같이 태워 보내는 방법이다.
처음에는 like
를 사용하여 like '%CS'
와 같은 방법을 사용 했다.
하지만 like에서 인덱스를 태울 수 있는 경우는 department like 'CS%'
와 같이 특정 문자열이 먼저 앞으로 나오고 뒤에 %
가 붙어야 한다. 그래서 이 방법은 사용하지 못 했다.
department의 데이터 구조는 (col1 의 데이터)_(TEXT)
의 구조를 지니고 있다. 쉽게 말하면
name | department |
---|---|
Kim | Kim_CS |
Lee | Lee_CS |
Jin | Jin_PM |
Jadon | Jadon_FL |
이런 구조를 지니고 있다. 그렇지만 기존의 서버에서는 department의 CS
, PM
, FL
과 같이 _
뒤에 있는 정보만 가지고 있었다. 그래서 해당 데이터를 데이터베이스에 저장된 Kim_CS
와 같이 형태를 맞추면 split_part를 사용하지 않고 바로 쿼리에 작성할 수 있으므로 해결할 수 있다고 생각했다.
하지만 해당 name과 department를 매핑 하는 과정이 추가적으로 필요했고, 과정 자체도 프론트와 서버 모두 손봐야 하는 작업이어서 진행하지 않았다.
해당 쿼리에서 col2가 인덱스를 타지 못 해 느려지기 때문에 해당 컬럼을 제외하고 쿼리를 수행한다.
그 뒤에 받아온 데이터를 Java의 Stream과 filter를 사용하여 처리할 수 있다. 이 과정은 쿼리 수행을 단축 시킬 수 있지만, 근본적인 문제를 해결할 수 는 없었다.
그리고 쿼리에서 받아온 데이터를 다시 한 번 순회해야 하므로 추가 시간이 소요됐다.
서버에서 처리하기 1
을 보면 해당 데이터는 정규화 3NF를 만족하지 않음을 알 수 있다. 왜냐하면 department를 알면 name은 자동으로 알 수 있게 되기 때문이다. 그래서 해당 department에서 _
뒤의 값만 저장하게 된다면, 서버에서 쿼리를 날릴 때 문제없이 인덱스를 타게 될 것이다.
하지만 이는 프론트엔드에서 department의 값만 불러서 대시보드에 올려 차트로 볼 때 해당 컬럼만으로 어떤 name인지 알 수 있어야 했다. 그래서 데이터 구조 자체를 바꾸는 것은 불가능 했다.
like
를 활용한 인덱스를 찾던 중 GIN 을 알게 됐다.
간단히 정리하면, GIN은 역색인 구조를 활용하여 특정 keyword가 들어간 데이터를 찾아낸다. 그렇기 때문에 특정 단어가 포함된 문자열을 빠르게 찾을 수 있다.
GIN을 사용하면 like '%CS%
와 같은 조건도 인덱스를 타게 할 수 있다.
이는 trgm을 활용하여 가능하게 만든 것이다.
trgm은 trigram을 활용하여 인덱스를 생성하여 데이터 내에 특정 문자열이 들어있는지 빠르게 확인할 수 있게 한다.
이를 사용하기 위해
-- pg_trgm extension을 추가
CREATE extension pg_trgm;
-- index 생성
CREATE INDEX gin_idx01 ON Students USING GIN (name gin_trgm_ops, department gin_trgm_ops);
다음과 같이 인덱스를 생성할 수 있다. trgm의 경우 기본으로 제공 되지 않아 extension을 추가해야 한다.
분명 나는 GIN으로 모든 문제를 해결 할 수 있을 것이라 생각했다. 왜냐하면 이렇게 하게되면 두 컬럼 모두 자연스럽게 인덱스를 태울 수 있으니 말이다. 하지만 결과는 생각 처럼 나오지 않았다.
우선 기존에 존재하던 index는 name, department를 모두 Btree 인덱스로 만들어 사용 했다. 그래서 name과 departments를 인덱스를 GIN 인덱스로 변경한 뒤 실행을 해봤다.
하지만 결과는 예상과는 달랐다. 우선 기존에 Btree로 만든 index에서 department만 제외한 인덱스와 GIN으로 새로 만든 인덱스 2개를 다음과 같이 만들었다.
-- Btree
CREATE INDEX btree_idx01 ON Students USING BTREE (name);
-- GIN
CREATE INDEX gin_idx01 ON Students USING GIN (name gin_trgm_ops, department gin_trgm_ops);
위와 같은 두 개의 인덱스를 만들고 EXPLAIN ANALYZE
를 통해 쿼리의 실행 계획을 확인하며 분석하니 GIN 인덱스를 타는 것이 아닌 Btree 인덱스를 타고 있었습니다.
분명 Postgresql에서 GIN 인덱스를 선택할 것이라고 예상했는데 아니었습니다.
우선 위에서 제시한 아이디어 중 GIN이 가장 마음에 들고 합리적인 해결책이라고 생각했는데, 마음대로 동작하지 않아서 아쉬움이 컸다. 또한 사수분께서 인덱스를 변경하면 다른 API에서 문제가 발생할 수 있으니, 서버단에서 처리하는 것이 좋아보인다고 말씀해주셨다. 그래서 결국 서버단에서 department를 제외한 데이터를 가져와 department 부분만 stream, filter를 사용하여 걸러냈다.
해결하긴 했지만 뭔가 찜찜한 기분이다...
https://medium.com/vuno-sw-dev/postgresql-gin-%EC%9D%B8%EB%8D%B1%EC%8A%A4%EB%A5%BC-%ED%86%B5%ED%95%9C-like-%EA%B2%80%EC%83%89-%EC%84%B1%EB%8A%A5-%EA%B0%9C%EC%84%A0-3c6b05c7e75f
https://www.postgresql.org/docs/current/indexes-bitmap-scans.html
이런 고민이 있었군요.