postgresql로 만들어진 테이블에서
음식의 이름을 검색하는 기능을 구현해야 했다.
유저가 특정 키워드를 입력하면 그 키워드가 포함된 음식을 반환하는 방식이다.
SQL의 LIKE 문법과 와일드 카드(%)를 사용하면
문자열을 포함한 결과를 조회할 수 있지만,
SELECT *
FROM foods
WHERE name LIKE '%키워드%';키워드 앞에 와일드 카드(%)를 붙이면
name 필드의 인덱스를 사용할 수 없다.
검색에서 인덱스를 타지 않으면 full table scan을 하게되고,
테이블의 크기가 클 수록 조회 쿼리의 부하가 커진다.
LIKE 검색 시 인덱스를 사용하기 위해 찾은 방법은
pg_trgm 트라이그램(trigram)이라는 postgresql 확장 기능을 사용하는 것이다.
pg_trgm 이란
트라이그램이란 문자열에서 추출할 수 있는 연속적인 3개의 문자를 의미한다.
pg_trgm의 원리는 2개의 문자열을 비교할 때,
두 문자열들이 얼마나 많은 트라이그램을 공유하는지에 따라 유사도를 비교하는 것이다.
실제로 트라이그램이 어떻게 되는가 하면
"cat" 의 경우 "c", "ca", "cat", "at"
3개의 연속적인 문자라고 했는데,
왜 "c", "ca", "at" 가 포함되는가 싶겠으나,
pg_trgm은 문자의 앞에 최대 두개의 공백, 뒤에 최대 하나의 공백이 포함된 경우도 고려한다.
다만 3개의 연속적인 문자 단위로 판단되기 때문에
중간 위치의 한 글자, 두 글자로는 검색이 불가능하다.
pg_trgm의 기능
pg_trgm의 기능은 크게 3가지이다.
필요에 따라 활용할 수 있다.
1. pg_trgm 내장 함수와 연산자
pg_trgm 확장을 설치하면 바로 사용할 수 있는 함수들과 연산자다.
| 함수 | 반환값 타입 | 설명 |
| similarity(text, text) | real | 두 문자열의 유사도를 반환한다. 결과값은 0부터 1까지이고, 0은 전혀 유사하지 않음, 1은 동일함을 의미한다. |
| show_trgm(text) | text[] | 문자열에 포함된 모든 트라이그램을 반환한다. 주로 디버깅 시에만 사용된다. |
| set_limit(real) | real | 유사성 연산자 %가 사용하는 유사성 판별 기준 수치를 설정한다. 0과 1 사이의 값을 입력할 수 있고 default 값은 0.3이다. 입력한 값을 다시 반환한다. |
| show_limit() | real | 유사성 연산자 %가 사용하는 유사성 판별 기준 수치를 반환한다. |
| 연산자 | 반환값 타입 | 설명 |
| text % text | boolean | 두 문자열의 유사도가 set_limit()로 설정한 유사성 판별 기준 수치 이상일 때 true를 반환한다. |
2. 인덱스
pg_trgm은 빠른 문자열 유사 검색을 위한 GiST, GIN 인덱스를 지원한다.
이 인덱스들은 위의 % 유사도 연산자를 지원한다.
다만, 다른 연산자는 지원하지 않기 때문에 Btree 인덱스도 함께 필요할 수 있다.
인덱스 사용법은
테이블을 생성 후 인덱스를 생성하는데, USING 문법을 사용하여
인덱스의 종류를 지정해준다.
CREATE TABLE test_trgm (t text);
CREATE INDEX trgm_idx ON test_trgm USING gist (t gist_trgm_ops);
또는
CREATE INDEX trgm_idx ON test_trgm USING gin (t gin_trgm_ops);이후 t 컬럼에 대해 인덱스를 사용하여 문자열 유사도 검색을 할 수 있다.
SELECT t, similarity(t, 'word') AS sml
FROM test_trgm
WHERE t % 'word'
ORDER BY sml DESC, t;이렇게 검색하면 'word'와 충분히 유사한 결과들이
유사도가 높은 순부터 낮은 순까지 모두 조회된다.
인덱스를 사용하기 때문에 큰 크기의 데이터셋에 대해서도 빠른 검색이 가능하다.
GIST와 GIN 인덱스의 사용은 경우에 따라 다른데,
일반적으로 GIN 인덱스는 조회가 빠르고, 대신 build와 update가 느리며,
GIST는 그 반대이다.
따라서 자주 업데이트 되는 테이블에서는 GIST를,
변동이 거의 없는 테이블에서는 GIN을 사용하는 것이 좋다.
3. 텍스트 검색 통합 (Text Search Integration)
트라이그램은 Full Text Search(FTS, 문서 고급 검색 기능)과 결합되어 사용될 수 있다.
특히 FTS 메카니즘으로 검색되지 않는 약간의 오타가 있는 단어를 검색할 때 유용하다.
먼저 원하는 문서 필드가 포함된 테이블의 보조 테이블을 만든다.
보조 테이블에는 문서 내 모든 unique한 단어(words)가 포함된다.
CREATE TABLE words AS SELECT word FROM
ts_stat('SELECT to_tsvector(''simple'', bodytext) FROM documents');예시에서 documents 테이블의 bodytext가 우리가 검색하길 원하는 필드이다.
이후 트라이그램 인덱스를 생성한다.
CREATE INDEX words_idx ON words USING gin(word gin_trgm_ops);이제 유저가 오타가 있는 단어를 입력할 때 교정된 단어를 제안할 수 있다.
애플리케이션에 pg_trgm 적용하기
작업 중인 서비스가 NestJS와 Prisma를 사용하기에 그에 따른 사용 방식을 소개한다.
우선 schema.prisma 파일에서 previewFeatures, extensions 기능을 활성화 해주어야 한다.
// schema.prisma
generator client {
provider = "prisma-client-js"
previewFeatures = ["postgresqlExtensions"]
}
datasource db {
provider = "postgresql"
url = env("DATABASE_URL")
extensions = [pg_trgm]
}
검색 기능을 사용할 테이블의 필드에 GIN 인덱스를 추가한다.
model Foods {
id String @id @db.VarChar(30)
name String @db.VarChar(100)
@@index([name(ops: raw("gin_trgm_ops"))], map: "idx_name_trgm", type: Gin)
@@map("foods")
}
이렇게 설정하면 아래와 같이 prisma migration 쿼리 파일이 생성된다.
-- CreateExtension
CREATE EXTENSION IF NOT EXISTS "pg_trgm";
-- CreateTable
CREATE TABLE "foods" (
"id" VARCHAR(30) NOT NULL,
"name" VARCHAR(100) NOT NULL,
CONSTRAINT "foods_pkey" PRIMARY KEY ("id")
);
-- CreateIndex
CREATE INDEX "idx_name_trgm" ON "foods" USING GIN ("name" gin_trgm_ops);
애플리케이션에서 조회 쿼리를 실행해보았을 때
// foods.service.ts
const searchedFoods = await this.prisma.$queryRaw<Foods[]>`
EXPLAIN ANALYZE
SELECT * FROM foods
WHERE name LIKE '%${text}%'
LIMIT ${pageSize}
OFFSET ${page * pageSize}
`;아래와 같이 GIN 인덱스를 사용하는 것을 확인할 수 있다.
[
{
'QUERY PLAN': 'Limit (cost=12.03..12.04 rows=1 width=79) (actual time=0.171..0.172 rows=0 loops=1)'
},
{
'QUERY PLAN': " Recheck Cond: ((name)::text ~~ '%$1%'::text)"
},
{ 'QUERY PLAN': ' Rows Removed by Index Recheck: 87' },
{ 'QUERY PLAN': ' Heap Blocks: exact=31' },
{
'QUERY PLAN': ' -> Bitmap Index Scan on idx_name_trgm (cost=0.00..8.01 rows=1 width=0) (actual time=0.043..0.043 rows=87 loops=1)'
},
{
'QUERY PLAN': " Index Cond: ((name)::text ~~ '%$1%'::text)"
},
{ 'QUERY PLAN': 'Planning Time: 0.357 ms' },
{ 'QUERY PLAN': 'Execution Time: 0.220 ms' }
]
+ pg_trgm extension 설치 에러
prisma migrate deploy를 실행했을 때 에러가 발생했다.
Database error code: 42704
Database error:
ERROR: operator class "gin_trgm_ops" does not exist for access method "gin"
DbError { severity: "ERROR", parsed_severity: Some(Error), code: SqlState(E42704), message: "operator class \"gin_trgm_ops\" does not exist for access method \"gin\"", detail: None, hint: None, position: None, where_: None, schema: None, table: None, column: None, datatype: None, constraint: None, file: Some("indexcmds.c"), line: Some(2139), routine: Some("ResolveOpClass") }gin이 없어서 gin 인덱스를 생성할 수 없다는 내용이었다.
CREATE EXTENSION 쿼리가 실행되었는데도 pg_trgm이 정상적으로 설치되지 않은 것이다.
sql client(DBeaver)로 직접 DB에 pg_trgm extension 설치 쿼리를 실행했으나,
이미 익스텐션이 존재하여 실행되지 않는다는 문구만 노출됐다.
CREATE EXTENSION IF NOT EXISTS "pg_trgm";
한 DB 내에 여러 스키마를 두고 사용 중이었는데,
스키마를 지정하여 CREATE EXTENSION을 실행해도 동일하게
이미 익스텐션이 존재한다는 문구만 노출됐다.
CREATE EXTENSION IF NOT EXISTS "pg_trgm" WITH SCHEMA schema_name;
trgm이 정상동작하는지 확인하기 위해
select show_trgm('hello');
를 실행했을 때도 정상 동작하지 않았다.
(주로 참고했던 github issue 링크)
구글링 했을 때 나온 해결책 중 남은 건 pg_trgm을 DROP 했다가
다시 CREATE 하는 방법 뿐이었다.
이미 trgm을 사용하는 인덱스가 존재하는 등 삭제하기 어려운 상황인 경우
에러가 반환되기 때문에
애플리케이션에서 trgm 함수를 직접 사용 중일수도 있다는 약간의 위험을 무릅쓰고
DROP 익스텐션을 실행 후 다시 CREATE 익스텐션을 실행했다.
DROP EXTENSION pg_trgm;
CREATE EXTENSION IF NOT EXISTS "pg_trgm";
이후 정상적으로 pg_trgm을 사용할 수 있었다.
그런데 그 후에 다시 다른 스키마에서 trgm 인덱스를 사용하려고 하자
다시 처음과 동일한 에러가 발생했다.
CREATE EXTENSION을 하려고해도 역시나 이미 존재한다는 에러만 발생했다.
이미 DROP 과 CREATE을 했고
스키마를 지정하지 않으면 DB 내에 전역으로 설치되어야 하는데,
이해할 수 없었다.
혹시나 sql client(DBeaver)의 문제일까 싶어서
익스텐션을 DROP 후 터미널에서 직접 psql 접속하여
CREATE EXTENSION 쿼리를 실행했더니
스키마에 관계없이 정상적으로 trgm을 사용할 수 있었다.
DBeaver가 postgresql 쿼리를 실행할 때
psql과 다르게 실행하는 부분이 있는 것으로 추정하고 있다.