[BE devlog] FAERS 약물명 정규화 작업

전에 작업한 내용

 

[BE devlog] FAERS 약물명 통합 쿼리 최적화

 

문제상황

일단 이 작업의 한계점은 주요 약물에만 동의어 사전을 만들어준것이었다.

그래서 아직 집계가 제대로 되지 않는 문제가 있었음.

예를 들어서 타이레놀 같은 경우는 브랜드명인데 주성분은 paracetamol 이다. 그래서 성분명 매핑작업을 시작했다.

 

왼쪽 전 / 오른쪽 후

 

기존에는 tylenol을 검색했을 때 제대로 된 타이레놀은 안나오고 tylenol's 라던가 tylenol-aceamethaphen 다 같은 데이터인데 여러개 뜨던가 하는 문제가 있었음. 그래서 정규화 과정이 필요했다.

 

개선 방법

 

1. 자동완성 정렬 빈도수 기준으로 보여줌

suggest 쿼리를 알파벳 순에서 freq(빈도수) 내림차순으로 바꿔서 보여주었다.

UPDATE faers_drug_dict d
SET freq = sub.cnt
FROM (
    SELECT lower(trim("DRUGNAME")) AS dn, COUNT(*) AS cnt
    FROM SD_FAERS_DRUG
    GROUP BY lower(trim("DRUGNAME"))
) sub
WHERE d.drugname_display = sub.dn;

 

 

 

2. standardized_drug 테이블

 

고객사에서 처음에는 a,b,c 테이블만 참고하면 된다고 했는데 찾아보니 제공해준 FAERS 스키마를 더 찾아보니 standardized_drug라고 브랜드명-성분명이 매핑된 테이블이 있었다. 행 수가 'SD_FAERS_DRUG'와 동일한 91,388,616행인데, 'substance' 컬럼(INN 성분명)이 추가된 구조다.

 

그래서 기존에 synonym 배열로 표기 변형만 묶어서 생긴 한계점을 해결할 수 있게 되었다.

 

 

 

3. 기존 faers_drug_dict 재구축

synonym 기반이었던걸 substance 기반으로 완전히 재구축하였다. 핵심 로직은 DRUGNAME 하나에 substance가 여러 개 매핑된 겨우 가장 많이 등장하는 것을 선택하는 것이다.

-- ✅ DRUGNAME → substance 매핑, 복수 매핑 시 빈도 최다 선택
INSERT INTO public.faers_drug_dict (drugname_display, drugname_norm, freq)
SELECT dn, COALESCE(substance, dn), cnt
FROM (
    SELECT 
        lower(trim("DRUGNAME"))  AS dn,
        substance,
        COUNT(*)                 AS cnt,
        ROW_NUMBER() OVER (
            PARTITION BY lower(trim("DRUGNAME")) 
            ORDER BY COUNT(*) DESC
        ) AS rn                  -- ✅ 같은 DRUGNAME에 substance 여러 개면 빈도 1위만 선택
    FROM "FAERS"."standardized_drug"
    WHERE trim("DRUGNAME") IS NOT NULL AND trim("DRUGNAME") <> ''
    GROUP BY lower(trim("DRUGNAME")), substance
) sub
WHERE rn = 1
ON CONFLICT (drugname_display) DO UPDATE 
    SET drugname_norm = EXCLUDED.drugname_norm,
        freq          = EXCLUDED.freq;
-- ✅ ON CONFLICT: 기존 synonym 행은 덮어쓰지 않고 norm/freq만 갱신

 

COALESCE(substance, dn)은 substance가 NULL인 1.4% 케이스의 fallback 처리다.

substance가 없으면 기존 norm을 쓰도록 하였다.

 

 

 

4. 조회 쿼리 수정

# 변경 전: synonym 수천 개 배열 → ANY() 매칭 → 풀스캔 유발 가능성
norm, synonyms = resolve_synonyms(conn, drug)
WHERE lower(trim("DRUGNAME")) = ANY(%(synonyms)s)

# 변경 후: substance 단일 값 → 인덱스 스캔
substance = resolve_substance(conn, drug)  # 예: 'acetylsalicylic acid'
WHERE d2.substance = %(substance)s

기존에 synonyms로 조회하던걸 이제 sbustance 단일 값 하나로 조회가 가능해졌다. 바인딩으로 넘기던 방식이 필요 없어졌다.

조회하는 테이블도 standardized_drug 테이블로 전환해주었다. substance 컬럼이 해당 테이블에 있기 때문이다.

 

기존 exact match	377,813건
1차 synonym 배열	600,294건
substance 기반	1,053,195건

aspirin 하나 기준으로 exact match 대비 2.79배 더 잡아낸다. 브랜드명까지 전부 포함되기 때문이다.

 

 

 

5. 인덱스 추가

-- ✅ 모두 CONCURRENTLY로 서비스 중단 없이 생성
CREATE INDEX CONCURRENTLY ON standardized_drug(substance);           
CREATE INDEX CONCURRENTLY ON standardized_drug("ISR");               
CREATE INDEX CONCURRENTLY ON standardized_drug("ROLE_COD");         

-- ✅ 커버링 인덱스: substance + ROLE_COD 필터 후 ISR을 인덱스에서 바로 읽음
CREATE INDEX CONCURRENTLY ON standardized_drug(substance, "ROLE_COD") 
    INCLUDE ("ISR");

standardized_drug에도 인덱스를 추가해주었다. substance와 ROLE_COD로 필터링하고 ISR을 가져오는 패턴이 쿼리에서 반복되는데 INCLUDE(ISR)을 붙이면 테이블을 다시 읽지 않고 바로 ISR을 가져올 수 있어서 테이블 접근 횟수를 줄일 수 있다.

 

 

 

6. summary 쿼리

그리고 기존에 summury 쿼리를 이용해서 부작용 보고 건수, 연도별 추이, 상위 부작용 목록을 반환하였는데 matched_isr CTE가 python에서 3번이나 중복 계산되고 있었다. 같은 isr 목록을 뽑는 쿼리를 세번 따로 날리고 있었던 것임.

 

이걸 단일 쿼리로 합치고 MATERIALIZED 키워드를 붙였다.

WITH matched_isr AS MATERIALIZED (
    -- ✅ substance로 ISR 목록 1회 계산, 이후 재사용
    SELECT "ISR"
    FROM standardized_drug
    WHERE substance = %(substance)s
      AND "ROLE_COD" IN ('PS', 'SS')
),
joined AS MATERIALIZED (
    SELECT d."ISR", d."AGE", d."SEX", d."OUTC_COD", r."PT"
    FROM matched_isr m
    JOIN SD_FAERS_DEMO  d ON m."ISR" = d."ISR"
    JOIN SD_FAERS_REAC  r ON m."ISR" = r."ISR"
),
yearly   AS (SELECT EXTRACT(YEAR FROM ...) AS yr, COUNT(*) FROM joined GROUP BY yr),
top_pt   AS (SELECT "PT", COUNT(*) FROM joined GROUP BY "PT" ORDER BY COUNT(*) DESC LIMIT 10)

SELECT
    (SELECT COUNT(DISTINCT "ISR") FROM matched_isr) AS isr_count,
    (SELECT json_agg(...) FROM yearly)               AS yearly_total,
    (SELECT json_agg(...) FROM top_pt)               AS top_pts;

 

MATERIALIZED를 사용하면 PostgreSQL은 해당 CTE를 중간 결과로 한 번 계산하여 저장하고, 같은 쿼리 내에서는 재사용한다.

 

 

결과적으로

약물	개선 전	개선 후
aspirin	~50,000ms	~26,000ms

 

다만 26초는 여전히 길어서 이후에 API 2단계 분리(isr_count 선응답 → 차트 별도 로드)와 Skeleton UI 작업을 추가로 진행했다.

 

회고

 

이번 작업의 핵심은 standardized_drug 테이블을 발견한 것이었다.

1차에서 synonym 배열로 커버리지를 377,813 → 600,294건으로 올렸는데, substance 기반으로 바꾸니 1,053,195건까지 올라갔다. 애초에 접근 방식 자체가 달랐던 거다.

summary 쿼리는 MATERIALIZED CTE로 절반까지 줄였는데, 26초는 쿼리 최적화만으로는 한계가 있었다. 데이터 규모 자체가 워낙 크다 보니 어느 순간부터는 아키텍처 문제가 된다는 걸 느꼈다. 그래서 쿼리를 더 짜내는 대신 API 분리로 방향을 틀었다.