재고 잔액은 지뢰밭과 같습니다**.

SQL 교과서에서는 모든 것이 깔끔해 보입니다. 트랜잭션 이력에 대해 SUM(quantity)를 실행하거나 ROW_NUMBER() 윈도우 함수 (Window Function)를 사용하면 끝입니다. 하지만 시스템이 수백만 행으로 성장하는 즉시, 이러한 "예쁜" 학술적 해결책들은 RDBMS를 느릿느릿한 병목 현상(Bottleneck)으로 변질시킵니다. 그리고 실제 비즈니스 시나리오(음수 판매, 소급 적용 문서, 즉석 제조 등)가 나타나면, 원가(Cost price)는 조용히 기하급수적으로 폭발합니다.

더 나쁜 것은, 현대의 AI 어시스턴트와 새로 들어온 주니어 "최적화 전문가(Optimizers)"들이 엄격하게 이러한 교과서적인 관점으로만 생각한다는 점입니다. 그들은 코드 내의 저수준 RDBMS 해킹(Hacks)을 보고 당황하며, 코드를 "깔끔하게" 만듭니다... 그리고 일주일 후, 회계 부서는 쓰레기 데이터를 받게 됩니다.

이 글에서 저는 재고 장부의 4가지 근본적인 고충(Pain points)을 분석하고, MySQL/MariaDB의 쿼리 옵티마이저(Query optimizer) 물리 법칙을 설명하며, 고부하 시스템을 12년 동안 개발하며 도달한 스냅샷 로그 (Snapshot Log) 아키텍처를 보여드리겠습니다.

고충 #1. 재고 잔액 조회: SUM()의 실패와 LIMIT 해킹의 물리 법칙

왜 SUM()과 윈도우 함수가 데이터베이스를 죽이는가

수백만 행의 데이터셋 전체에 대해 SUM(quantity)를 사용하여 재고 잔액을 계산하는 것은 전형적인 아키텍처 설계 오류입니다. 매번 확인을 할 때마다 발생하는 $O(N)$ 복잡도는 어떤 보고서든 한 시간 동안 기다려야 하는 작업으로 만듭니다.

"학자들"의 두 번째 시도는 윈도우 함수 (Window functions)를 사용하는 것입니다:

-- 의사(Pseudo) 형태의 예쁜 교과서 SQL
SELECT item_id, warehouse_id, remains_quantity
FROM (
...

재앙의 물리 법칙: MySQL/MariaDB에서 대량의 데이터에 대한 이 쿼리는 치명적인 filesort를 유발합니다. 엔진은 디스크에 임시 테이블을 생성하고, 기가바이트 단위의 인덱스를 스캔하며, 이를 임시 파일에서 정렬하도록 강제됩니다. 성능은 몇 차례의 자릿수(Orders of magnitude)만큼 떨어집니다.

아키텍처적 해결책: 스냅샷 로그 (Snapshot Log)

단순한 변동 로그 대신, 우리는 **상태 스냅샷 (Snapshot Log)**을 저장합니다. 모든 문서 실행은 두 개의 숫자를 증가시킵니다:

$$
\text{remains_quantity}{\text{new}} = \text{remains_quantity}{\text{old}} + \Delta\text{qty}
$$

계산 복잡도 (Calculation complexity)는 $$O(1)$$입니다. 현재 잔고는 항상 **마지막으로 기록된 행 (last recorded row)**입니다.

LIMIT 해킹과 AI의 막다른 길

윈도우 함수 (Window functions) 없이 마지막 행을 가져오기 위해 서브쿼리 그룹화 (Subquery grouping)가 사용됩니다. 하지만 RDBMS 쿼리 옵티마이저 (Query optimizers)는 "똑똑"합니다. 만약 ORDER BY가 포함된 표준 서브쿼리를 작성하면, 옵티마이저는 정렬을 단순히 무시해 버리며, GROUP BY는 무작위 행을 반환합니다.

엔진이 실제로 데이터를 정렬하도록 강제하기 위해, **강제 서브쿼리 구체화 (Forced subquery materialization)**가 적용됩니다:

SELECT 
    r.warehouse_id, 
    r.item_id, 
...

실무에서의 드라마: 똑같은 이야기가 정기적으로 반복됩니다. 새로운 개발자(또는 AI 어시스턴트)가 나타나서, 터무니없는 LIMIT 9999999999를 보고 이렇게 생각합니다: "정말 엉망이군! 이걸 깔끔하게 정리하고 LIMIT을 제거해야지". 쿼리는 갑자기 매우 빠르게 실행되고 모두가 행복해하지만, 하루 뒤 MariaDB가 무작위 행을 그룹화하면서 재고 잔고가 깨져버립니다.

페인 포인트(Pain Point) #2. 품절 소비: "0"을 넘어가는 경우

이상적인 세상에서는 마이너스 재고가 존재하지 않습니다. 하지만 실제 비즈니스에서는 — 계산원이 실수하거나, 송장 (Invoice)이 제때 처리되지 않거나, 실물 상품은 출고되었으나 아직 데이터베이스에는 반영되지 않은 상황이 발생합니다.

만약 마이너스 잔고가 허용된다면, 수학적 이상 현상이 발생합니다. 만약 품목 잔고가 원가 0인 상태에서 -5 단위까지 내려갔다가, 이후 $100 가격의 배치 (Batch)가 입고된다면, 기초적인 산수만으로도 원가에 엄청난 왜곡이 발생합니다.

마이너스에서 플러스로의 전환을 올바르게 처리하는 방법:

1. 패치를 통한 마이너스 잔고 비활성화 (is_overplus): 잔고가 0 미만으로 떨어지면, 시스템은 잔고를 0으로 유지하는 가상 입고 항목 패치 (is_overplus = 1)를 자동으로 생성합니다.
2. 마이너스 잔고 허용: 마이너스 잔고가 허용되는 경우, 마이너스 잔고 상태 이후에 입고 전표를 작성할 때, remains_costsum은 현재의 마이너스 잔고가 아니라 마지막 양수 입고 가격 (lastprice)으로부터 강제로 계산됩니다.

$$\text{remains_costsum} = \text{remains_quantity} \times \text{lastprice}$$

Pain Point #3. 적시 생산 (Just-In-Time Manufacturing (Transit))

외식업이나 소매업에서는 모든 버거와 커피 한 잔을 판매하기 전에 별도의 "생산 (Production)" 문서를 생성하는 사람이 아무도 없습니다. 품목들은 "즉석에서 (on the fly)" 판매됩니다.

그 정확한 순간, 단일 문서 내에서 다음과 같은 이동 (transit) 계산이 발생합니다:

• 원재료 (자식, parentid)는 창고에서 차감되며, 원가 (cost price)를 확정합니다.
• 완성된 요리 (부모, id)는 원재료의 총 원가로 입고되며, 동일한 트랜잭션 (transaction) 내에서 즉시 0으로 차감됩니다.
• 레지스터 (register)에서 요리 자체의 remains_quantity는 변하지 않지만 (0으로 유지), 재무 회계는 창고 평균 가격을 왜곡하지 않으면서 정확한 원가 비용을 기록합니다.

Pain Point #4. 소급 문서와 되돌릴 수 없는 지점 ($mindt)

사용자가 지난주 화요일 날짜로 문서를 입력하면, 해당 날짜 이후의 계산된 모든 재고 잔액 (remains_quantity, remains_costsum) 체인이 무효화됩니다.

5년 치의 이력을 다시 계산하는 것은 데이터베이스 (database)에 자살 행위나 다름없습니다.

이를 해결하기 위해 "되돌릴 수 없는 지점 (Point of No Return, $mindt)" 알고리즘이 사용됩니다. 우리는 해당 품목에 대해 마지막으로 보장된 양수 입고 트랜잭션의 날짜를 찾습니다:

SELECT warehouse_id, item_id, MAX(dt) AS mindt
FROM stock_register
WHERE dt <= :target_dt
...

모든 계산과 재계산 연쇄 (recalculation cascades)는 dt >= $mindt 조건에 의해 차단되며, 이를 통해 스캔되는 데이터 볼륨을 90~95%까지 줄일 수 있습니다.

요약 및 도구

재고 회계는 단순히 "예쁜 코드"에 관한 것이 아닙니다. 특정 RDBMS의 쿼리 물리 법칙 (query physics)을 이해하고, 트랜잭션 무결성 (transactional integrity)을 관리하며, 예외적인 수학적 상황 (edge-case mathematics)을 처리하는 것에 관한 것입니다.

새로운 개발자들에게 매번 이 규칙들을 설명하거나, 끊임없이 LIMIT 9999999999를 누락시키거나 ROW_NUMBER()를 사용하여 쿼리를 다시 작성하려는 LLM 모델들과 논쟁하는 것은 금방 지치는 일입니다.

그렇기 때문에 저는 이 12년 동안의 아키텍처 (architecture), 정렬 삼중주 (sorting triad), is_overplus 로직, 그리고 MariaDB 해킹 기법들을 모두 하나의 특화된 시스템 프롬프트 (specialized system prompt, AI Trainer) 에 담았습니다.

만약 SQL을 생성하거나 백엔드 아키텍처 (backend architecture)를 설계하기 위해 AI 어시스턴트 (AI assistants)를 사용한다면, 이 시스템 프롬프트를 컨텍스트 (context) 에 단순히 삽입하기만 하면 됩니다. 그 이후부터는 어떤 AI라도 초보적인 실수 없이, 시니어 엔지니어링 표준 (senior engineering standards) 에 따라 검증 쿼리 (verification queries), DDL, 그리고 재계산 함수 (recalculation functions) 를 엄격하게 생성하기 시작할 것입니다.

• AI Trainer를 다운로드하고 편리한 결제 방법을 선택하세요: linktr.ee/AlexCRAZY74