[PostgreSQL] 오타·발음·악센트를 견디는 이름 검색 — pg_trgm + unaccent + fuzzystrmatch

설치·접속: PostgreSQL 설치와 접속

부제: CS 검색창에서 'Katharyn', 'Jose Garcia', 'Catherine'을 각각 다르게 흔들어 쳐도 같은 사람을 찾아야 할 때

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE EXTENSION IF NOT EXISTS unaccent;
CREATE EXTENSION IF NOT EXISTS fuzzystrmatch;

CREATE TABLE names (id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name text NOT NULL);
-- 후보 좁히기용 trigram GIN, 발음 폴백용 daitch_mokotoff GIN
CREATE INDEX names_trgm_gix ON names USING gin (name gin_trgm_ops);
CREATE INDEX names_dm_gix   ON names USING gin (daitch_mokotoff(name));

이름 검색은 실패 방식이 세 가지다. 오타('Katharyn'), 악센트 표기 차이('José' vs 'Jose'), 그리고 철자가 아예 다른 발음('Catherine' vs 'Katharine'). 하나의 LIKE나 하나의 함수로는 셋 다 못 잡는다. 단계로 쌓는다.

1단계 — pg_trgm으로 후보를 좁힌다 (오타 흡수)

SET pg_trgm.similarity_threshold = 0.3;
SELECT name, round(similarity(name,'Katharyn')::numeric,3) AS sml
FROM names WHERE name % 'Katharyn' ORDER BY sml DESC;

      name      |  sml
----------------+-------
 Kathryn Hahn   | 0.375
 Katharine Ross | 0.333

% 연산자는 trigram(연속 3글자) 유사도가 임계값 이상인 행만 남긴다. 오타 'Katharyn'이 'Kathryn'/'Katharine'을 물어 온다. GIN 인덱스(gin_trgm_ops)를 타므로 수만 행에서도 후보 추림이 인덱스 스캔이다. 이게 1차 그물이다.

2·3단계 — unaccent 정규화 후 levenshtein 임계값 (악센트 + 오타 컷)

-- 검색어 'Jose Garcia'로 'José García'까지 매칭
SELECT name FROM names
WHERE levenshtein_less_equal(
        lower(unaccent(name)), lower(unaccent('Jose Garcia')), 2) <= 2;

    name
-------------
 José García
 Jose Garcia

unaccent()가 José García → jose garcia로 diacritic을 벗겨 악센트 유무를 무력화한다. 그 위에 levenshtein_less_equal(a, b, 2)가 편집거리를 재되 2를 넘으면 즉시 포기(전체 거리 계산보다 빠르다)해 "오타 2글자까지만" 통과시킨다. unaccent를 먼저 걸지 않으면 é vs e가 편집거리 1로 잡혀 임계값을 잡아먹는다. 정규화 → 거리 컷 순서가 중요하다.

4단계 — 발음 폴백: soundex는 놓치고 dmetaphone은 잡는다

여기가 핵심이다. 'Catherine'과 'Katharine'은 오타도 악센트도 아니고 첫 글자부터 다르다. trigram·levenshtein이 모두 약하다. 발음 코드로 넘어간다.

SELECT name,
       soundex(split_part(name,' ',1))    AS sx,
       dmetaphone(split_part(name,' ',1)) AS dm
FROM names
WHERE dmetaphone(split_part(name,' ',1)) = dmetaphone('Catherine')
ORDER BY name;

      name      |  sx  |  dm
----------------+------+------
 Catherine Bell | C365 | K0RN
 Katharine Ross | K365 | K0RN
 Kathryn Hahn   | K365 | K0RN

주의: 흔한 오해와 달리 soundex('Catherine')은 C365, soundex('Katharine')은 K365로 서로 다르다. soundex는 첫 글자를 코드에 그대로 박기 때문에 C/K가 갈리면 매칭이 깨진다. 반면 dmetaphone(double metaphone)은 셋 다 K0RN으로 같아 정확히 잡아낸다. 그래서 발음 폴백은 soundex가 아니라 dmetaphone을 기본으로 둔다. (Geoff/Jeff도 같은 함정 — soundex는 G100 vs J100으로 갈리지만 dmetaphone은 둘 다 JF다.)

결론 — 언제 이 조합을 쓰나

한글이 아닌 영문 이름/지명 검색에서 오타·악센트·발음 흔들림을 한 검색창으로 흡수해야 할 때다. 순서는 값싼 것부터: pg_trgm(인덱스 후보 추림) → unaccent+levenshtein(정밀 컷) → dmetaphone(발음 폴백). 앞 단계가 잡으면 뒤는 안 가도 된다.

이렇게도 쓴다

soundex가 놓치는 짝을 dmetaphone이 잡는 걸 직접 비교한다. 발음 폴백 기본값을 정할 때 근거.

SELECT 'Catherine' n, soundex('Catherine') sx, dmetaphone('Catherine') dm
UNION ALL SELECT 'Katharine', soundex('Katharine'), dmetaphone('Katharine');
-- sx: C365 vs K365 (불일치)   dm: K0RN = K0RN (일치)

 

다국어 이름은 daitch_mokotoff + GIN 배열 겹침으로. 표기 흔들림을 6자리 코드 겹침(&&)으로 잡는다. (조합: GIN 인덱스)

SELECT name FROM names
WHERE daitch_mokotoff(name) && daitch_mokotoff('Shwartzeneger');
-- → Schwartzenegger, Swartzenegger  (dmk 모두 {479465})

 

soundex가 잘 통하는 짝도 있다. 첫 글자가 같은 계열이면 soundex로 충분하고 가볍다.

SELECT name FROM names WHERE soundex(split_part(name,' ',1)) = soundex('John');
-- John/Jon/Jhon 모두 J500 (첫 글자 J로 같아 soundex도 잡는다)

 

difference()로 soundex 유사도를 0~4 점수로 받아 정렬에 쓴다.

SELECT name, difference(split_part(name,' ',1), 'John') AS score
FROM names ORDER BY score DESC;   -- 4=매우 유사