[Project] 프로젝트 삽질기50 (feat PostgreSQL 검색)

들어가며

NestJS와 TypeORM을 활용하여 프로덕트를 만들고 있습니다. 특정 유저를 멘션 하는 기능을 만들기 위해, 특정 유저의 닉네임을 검색하는 시스템을 구축해야 했습니다. 검색 시스템을 구축하기 위해 ElasticSearch를 활용하여 검색 기능을 개발하고 싶었습니다. 하지만 개발 마감 기한이 촉박했고, 대량의 데이터를 검색하는 시스템이 아니었기 때문에, 당장 ES 구축을 하는 것은 오버 엔지니어링이라 판단했습니다. 오버 엔지니어링하지 않으면서 보다 빠르고, 안정적으로 검색 시스템을 구축하려면 어떻게 해야 할까 고민하면서 postgresql로 full text search를 구현했습니다.

이번 글은 PostgreSQL을 활용한 검색 시스템 구축 과정과 전략 패턴을 활용한 코드 구성 과정에서 겪은 삽질의 경험이 담긴 글입니다. 

 

 

 

 

 

 

 

---

 

 

 

full text search 구현

검색 시스템을 구축하기 위해 ElasticSearch를 활용하여 검색 기능을 구현하려 했습니다. 하지만 ES를 전혀 모르는 상황에서, 공부하며 적용시키기에는 시간적 여유가 부족했습니다. 지금 상황에서는 현재 사용하고 있는 기술을 보다 잘 활용하여 문제를 해결하는 것이 좋다고 판단했습니다. 현재 PostgreSQL을 사용하고 있는데, RDB에서 full text search 구현이 가능한지 먼저 알아보고, 전혀 안될 경우엔 차선책을 고려해 봐야겠다고 생각했습니다. 그래서 먼저 PostgreSQL을 활용하여 full-text search를 구현할 수 있는지 알아봤습니다.

 

 

1. Like 검색

RDB에서 가장 대표적으로 검색에 활용하는 것은 Like 쿼리입니다. 현재 nickname에는 unique index를 설정해서 활용하고 있었습니다. 만약 Like 쿼리를 '검색어%'로 활용한다면, 인덱스를 활용하여 효율적으로 조회 쿼리가 실행될 수 있다고 생각했습니다. 

 

SELECT * FROM public."user"
WHERE nickname LIKE '호호%'

 

 

 

하지만 Like 쿼리의 실행계획을 분석한 결과 닉네임을 검색했을 때 Seq Scan(full table scan)이 일어나고 있었습니다. 혹시나 nickname 컬럼에 인덱스가 제대로 설정이 안 된 것인지 확인하기 위해 user 테이블의 인덱스 설정을 확인해 봤습니다.

 

 

SELECT * 
FROM pg_indexes 
WHERE tablename = 'user'

 

 

 

현재는 nickname 칼럼이 제대로 인덱스 설정이 되어 있는 것을 확인했습니다. 그럼 왜 풀테이블 스캔이 일어났던 것일까요? PostgreSQL Docs에서는 만약 PostgreSQL을 활용할 때 C locale을 사용하지 않는다면, 패턴 일치 쿼리의 인덱싱을 지원하기 위해 special operator class로 index를 생성해야 한다고 합니다. 

 

 

 

2. C locale

여기서 C locale이란, PostgreSQL에서 지원하는 다양한 로케일(언어, 국가 등의 지역 설정) 중 하나로, 특정 언어와 국가에 종속되지 않는 기본 로케일입니다. 즉, C locale에서는 특정 언어나 국가에 따라 다른 문자열 정렬 방식이나 대소문자 구분 등이 적용되지 않습니다.

 

 

 

 

만약 C locale을 사용한다면 위 쿼리의 출력 결과가 C로 나오는데, 현재 저는 C locale을 사용하지 않고 있지 않았습니다. 그럼 현재는 PostgreSQL에서 패턴 일치 검색을 위해 LIKE 연산자를 사용하면, 일반적으로 패턴 일치를 검색하기 위해 풀 테이블 스캔을 수행해야 했습니다. 그럼 위에서 살펴본 것처럼 special operator class로 index를 생성한다면 nickname 컬럼의 데이터를 빠르게 조회할 수 있다고 생각했습니다.

 

 

 

3. Operator class

그럼 활용해야 하는 Special operator class는 PostgreSQL에서 인덱스를 사용하기 위해 필요한 operator class 중 하나입니다. 여기서 operator class란 인덱스를 효과적으로 사용할 수 있도록 연산자를 구성하는 방법을 지정하는 일종의 규칙 집합입니다. 예를 들어 PostgreSQL에서 문자열 데이터 타입에 대한 인덱스를 생성할 때, 해당 문자열이 일치하는지 확인하기 위해 일반적으로 '=' 연산자를 사용합니다. 하지만 대소문자를 구분하지 않는 경우, '=' 연산자를 사용하면 정확한 결과를 얻을 수 없으므로, 'ILIKE'와 같은 대소문자를 무시하는 연산자를 사용해야 합니다. 이때, PostgreSQL에서는 대소문자를 무시하는 'ILIKE' 연산자를 정의하고, 이를 사용하여 operator class를 생성합니다. 

 

operator class의 대표적인 예시로는 int4_ops와 text_ops 등이 있습니다.

 

int4_ops는 정수형 데이터 타입에 대한 연산자 클래스입니다. 이 클래스는 =, <, >, <=, >=, <> 등의 연산자를 포함합니다.

text_ops는 문자열 데이터 타입에 대한 연산자 클래스입니다. 이 클래스는 =, <, >, <=, >=, <> 등의 연산자를 포함합니다. text_ops 클래스는 일반적인 문자열 검색을 위해 다양한 operator class를 제공하는데, 대표적인 것으로 pg_trgm, pg_bigm, pg_catalog.english 등이 있습니다.



 

 

4. Special operator class

다시 Special operator class로 돌아와서, Special operator class는 특정 타입의 데이터를 인덱싱 하기 위해 사용되며, 해당 데이터 타입에 대한 인덱싱 방법을 정의합니다. 특히, Full Text Search에서는 텍스트 데이터를 인덱싱 하기 위해 tsvector와 tsquery 타입을 사용하며, 이를 위해 tsvector_ops와 tsquery_ops operator class가 제공됩니다. 이와 마찬가지로, 패턴 일치 검색에서는 text_pattern_ops operator class가 제공됩니다.

 

 

그럼 special operator class를 활용하여 index를 생성하려면 어떻게 해야 할까요? 

 

 

CREATE INDEX test_index ON test_table (col text_pattern_ops);

 

 

위와 같이 구성한다면 B-tree 인덱스를 설정할 수 있습니다. 저는 user 테이블에 nickname 컬럼이 존재하기 때문에 아래와 같이 Index를 생성했습니다.

 

 

 

CREATE INDEX test_index ON user (nickname text_pattern_ops);

 

 

 

 

 

인덱스를 추가한 후, 쿼리를 했을 땐 확실히, 쿼리 실행 속도가 현저히 증가했습니다. user 테이블에 10만 개의 데이터가 있을 때, 인덱스 설정 전의 유저의 닉네임 검색 시간은 16.5ms였다면, 인덱스를 설정한 다음엔 0.026ms 시간이 소요됐습니다.

 

 

 

 

SELECT * FROM public."user"
WHERE nickname LIKE '%호호%'

 

 

 

 

 

하지만, %검색어% 처럼 full text search를 검색하는 경우엔 여전히 seq scan이 발생하고 있었습니다.

 

 

 

 

SELECT * FROM public."user"
WHERE nickname LIKE CONCAT('호호', '%')

 

 

 

 

그리고 CONCAT 함수와 함께 사용할 경우에도 Seq Scan이 발생하고 있었습니다.

 

B-tree 인덱스는 데이터의 삽입과 삭제에 대해 항상 균형 트리를 유지하는 자료 구조를 이용해, 순차 비교에 대해 최악의 경우에도 탐색의 시간복잡도가 O(log N)이 되도록 합니다. 한편, B-tree 인덱스는 인덱스를 적용하는 컬럼의 값을 변형하지 않고 원래의 값을 이용합니다. 따라서 = 연산과 같은 값 자체에 대한 탐색에는 효과적입니다. 또한 LEFT-TO-RIGHT 방식으로 동작하는 B-Tree 인덱스의 특성상 LIKE를 통한 검색은 '검색어%'만 인덱스를 활용할 수 있습니다. 하지만 %LIKE% 연산과 같이 검색어가 데이터 값에 포함되었는지 확인하는 것에는 인덱스가 적용되기 어렵습니다. 그리고 B-tree 인덱스는 짧은 문자열 혹은 숫자 타입의 컬럼에서 효과적인데, Text 타입처럼 컬럼에 대량의 문자열의 경우 B-tree 인덱스는 효과적이지 못합니다. 

 

실험 결과 B-tree index로는 검색 시스템을 구축하기엔 부족함을 느꼈습니다. B-tree 인덱스가 아닌 다른 방식으로 문제를 해결해야 했고, 이때 찾은 것이 바로 GIN Index였습니다. Gin Index를 활용하여 Index를 설계하는 방식에 대해 알아보겠습니다.

 

 

 

 

 

 

 

 

 

---

 

GIN Index

GIN Index는 Full text 검색 속도를 높이는 데 사용 가능한 index입니다. GIN(Generic Inverted Index) index는 B-tree index와는 다른 구조를 가지고 있습니다. GIN index는 역 인덱스(inverted index) 구조를 사용하여, 키워드(keyword)나 토큰(token)과 같은 작은 단위의 단어(word)를 인덱싱 합니다. GIN은 항상 항목 값이 아닌 키를 저장하고 검색합니다. 이러한 작은 단위의 단어는 텍스트 검색을 위해 자주 사용되는데, 이를 효과적으로 인덱싱 하면 텍스트 검색의 속도를 높일 수 있습니다.

 

B-tree index는 데이터를 정렬된 상태로 저장해서, 키-값 쌍을 효과적으로 탐색하는 데 사용되는 반면, GIN index는 역 인덱스 구조를 사용하여 단어를 인덱싱 하고, 이를 사용하여 데이터를 조회합니다. GIN Index는 B-tree index보다 키워드 검색과 같은 텍스트 검색에서 훨씬 효과적이며 대량의 텍스트 데이터를 처리할 때 성능 향상을 제공합니다. 

 

GIN Index를 활용하여 인덱스를 생성할 땐 build-in operator class인 array_ops, tsvector_ops, jsonb_ops, jsonb_path_ops 외에도 확장 모듈인 pg_trgm에서 텍스트 row에 index를 생성할 수 있는 GIN 및 GiST index operator class를 제공하는데, 이 Class들은 LIKE, ILIKE, ~, ~*, = 연산자에 대한 trgm 기반 index 검색을 추가적으로 지원합니다. pg_trgm에 대해 자세히 알아보겠습니다.

 

 

 

---

pg_trgm

postgresql 추가모듈 중 pg_trgm을 이용하여 '%문자열%' like 검색 시 인덱스 스캔을 실행할 수 있습니다. pg_trgm 모듈은 유사한 문자열을 빠르게 검색할 수 있는 색인 연산자 클래스뿐만 아니라 trigram(문자열에서 가져온 세 개의 연속 문자 그룹) 일치를 기반으로 영숫자 텍스트의 유사성을 결정하는 함수와 연산자를 제공합니다.

참고로 pg_trgm은 문자열에서 trigram을 추출할 때 단어가 아닌 문자 (영숫자가 아닌 문자)를 무시합니다. 각 단어는 문자열에 포함된 트라이 그램 세트를 결정할 때 접두사 두 개와 접미사 한 개가 있는 것으로 간주됩니다. 예를 들어, 문자열 “cat”의 트라이 그램 세트는 “c”, “ca”, “cat”및 “at”입니다. 문자열 “foo | bar”의 트라이 그램 세트는 “f”, “fo”, “foo”, “oo”, “b”, “ba”, “bar”및 “ar”입니다.

 

pg_trgm 모듈은 GiST 및 GIN 인덱스 연산자 클래스를 제공하여 매우 빠른 유사성 검색을 위해 텍스트 열에 인덱스를 생성할 수 있습니다. 이러한 인덱스 유형은 위에 설명된 유사성 연산자를 지원하며 LIKE, ILIKE, ~ 및 ~ * 쿼리에 대한 trigram 기반 인덱스 검색을 추가적으로 지원합니다. 하지만 이 인덱스는 동등성이나 간단한 비교 연산자를 지원하지 않으므로 일반 B-tree 인덱스도 필요할 수 있습니다.

 

이런 pg_trgm은 문자열을 3글자 (trigram) 씩 쪼개는 방식으로 동작합니다. 그래서 단어를 검색할 경우 최소 3글자 이상부터 인덱스를 통한 탐색이 Full Scan 대비 효율적이라는 한계점이 있습니다. 하지만 3글자 미만에 대한 검색은 인덱스 탐색 비용이 증가하여 full table scan 비용을 초과합니다. pg_trgm을 활용하면 검색을 할 때 반드시 3글자를 입력해줘야 하는 불편함이 있으니, 2글자로 검색을 진행한다면 빠르게 검색을 할 수 없다는 문제가 있습니다.

 

만약 2글자로 검색을 진행했을 때, 빠르게 검색을 할 수 있으려면 어떻게 해야 할까 알아보던 중 pg_bigm 모듈에 대해 알 수 있었습니다. pg_bigm 모듈에 대해 알아보겠습니다.

 

 

 

 

---

pg_bigm

pg_bigm 모듈을 활용하면 빠른 전체 검색 텍스트 검색을 위해 2-gram 인덱스를 만들 수 있습니다. 여기서 2-gram 인덱스는 문자열의 연속된 2개 문자마다 인덱스 항목을 만드는 것을 의미합니다(bigm이라는 모듈의 이름 또한 bi-gram의 약자입니다). pg_bigm 모듈은 PostgreSQL 13 버전이라면, 13.2 이상에서 사용할 수 있는데, 현재 AWS Aurora PostgreRDS 13.9 버전을 사용하고 있으므로 해당 모듈을 활용할 수 있었습니다.

 

3-gram(trigram) 모델을 이용한 전체 텍스트 검색 기능을 제공하는 pg_trgm과 이를 기반으로 하는 pg_bigm은 다음과 같은 차이점이 있습니다.

 

 

기능	pg_trgm	pg_bigm
전체 텍스트 검색에 필요한 최소 단어수	3단어	2단어
사용 가능한 인덱스	GIN, GiST	GIN
사용 가능한 검색 연산자	like, ilike	like
한국어 지원 여부	X	O
1-2자 키워드로 전체 텍스트 검색시 성능	느림	빠름
유사성 검색	O	O (1.1버전부터)
최대 인덱스 컬럼 사이즈	~228MB	~102MB

출처: https://jojoldu.tistory.com/590

 

 

 

여러 가지 차이가 있지만, pg_trgm과 pg_bigm의 큰 차이는 한국어 인덱스 지원 여부입니다. 기존의 pg_trgm은 한국어가 지원 안되었습니다. 그래서 한글 텍스트가 포함된 컬럼의 전체 텍스트 검색 성능 향상을 위해서는 pg_bigm을 사용해야만 했습니다. 하지만 pg_bigm의 가장 큰 단점은 알파벳의 대/소문자를 동일하게 보지 못합니다. 즉, ilike로 대/소문자를 모두 묶어서 검색하던 방식으로는 pg_bigm을 사용할 수는 없습니다. 

 

 

 

pg_bigm 활용

pg_bigm을 사용하고 싶다면, 먼저 pg_bigm 확장 모듈을 설치한 후, pg_trgm을 사용하는 방법과 유사한 방법으로 GIN 인덱스를 생성하면 됩니다. 다음은 pg_bigm을 사용하여 GIN 인덱스를 생성하는 예시입니다. 

 

SELECT * FROM pg_extension;
-- pg_bigm이 설치되어 있으면 아래 DDL은 점프! 만약 설치가 안되어있다면 extension 설치

CREATE EXTENSION pg_bigm;

CREATE INDEX user_nickname_gin ON public."user" USING gin (nickname gin_bigm_ops);

-- 인덱스 잘 생성됐는지 확인하고 싶다면 아래 DML 설정
SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'user';

 

위 예시에서 gin_bigm_ops는 pg_bigm 모듈에서 제공하는 연산자 클래스입니다. 이 클래스는 GIN 인덱스에서 bigram 검색을 수행할 때 사용됩니다.

 

 

SELECT * FROM public."user"
WHERE nickname LIKE '%호호%'

 

 

그럼 인덱스를 설정한 후 위의 쿼리를 실행하면 어떻게 동작할까요? 

 

 

 

 

pg_bigm을 활용하여 GIN 인덱스를 생성한 결과 17.4ms에서 0.013ms로 성능 개선이 된 것을 확인할 수 있습니다. 효율적으로 Full Text Search 쿼리가 동작하는 것을 확인할 수 있었습니다. 

 

하지만 Gin index는 B-tree index와 비교하여 인덱스의 크기가 크고, 쿼리 처리 속도가 느릴 수 있습니다. 따라서 GIN Inex는 텍스트 검색과 같은 특수한 용도에 적합하며, 모든 종류의 데이터에 대해 사용하기에는 적합하지 않을 수 있습니다.

 

고려해야 할 점은, GIN index는 역 인덱스 구조이다 보니, indexing 된 항목에서 추출된 각 key에 대해 heap row를 삽입하거나 업데이트하면 index에 많은 삽입이 발생할 수 있어서 느린 경향이 있고, 디스크 공간을 많이 사용할 수 있습니다. 이는 삽입되는 단일 row 당 여러 index 항목을 포함하는 경우가 많아 단일 row로 인해 10개 또는 최악의 경우 100개의 index 항목이 업데이트될 수 있습니다. 이 때문에 insert, update 쿼리 실행 시 처리 시간이 다소 걸릴 수 있으나, 이러한 문제는 autovacuum을 사용하는 등의 방법으로 최소화할 수 있습니다.

 

만약 insert, update 작업이 적게 발생하고, select가 많이 실행되거나 select의 성능이 중요한 프로젝트에서는 GIN index 사용을 고려해 볼 만하다고 생각합니다. 하지만 디스크 공간, 메모리 용량 등의 제약으로 인해 GIN index를 사용할 수 없는 상황이라면 Full Text Search in Milliseconds with Rails and PostgreSQL처럼 다른 대안을 모색하는 것도 방법이라 합니다. 

 

 

Full Text Search in Milliseconds with Rails and PostgreSQL

 

 

 

 

 

 

 

---

 

 

 

 

 

 

 

 

 

마치며

공부를 하며 그동안 아주 기초적인 것들을 제대로 공부하지 않았다는 것을 깨닫습니다. 좋은 기술을 배우는 것도 좋지만, 기술의 기반이 되는 기초적인 지식을 먼저 쌓는 것이 중요하다는 것을 다시금 깨달았습니다. 기초가 튼튼한 개발자가 되고 싶습니다.

 

 

 

 

 

 

 

---

 

 

 

출처

pg_bigm 1.2 Document

Amazon Aurora PostgreSQL 에서 pg_bigm 모듈사용하기

PostgreSQL GIN 인덱스를 통한 LIKE 검색 성능 개선

PostgreSQL GIN INDEX

pg_trgm 설치하기

Postgres를 이용한 full-text search

PostgreSQL - GIN

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

pg_trgm - RastaLion's IT Blog

MySQL - 확장 검색 - 전문검색 FullText Search - Real MySQL 8.0

MySQL FullText Search 전문검색 기능 (2) - N-gram - MeCab (은전한닢)

pg_bm25: Elastic-Quality Full Text Search Inside Postgres - ParadeDB

 

Full Text Search를 이용한 DB 성능 개선 일지