우리 API에서 가장 느린 요청은 '아무것도 찾지 못한' 요청이었다

본문

선박 위치·ETA API를 운영하는 Vessel 팀의 엔지니어링 포스트모템입니다. 2026년 9월 9일 기준으로, 선박의 최근 항만 이벤트 조회는 결과가 있으면 28ms였지만 없으면 2.0~2.5초가 걸렸고, IMO를 MMSI로 매핑하지 못하는 ETA 조회는 유휴 상태에서 12초, 부하 시에는 statement timeout까지 30초 이상이 걸렸습니다. 하룻밤 동안 ETA 엔드포인트에 쓰인 데이터베이스 시간의 71%가 'not found'로 끝난 요청이 사용했습니다. 환경은 TimescaleDB 2.25와 PostgreSQL 16입니다.

잘못된 압축 설정

port_events 테이블은 1시간 청크에 MMSI 세그먼트(segmentby), 시간 역순 정렬(orderby)로 압축돼 있었습니다. 위치 데이터는 MMSI+시간으로 읽히므로 맞는 설정이지만, port_events와 vessel_eta는 IMO나 항만 코드로 조회되는데 같은 설정을 복사해 쓴 것이 문제였습니다. 764개 청크 중 760개가 압축된 상태에서, 압축 청크의 (vessel_imo, timestamp) btree 인덱스는 정의만 있고 내용이 비어 있었습니다. ORDER BY timestamp DESC LIMIT 1 쿼리는 최신 청크부터 순회해 첫 행에서 멈추므로, 결과가 있는 선박은 빠르지만 없는 선박은 압축 청크마다 순차 스캔을 수행했습니다. 같은 miss를 세그먼트 컬럼인 MMSI로 조회하면 0.12초였습니다.

블룸필터와 COALESCE의 함정

TimescaleDB 2.25는 배치별로 vessel_imo 같은 컬럼에 블룸필터를 유지하지만, 저자들의 데이터에는 IMO가 NULL인 행이 많았고 IMO가 전부 NULL인 배치는 블룸필터도 NULL이라 모든 조회를 통과시켰습니다. 두 번째 문제는 SQL 자체로, 항만 코드를 COALESCE((SELECT ...), $1)와 비교했는데 이 버전에서 COALESCE는 블룸필터로 pushdown되지 않아 요청 구간의 모든 배치를 압축 해제했습니다. 행 두 개뿐인 항만에 6.05초가 걸렸고, 단순 파라미터로 바꾸면 0.84초였습니다. 팀은 코드 변환을 Go에서 먼저 처리하도록 바꾸고 생성 SQL에 서브쿼리가 없음을 검증하는 테스트를 추가했습니다.

압축 해제 마이그레이션

압축 효율도 형편없었습니다. 1,689MB가 1,687MB로, 2MB 절약에 그쳤습니다. 1시간 청크를 MMSI로 세그먼트화하면 배치가 '한 선박의 한 시간 항만 이벤트'가 되는데 대부분 한 행뿐이라 압축할 대상이 없었습니다. 팀은 정책 제거와 청크 간격 변경(1일)을 먼저 수행하고 — 순서가 중요한데 정책이 30분마다 루프 뒤의 청크를 재압축하기 때문입니다 — decompress_chunk를 청크당 개별 트랜잭션으로 최신 청크부터 실행했습니다. lock_timeout 5초와 재시도, 잠금 대기 감지 시 중단, retention·continuous aggregate 작업 일시 정지, 재실행 가능 설계를 갖췄고, 로컬 리허설(745 청크, 341초)을 거쳐 프로덕션에서 2,280초가 걸렸습니다. 결과로 IMO miss가 2.0~2.5초에서 8~12ms, hit는 28ms에서 8~10ms, 항만별 쿼리는 6.05초에서 20~281ms로 개선됐습니다.

vessel_eta와 latest 테이블

78GB·752 청크의 vessel_eta는 압축이 3.0x로 실효가 있어 유지하되, '최신 상태'만 묻는 두 쿼리(선박별 최신 ETA, 항만 입항 예정 선박)를 위한 별도 테이블 vessel_eta_latest를 만들었습니다. 단순한 '선박당 한 행' 설계는 실제 답과 달라 — 다른 목적지를 보고한 선박이 누락되는 사례가 있어 — PRIMARY KEY를 (mmsi, dest_key, eta_key, imo_key)로 잡고 generated column으로 NULL을 정규화했습니다. 이 테이블은 592,672행·248MB에 그쳤습니다. 배치는 먼저 키당 한 행으로 축약한 뒤 ON CONFLICT DO UPDATE ... WHERE EXCLUDED.timestamp 비교로 upsert합니다.

백필 데드락과 배포 실패

백필 중 SQLSTATE 40P01 데드락이 발생했습니다. ON CONFLICT DO UPDATE는 WHERE 절이 업데이트를 거부해도 충돌 행을 잠그고, 백필은 키 순서·라이브 쓰기는 도착 순서로 같은 행들을 다른 순서로 잠갔기 때문입니다. 양쪽을 COLLATE "C"(Go 문자열 순서와 일치)로 정렬하고 unnest WITH ORDINALITY를 쓰며 40P01 재시도를 추가했고, 이후 라이브 writer끼리도 데드락이 나 이 정렬이 백필과 무관하게 필요했음이 드러났습니다. 배포에서는 NULL::uuid AS id를 Go string으로 스캔해 약 30분간 500 에러가 났습니다. 검증 스크립트는 포트가 점유된 채 구 서버와만 비교해 '4,105개 응답이 바이트 단위로 동일'한 가짜 통과를 냈고, 입항 쿼리는 한도 50인데 200을 요청해 양쪽 다 400 에러 본문을 비교하고 있었습니다.

결과

전환 후 ETA miss는 p50 209ms→10ms, p99 34.8초→567ms, IMO 직접 조회는 33.6초→8ms, 입항 쿼리는 12.5초에서 5ms로 줄었습니다. 남은 꼬리는 커넥션 풀 포화가 원인으로, 상세 조회가 촉발하는 백그라운드 집계 갱신에 별도 소형 풀과 동시성 상한을 둬 해결했습니다. 마지막으로 latest 테이블을 이용한 중복 리포트 제거로 쓰기 행 수가 약 3/4 감소했고, 이 작업을 먼저 했다면 78GB 테이블이 20GB 미만이었을 것으로 추정합니다.

원문: dev.to / 번역·요약: Trawling