170만 개의 밀집된 PostgreSQL 행을 6GB ARM 인스턴스에 밀어 넣고 실제 사용자 트래픽을 처리하려고 할 때 어떤 일이 발생하는지 말씀드리겠습니다.

서버를 45초 동안 마비시키는 순차 테이블 스캔 (Sequential table scans). 쿼리 플래너 (query planner)를 울게 만드는 COUNT(*) 쿼리. 첫 100명의 사용자가 사이트에 접속하기도 전에 예산의 블랙홀로 변해버리는 다국어 LLM 번역 비용.

이것이 LemonKnows가 시작된 배경입니다. LemonKnows는 170만 개의 제품, 41,000개의 글로벌 정부 리콜, 100,000개 이상의 커뮤니티 불만 사항을 추적하는 소비자 안전 및 식품 인텔리전스 플랫폼으로, 이 모든 것이 추가 호스팅 비용 없이 Oracle Cloud Free Tier에서 실행됩니다.

오늘날, 사용자 대상 페이지는 66ms 내에 조립됩니다.

이를 가능하게 한 정확한 엔지니어링 플레이북을 소개합니다.

COUNT(*)의 문제점

프론트엔드 페이지네이션 (pagination)이나 대시보드 지표를 지원하기 위해 수백만 개의 레코드가 있는 테이블에서 SELECT COUNT(*)를 실행하고 있다면, 당신은 능동적으로 데이터베이스를 죽이고 있는 것입니다.

PostgreSQL은 활성 트랜잭션의 가시성을 확인하기 위해 물리적으로 행을 스캔하여 MVCC (Multi-Version Concurrency Control, 다중 버전 동시성 제어)를 처리합니다. 170만 행의 데이터셋에서 이는 쿼리 플래너를 완전히 무력화합니다. 부하가 걸린 상태에서 단 한 번의 COUNT(*) 호출이 서버를 최대 45초 동안 멈추게 했습니다.

표준적인 조언은 pg_class 추정치를 사용하는 것입니다:

SELECT reltuples::bigint FROM pg_class WHERE relname = 'products';

더 빠르지만, WHERE 절을 추가하는 순간 무용지물이 됩니다. pg_class는 필터링된 하위 집합에 대해 알지 못하기 때문입니다.

실질적인 해결책: 저는 쿼리 시점에 Postgres에게 무언가를 세어달라고 요청하는 것을 완전히 중단했습니다.

저는 사전 집계된 카운터의 플랫 키-값 저장소(flat key-value store)인 가벼운 stats_cache 조회 테이블을 구축했습니다. 백그라운드 인제스션 파이프라인 (ingestion pipeline)이 실행되거나 제품의 안전 상태가 변경될 때마다, 백엔드는 관련 카운터를 비동기적으로 증가시키거나 감소시킵니다. 프론트엔드 요청은 수 초가 걸리는 스캔을 트리거하는 대신 2ms의 기본 키 (primary key) 조회를 실행합니다:

-- 문제를 일으키는 코드:
SELECT COUNT(*) FROM products WHERE category = 'automotive' AND status = 'active';
-- 대체 코드:
...

그 트레이드오프(tradeoff)는 결과적 일관성(eventual consistency)입니다. 즉, 카운트(counts)가 실시간 상태보다 약간 뒤처지게 됩니다. 소비자 안전 플랫폼에서 이는 수용 가능한 계약입니다. 리콜 데이터 자체는 항상 정확하지만, 이를 둘러싼 집계(aggregates) 데이터까지 그럴 필요는 없기 때문입니다.

단일 컬럼 인덱스가 대부분 '보여주기식'인 이유

쿼리가 카테고리(category)로 필터링하고, 규제 안전 플래그(regulatory safety flag)를 확인하며, 신뢰도 점수(reliability score)로 동시에 정렬해야 하는 경우, 단일 컬럼 인덱싱(single-column indexing)은 사실상 가치가 없습니다.

다음은 조용히 성능을 파괴하고 있었던 인덱스 설정입니다:

CREATE INDEX idx_products_category ON products (category);
CREATE INDEX idx_products_status ON products (status);
CREATE INDEX idx_products_score ON products (score);

세 개의 인덱스가 있습니다. 하지만 운영 환경(production)에서 발생하는 실제 쿼리 패턴에는 그 중 어느 것도 유용하지 않습니다:

SELECT * FROM products
WHERE category = 'automotive'
AND status = 'active'
...

Postgres는 인덱스 중 하나를 선택하여 적용한 다음, 나머지는 메모리에서 필터링합니다. 이는 결과적으로 거대한 중간 결과 집합에 대해 부분적인 순차 스캔(partial sequential scan)으로 회귀하는 것과 같습니다.

해결책은 단일 컬럼의 노이즈를 완전히 제거하고, 실제 필터 실행 순서에 직접 맞춤화된 매우 구체적인 복합 인덱스(composite index)를 배포하는 것입니다:

CREATE INDEX idx_products_cat_status_score
ON products (category, status, score DESC);

컬럼 순서는 미적인 요소가 아닙니다. WHERE 절과 ORDER BY 절의 정확한 순서를 반영해야 합니다. 순서가 일치하면 쿼리 플래너(query planner)는 순차 스캔을 완전히 건너뛰고 인덱스 조건 조회(Index Condition lookups)를 수행합니다. 이는 170만 개의 행을 스캔하는 것과 필요한 정확한 20개의 행만 스캔하는 것의 차이입니다.

비용 제로의 다국어 AI 번역 레이어 구축하기

LemonKnows는 영어, 프랑스어, 독일어, 스페인어, 이탈리아어, 포르투갈어, 네덜란드어 등 7개 언어로 데이터를 제공합니다. 번역을 동적으로 처리하기 위해 LLM API를 요청 스트림(request stream)에 직접 연결하는 방식은 두 가지 즉각적인 결격 사유를 초래합니다. 바로 누적되는 네트워크 지연 시간(network latency)과 트래픽에 따라 증가하는 예측 불가능한 API 비용입니다.

대신 제가 구축한 아키텍처는 현지화 (localization)를 콘텐츠 주소 지정 캐시 계층 (content-addressed cache layer)으로 취급합니다.

데이터 흐름:

• 독일어로 된 제품 결함 상세 내역에 대한 국제 요청이 도착합니다.
• Node.js 백엔드가 원본 영어 소스 텍스트의 SHA-256 해시 (hash)를 생성합니다.
• ugc_translations 테이블에서 hash = SHA256 이고 lang = 'de'인 항목을 조회 (lookup) 합니다.
• 캐시 히트 (Cache hit): 로컬 스토리지에서 현지화된 텍스트가 반환됩니다. API 비용: $0. 조회 시간: 5ms 미만.
• 캐시 미스 (Cache miss): 문자열이 백그라운드 워커 (background worker)를 통해 Gemini 2.5 Flash Lite로 전달되어, 규제 전문 용어 (regulatory jargon)를 보존하며 번역된 후, 로컬 DB 캐시에 기록되고 사용자에게 반환됩니다.

핵심 설계 제약 사항: 백그라운드 워커는 비동기적 (asynchronously)으로 실행됩니다. 사용자는 실시간 LLM 호출을 위해 기다리지 않습니다. 캐시 미스 발생 시, 사용자는 이번 요청에서 번역본을 받게 됩니다. 이후 검색 엔진 크롤러 (search engine crawlers)를 포함하여, 어떤 국가의 어떤 사용자라도 해당 문자열에 대해 보내는 모든 후속 요청은 비용이 들지 않으며 5ms 이내에 반환됩니다.

특정 데이터셋은 정확히 한 번만 번역됩니다. 그 이후로는 영구적으로 무료로 서비스할 수 있습니다.

UGC 이미지 파이프라인 및 데이터베이스 수준의 어뷰징 방지

170만 개 이상의 제품을 관리한다는 것은 엄청난 콘텐츠 파편화 (content fragmentation), 특히 제품 이미지 누락 문제를 다뤄야 함을 의미합니다. 100만 개의 항목에 대한 사진을 수동으로 확보하는 것은 1인 운영자에게 불가능하므로, 저는 애플리케이션 계층 (application layer)이 아닌 데이터베이스 계층 (database layer)에 어뷰징 방지 (abuse prevention) 기능을 구축한 크라우드소싱 UGC 루프를 설계했습니다.

핵심 제약 사항은 스키마 (schema)에 존재합니다:

-- 엄격한 제한: 사용자당, 제품당 하나의 대기 중인 제안만 허용
ALTER TABLE product_image_suggestions
ADD CONSTRAINT unique_user_product UNIQUE (user_id, product_id);

데이터베이스 엔진 자체가 이 제한을 강제합니다. 유지 관리해야 할 애플리케이션 수준의 속도 제한 (rate limiting) 로직도, 처리해야 할 경쟁 상태 (race conditions)도, 동시 요청으로 이를 우회할 방법도 없습니다. 이 제약 조건은 원자적 (atomic)입니다.

제출된 데이터는 격리된 대기 (pending) 상태로 진입하며, 운영 (production) 데이터에 영향을 미치기 전에 가벼운 관리자 대시보드를 통해 검토됩니다. { "status": "approved" }를 포함한 깔끔한 PATCH /products/image-suggestions/:id 요청이 마이그레이션을 트리거합니다. 핵심 운영 데이터는 명시적인 승인이 있을 때까지 검증되지 않은 커뮤니티 입력에 의해 전혀 오염되지 않은 상태로 유지됩니다.

실제 수치는 다음과 같습니다

• 모든 카테고리에 걸친 170만 개의 제품 레코드
• 41,000개 이상의 전 세계 정부 리콜 기록
• 100,000개 이상의 커뮤니티 안전 불만 사항
• 7개 언어 지원
• Oracle Cloud Free Tier VM 1대 (6GB ARM)
• $0의 반복적인 호스팅 비용
• 평균 66ms의 페이지 조립 시간

이 중 어느 것도 특이한 인프라를 필요로 하지 않았습니다. 실시간 정확성이 실제로 중요한 지점과, 결과적 일관성 (eventual consistency)이 완벽하게 합리적인 공학적 선택인 지점을 정확히 구분하는 것이 필요했을 뿐입니다.

데이터베이스가 실시간으로 행 (row)의 개수를 셀 필요는 없습니다. 인덱스 (index)는 어떤 컬럼이 중요할지에 대한 직관이 아니라, 실제 쿼리 패턴 (query patterns)과 일치해야 합니다. 그리고 LLM API 호출은 고유한 입력당 정확히 한 번만 발생해야 하며, 그 이후에는 다시 발생하지 않아야 합니다.

제가 다르게 했을 부분

stats_cache 방식은 작동하지만, 모든 수집 파이프라인 (ingestion pipeline) 전반에 걸쳐 규율 있는 카운터 유지가 필요합니다. 상태 변경 시 감소 (decrement) 처리를 놓치면 디버깅하기 어려운 조용한 드리프트 (drift)가 발생합니다. 저는 소스 진실 (source truth)로부터 주기적으로 카운터를 재계산하고 드리프트를 패치하는 예약된 조정 (reconciliation) 작업을 고려할 것입니다. 아직 구축하지는 않았지만 아마도 구축해야 할 부분입니다.

번역 레이어 (translation layer)의 경우, SHA-256 해시 방식은 소스 영어 텍스트가 단 한 글자만 바뀌어도 깨집니다. 이는 새로운 캐시 미스 (cache miss)를 생성하고 재번역을 유발합니다. 더 스마트한 퍼지 매칭 (fuzzy matching)이나 의미론적 유사도 임계값 (semantic similarity threshold)을 사용한다면 사소한 콘텐츠 수정 시 발생하는 중복 번역 호출을 줄일 수 있을 것입니다.

LemonKnows는 lemonknows.com에서 라이브 중입니다. 아키텍처에 대한 의견이나, 이러한 문제들에 대한 더 나은 접근 방식, 또는 제가 틀린 부분이 있다면 무엇이든 댓글로 알려주세요.