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

지금 진행하고 있는 프로젝트에서 대규모 데이터를 다루는 FDA 페이지를 오늘 개선해봤다.

 

문제 상황

FDA에서 운영하는 FAERS(FDA Adverse Event Reporting System) 라는 부작용 자발보고 데이터베이스가 있다.

전 세계 의료진과 환자가 "이 약 먹고 이런 부작용이 생겼어요"를 직접 신고하는 시스템이다.

이 데이터들을 약 별로 조회할 수 있게 해달라는 요청을 받았고 내가 간호학과에서 받은 데이터만 해도 약 1.9억행이다

 

일단 이게 실제 연관된 테이블들이 3개나 있었기 때문에 이 테이블들을 조인하는것도 어려웠고

처음에는 사실 실행에 의의를 두고 만들었다.

그래서 마음속에 묻어두고 다른 기능 개발하다가 이제 주 기능은 구현이 끝나서 리팩토링을 하다가 문제를 깨달음.

 

aspirin으로 검색하면 내 웹사이트에선 377,813건이 나오는데 실제 데이터베이스에는 aspirin 관련 행이 600,294건이었음.

실제로 222,481건이 누락되고 있었다.

 

DRUGNAME	count
aspirin	200,000
ASPIRIN	120,000
Aspirin	50,000
aspirin.	7,294
...

 

이런식으로 각각 표기가 다르게 들어있어서 그랬던 거임!!

정규화를 제대로 안해서 이런 문제가 생겼다

 

 

원인 분석

aspirin / ASPIRIN / aspirin. / Aspirin을 전부 같은 약으로 인식하는 synonym(동의어) 테이블을 만들고, 검색할 때 이걸 참고해서 한 번에 조회하게 했다. 아스피린 말고도 다른 약들도 이렇게 정규화 안된게 많은데 일단 우선적으로 대표적인 것들만 동의어 설정을 해놨다.

 

faers_drug_dict 라는 테이블을 만들어서 대표적인 것만 정규화 사전을 만들어줌(굉장히 비효율적이긴 함)

aspirin	aspirin
ASPIRIN	aspirin
aspirin.	aspirin
Aspirin	aspirin

 

synonym JOIN 구현

SELECT COUNT(*)
FROM SD_FAERS_DRUG
WHERE lower(trim("DRUGNAME")) = ANY(
  ARRAY(
    SELECT lower(trim(drugname_display))
    FROM faers_drug_dict
    WHERE drugname_norm = 'aspirin'
  )
)

 

근데 이렇게 sql을 짰는데 어떤 약물이든 항상 12~13초가 걸렸다.

약물마다 synonym 수가 다른데 시간이 똑같아서 쿼리가 이상하다는 걸 깨달았다.

 

EXPLAIN ANALYZE
SELECT COUNT(*) FROM SD_FAERS_DRUG
WHERE lower(trim("DRUGNAME")) = ANY(
  ARRAY(SELECT lower(trim(drugname_display)) 
        FROM faers_drug_dict WHERE drugname_norm = 'aspirin')
);
```
```
Parallel Seq Scan on SD_FAERS_DRUG  (cost=... rows=...)
  Filter: (lower(trim("DRUGNAME")) = ANY ($0))
  Rows Removed by Filter: 수천만

 

그래서 EXPLAIN ANALYZE를 찍어봤다

EXPLAIN ANALYZE란?

PostgreSQL은 쿼리의 실행 계획을 수립한다. 쿼리 구조와 데이터의 속성에 맞는 올바른 계획을 선택하는 것이 좋은 성능을 내기 때문! 시스템에는 좋은 계획을 선택하기 위한 플래너가 포함되어 있다.

 

EXPLAIN 명령을 사용하면 플래너가 어떤 쿼리 계획을 생성하는지 확인할 수 있다.

쿼리 계획의 구조는 트리 형식이다. 트리의 맨 아래에 있는 노드는 스캔 노드로, 테이블의 행을 반환한다.

테이블 액세스 방식에 따라 순차 스캔, 인덱스 스캔, 비트맵 인덱스 스캔 등 다양한 유형의 스캔 노드가 있다.

 

EXPLAIN의 출력에는 계획 트리의 각 노드에 대해 기본 노드 유형과 플래너가 노드를 실행하기 위해 만든 비용 추정치가 표시된다.

내가 위에서 실행한 sql의 경우 cost, rows값이 있는데

cost : 예상 시작 비용, 출력 단계를 시작하기 전에 소요되는 시간

rows: 이 계획 노드에서 출력되는 예상 행 수를 의미한다.

 

EXPLAIN ANALYZE

플래너의 추정지의 정확성을 확인하기 위해 ANALYZE 옵션을 사용할 수 있다.

이 쿼리를 DB가 어떤 방식으로 실행했는지, 실제로 얼마나 걸렸는지를 보여주는 명령어다

 

EXPLAIN	EXPLAIN ANALYZE
실행 계획만 보여줌	쿼리를 실제로 실행함
실제 실행은 안함	실행 계획 + 실제 수행 시간, 실제 처리 행 수까지 보여줌
옵티마이저가 이렇게 실행할 것 같음 하고 예측한 결과	예측과 실제 차이를 확인할 수 있음

 

 

결과가 Paralle Seq Scan on SD_FAERS_DRUG로 나왔는데 이건

- SD_FAERS_DRUG 테이블을 순차적으로 처음부터 끝까지 읽고 있다

- 병렬로 여러 워커가 나눠서 읽고 있다.

라는 의미로 인덱스 탐색이 아니라 풀테이블 스캔을 하고 있다는 의미다.

그래서 synonym 개수와 상관없이 항상 시간이 똑같이 나왔던 것이다.

 

Seq Scan이 발생한 원인은 ANY(ARRAY(서브쿼리)) 구조 때문이다.

플래너는 쿼리를 실행하기 전에 실행 계획을 먼저 확정하는데, 서브쿼리 안에 어떤 값이 들어올지는 실제로 실행해봐야 알 수 있다.

값을 모르는 상태에서는 인덱스를 어떻게 써야 할지 판단할 수 없어서, 결국 "그냥 다 읽자"는 보수적인 선택(Seq Scan)을 하게 된다.

 

Filter: (lower(trim("DRUGNAME")) = ANY ($0))

- DRUGNAME 을 trim, lower 처리

- $0 배열 안에 있는지 검사

 

Rows Removed by Filter: 수천만

- 조건에 안맞아서 버린 행이 수천만 개

- 비효율적인 스캔 발생

 

 

해결 - 파라미터 바인딩 + 인덱스 추가

플래너가 실행 계획 시점에 값을 알 수 있도록 python에서 synonym 목록을 먼저 조회한 뒤 파라미터 바인딩으로 PostgreSQL에 전달하는 방식으로 바꿨다. subquery와 달리 플래너가 계획을 세우는 시점에 배열 안의 값을 이미 알고 있어서 인덱스 스캔이 가능해진다.

 

def resolve_synonyms(conn, drug_name: str) -> tuple[str, list[str]]:
    with conn.cursor() as cur:
        cur.execute("""
            SELECT drugname_norm, array_agg(lower(trim(drugname_display)))
            FROM faers_drug_dict
            WHERE drugname_norm = lower(trim(%s))
            GROUP BY drugname_norm
        """, (drug_name,))
        row = cur.fetchone()
    
    if row:
        return row[0], row[1]
    return drug_name.lower().strip(), [drug_name.lower().strip()]

 

# ✅ 1단계: synonym 목록 먼저 조회 (dict 테이블에서 즉시 반환)
norm, synonyms = resolve_synonyms(conn, "aspirin")
# synonyms = ['aspirin', 'ASPIRIN', 'aspirin.', 'Aspirin']

# ✅ 2단계: 파라미터 바인딩으로 전달 — 플래너가 계획 시점에 값을 앎
cur.execute("""
    SELECT COUNT(*) FROM SD_FAERS_DRUG
    WHERE lower(trim("DRUGNAME")) = ANY(%(synonyms)s)
""", {"synonyms": synonyms})

 

계획 시점에 플래너가 값을 알 수 있기 때문에 index scan으로 실행할 수 있다. 물론 db 요청이 2번으로 늘어난다는 단점이 있다.

하지만 synonym 조회는 dict 테이블에서 인덱스를 타기 때문에 시간이 짧고, 본 쿼리도 인덱스 스캔이 가능해지니 전체 응답시간도 확실히 짧아진다.

 

여기에 자주 조회되는 컬럼에 인덱스 3개를 추가했다.

JOIN 성능 개선을 위해 ISR, ROLE_COD 컬럼에도 인덱스를 추가했다.

(지금 실제 서비스에서 쓰고있는 DB이기 때문에 CONCURRENTLY를 사용함)

CREATE INDEX CONCURRENTLY idx_sd_faers_drug_isr  ON SD_FAERS_DRUG(ISR);
CREATE INDEX CONCURRENTLY idx_sd_faers_drug_role ON SD_FAERS_DRUG(ROLE_COD);
CREATE INDEX CONCURRENTLY idx_sd_faers_demo_isr  ON SD_FAERS_DEMO(ISR);

 

결과

약물	개선 전	개선 후	배율
aspirin	13,193ms	3,357ms	3.9x
rituximab	12,504ms	1,017ms	12.3x
xeljanz	12,493ms	711ms	17.6x
eliquis	12,798ms	1,131ms	11.3x

synonym 을 적용한 약물에 대해서 응답 속도가 확실히 빨라진걸 확인할 수 있다.

무엇보다 데이터 정확도가 올라간 게 가장 큰 의의이다.

 

또 faers_drug_dict를 SD_FAERS_DRUG 전체 기준으로 재구축했다.

synonym 적용한 거 외에 다른 약물은 SD_FAERS_DRUG 기준으로 데이터를 채워줬다.

덕분에 SD_FAERS_DRUG를 직접 조회하던걸 dict 경유로 바뀌어서 즉시 응답할 수 있게 되었다.

또 aspirin / ASPIRIN / aspirin. 이 중복 노출되던 문제도 해결되었다.

 

 

회고

이번 작업에서는 플래너에 대해서 알게 되었다. 플래너가 값을 아냐 모르냐가 실행 계획에 큰 영향을 주는지를 알게 되었다. 느린 쿼리를 만나면 EXPLAIN ANALYZE를 찍어서 성능을 파악할 수 있다.

물론 이번에 한 건 주요 약물에만 동의어 처리를 했기 때문에 상당히 비효율적이고 한계가 있다.

 

그래서 사실 이 방법말고 다른 방법으로 새로 구현한게 있다.

그건 다음 포스팅에서 다루도록 하겠다.

 

백엔드나 디비는 제대로 공부해본적이 없어서 많이 헤매게 되는 것 같다.. 이번 프로젝트를 진행하면서 많이 공부해나간다ㅜㅜ..

그리고 처음에 그냥 돌아가면 되지~ 마인드로 대충 구현했더니 지금 리팩토링을 엄청 해야돼서 처음부터 제대로 할걸 생각이 든다.