유저 닉네임 검색 속도 개선 - Postgresql 문자열 검색 빨리하기

먼저 성과부터 보여드립니다.

성과

개선 전

select * from user_table where nickname ilike '코%' order by id desc limit 100

postgresql 의 explain analyze 해본 결과

cost : 114404.84
Execution Time : 2571.743ms
정직한 Parellel Seq Scan

개선 후

select * from user_table where nickname ilike '코%' order by id desc limit 100

postgresql 의 explain analyze 해본 결과

cost : 743
Execution Time : 0.8ms

문제

문제 상황

유저간의 닉네임을 검색해야 했는데, 이 검색 속도가 느렸음

유저는 몇명정도? -> X00만명
검색이 어떻게 되어야 함? -> "코" 를 검색했을때, "코"-> "코*", "코" 순서로 나와야 함

개선사항 1

처음에는 코 로 죄다 검색해서, 코 -> 코* -> 코 순서로 정렬했었는데,
코 로 검색하는게 오래걸리다보니
코, 코* 만 검색해서 100개 이상의 결과가 나오면,
코 는 더이상 검색하지 않는 것으로 수정했다.

개선사항 2

근데 어쨌든 코* 검색이 오래걸려서
이걸 해결 해야만 했다.

PostgreSQL 문자열 검색 속도 높이기

(실패) Full Text Search

일단 지금 필요한 검색보다 조금 오버스펙이다.
satisfies, satisfy 요런 단어의 형태 변화까지 잡아낼 수 있는 방식.

postgreSQL은 Full Text Search 를 위해,
2가지 자료형을 제공한다

1. tsvector
2. tsquery

(실패) tsvector 컬럼 만들기

ALTER TABLE user_table ADD COLUMN nickname_ts tsvector;

ALTER TABLE user_table ADD COLUMN nickname_ts tsvector
    GENERATED ALWAYS AS (to_tsvector('simple', nickname)) STORED;

먼저 테스트 디비에 위 쿼리를 날려봤는데, 한세월이 걸렸다.
운영중인 디비에 적용시키기에는 너무 오래 걸리는 쿼리.

이렇게 만드는데 오래걸리는걸로 미루어보아,
row가 새로 생기거나, 유저가 닉네임을 변경해서 update 가 될때도 엄청 느릴거라는 판단을 했다
(simple은 형태소 없이 진행하는거)

(실패) tsvector 인덱스 만들기

CREATE INDEX ON user_table USING GIN (to_tsvector('simple', nickname));

SELECT *
FROM   user_table
WHERE  to_tsvector('simple', nickname) @@ to_tsquery('simple', 'a');

select to_tsvector('simple','A_paper') 
# 결과 -> 'a':1 'paper':2

select to_tsquery('simple','A_paper')
# 결과 -> 'a' <-> 'paper'

이거 해보고 해결 한 줄 알았다.
그런데 'a'가 포함된 닉네임은 실제로는 몇만개인데 반해,
위 to_tsquery 로 a를 검색했을때는 몇백개만 나왔다.

이게 매 문자마다 잘려지는게 아니라, 뭔가 알아서 잘려졌다.
그래서 특히 한글일때 원하는 결과가 안나왔다.

(성공) pg_trgm 익스텐션

CREATE EXTENSION pg_trgm;
CREATE INDEX user_table ON user_table USING gin (nickname gin_trgm_ops);

익스텐션 깔고, gin 인덱스 만드니까,
실행시간 2초대에서, 1ms 로 줄어드는 기적같은 일이 벌어졌다.

디비 이미지 세션 수가 감소했음..

tri 알고리즘은 알고리즘 책 속에만 존재하는 줄 알았는데,
이게 이런 효자같은 녀석이었다니,

최근에는 pg 익스텐션들이 해답이었던 경우가 많았군
pg_repack도 요긴하게 썼었는데 말이지..

참고자료

• text search 관련 함수 공식문서 : https://www.postgresql.org/docs/current/functions-textsearch.html